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)



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 2 is the partition number


Hope This Helps!


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