Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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

share|improve this question

2 Answers

up vote 6 down vote accepted

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.)

share|improve this answer
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.)? – GaTechThomas 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. – Aaron Bertrand 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/…; – Mr.Brownstone 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/… – GaTechThomas 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. – Remus Rusanu Aug 17 '12 at 14:55
show 5 more comments
up vote 4 down vote

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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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