In SQL Server 2008 R2, how would I determine what partition a record currently lives in?
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
(This is also useful shorthand if you are identifying the row(s) in the table using the same column.)
You can use the $PARTITION function to achieve this. The link below should help you: