Sql-server – Get a record’s current partition

partitioningsql serversql-server-2008-r2

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

Best Answer

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

Related Question