Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Built-in System Function – $PARTITION

Using Built-in System Function – $PARTITION


you can use @PARTITION TSQL function to check which partition will be mapped to a specified column value.

For example if you have a partition function with four partitions as below:

— © 2011 – Vishal (http://SqlAndMe.com)

 

CREATE PARTITION FUNCTION PartFunc01 (INT)

AS RANGE FOR VALUES (10, 20, 50)

Now, when you insert data to a table which uses a partition scheme which is based on this partition function, the data will be inserted according to partition number returned by the partition function.

If you need to check which partition does a column value belongs to, you can use $PARTITION function as below:

SELECT      $PARTITION.PartFunc01 (9),

            $PARTITION.PartFunc01 (11),

            $PARTITION.PartFunc01 (21),

            $PARTITION.PartFunc01 (51)

Result Set:

———– ———– ———– ———–

1           2           3           4

 

(1 row(s) affected)

It can also be used in a WHERE clause:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      ID, Name

FROM  dbo.FirstNames

WHERE $PARTITION.PartFunc01(ID) = 2

— Where 2 is the partition number

 

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.