Advertisement
anchormodeling

Overflow partition and reserved value

Dec 9th, 2011
218
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.92 KB | None | 0 0
  1. drop table Test_Partitioning;
  2. drop partition scheme Test_Partition_Scheme;
  3. drop partition function Test_Partition_Function;
  4.  
  5. create partition function Test_Partition_Function (datetime)
  6. as range left for values (0); -- special reserved value
  7.  
  8. create partition scheme Test_Partition_Scheme
  9. as partition Test_Partition_Function
  10. all to ([PRIMARY]);
  11.  
  12. create table Test_Partitioning (
  13.     id int not null,
  14.     stamp datetime not null,
  15.     primary key (id, stamp)
  16. ) ON Test_Partition_Scheme (stamp);
  17.  
  18. insert into Test_Partitioning values (1, '2001-01-01');
  19. insert into Test_Partitioning values (2, 0);
  20. insert into Test_Partitioning values (2, 1);
  21.  
  22. SELECT
  23.     $partition.Test_Partition_Function(t.stamp) AS [Partition Number],
  24.     t.id,
  25.     case when t.stamp = 0 then null else t.stamp end as stamp
  26. FROM
  27.     Test_Partitioning t
  28.  
  29. /*
  30. Partition Number    id  stamp
  31. 1           2   NULL
  32. 2           1   2001-01-01 00:00:00.000
  33. 2           2   1900-01-02 00:00:00.000
  34. */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement