7

In SQL Server 2008 R2, how would I determine what partition a record currently lives in?

10

When you can identify a row or set of rows already in the table, you can do it like this:

SELECT $PARTITION.[PartitionFunctionName](PartitioningColumn)
  FROM dbo.table
  WHERE <clause to identify rows>;

If you have just a value, and it's not necessarily in the table yet, you can tell what partition it would be in given the current state using $PARTITION independently:

SELECT $PARTITION.[PartitionFunctionName](SomeConstant);

(This is also useful shorthand if you are identifying the row(s) in the table using the same column.)

10
  • Excellent. I looked into the $partition function but read a question on this site that left some doubt as to whether a record automatically moves to a different partition when its data changes. Is $partition accurate 100% of the time (no delays in moving to new partition, etc.)? Aug 16 '12 at 18:59
  • Well I can't speak for what happens if you use the function while a partition is being switched, or while data is being moved or updated, but if the data's at rest I don't see how there would ever be an issue. Aug 16 '12 at 19:02
  • 1
    Yes, it will move the data. It is recommended however that the partition column be immutable: <social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/…> Aug 16 '12 at 19:04
  • @Mr.Brownstone, Very good. The link had an issue but I was able to sort it out: social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/… Aug 16 '12 at 19:10
  • 1
    An update of a partition column value that causes the row to move across partition boundaries is two distinct operations: a delete from the old partition and an insert into the new partition. Never can a row have a partitioning column value for partition X and reside in partition Y. Aug 17 '12 at 14:55
4

You can use the $PARTITION function to achieve this. The link below should help you:

http://msdn.microsoft.com/en-us/library/ms188071.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.