Get a record's current partition 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 &lt;clause to identify rows&gt;;


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