====== Table partitioning ======
Here's a great intro: https://www.cathrinewilhelmsen.net/table-partitioning-in-sql-server/.
===== Partition number/range correspondence  =====

Based on http://www.theboreddba.com/Categories/partitioning/How-to-Find-Partition-Range-Values.aspx.

<code tsql>SELECT 
  QUOTENAME(OBJECT_SCHEMA_NAME(p.[object_id])) + '.' 
    + QUOTENAME(OBJECT_NAME(p.[object_id])) AS TableName
, p.partition_number
, lv.[value] AS LowerBound
, rv.[value] AS UpperBound
, 'Excludes ' + IIF(f.boundary_value_on_right = 1, 'Upp', 'Low') + 'erBound (range ' 
    + IIF(f.boundary_value_on_right = 1, 'RIGHT', 'LEFT') + ')' AS [RangeDirection]
FROM sys.partitions p
  INNER JOIN sys.indexes i
    ON p.[object_id] = i.[object_id]
    AND p.index_id = i.index_id
  INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id
  INNER JOIN sys.partition_functions f ON s.function_id = f.function_id
  LEFT JOIN sys.partition_range_values lv
    ON f.function_id = lv.function_id
    AND p.partition_number - 1 = lv.boundary_id
  LEFT JOIN sys.partition_range_values rv
    ON f.function_id = rv.function_id
    AND p.partition_number = rv.boundary_id
WHERE p.index_id <= 1
</code>

===== Which partitioning schemes do partitioned tables use? =====

<code tsql>SELECT
  '[' + OBJECT_SCHEMA_NAME(i.[object_id]) + '].[' + OBJECT_NAME(i.[object_id]) + ']'
, ps.[name] AS PartitionScheme
, c.[name] AS PartitioningColumn
, pf.[name] AS PartitionFunction
FROM sys.indexes i  
  INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id  
  INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
  INNER JOIN sys.index_columns ic 
    ON ic.[object_id] = i.[object_id]
    AND ic.index_id = i.index_id
  INNER JOIN sys.columns c 
    ON c.[object_id] = ic.[object_id]
    AND c.column_id = ic.column_id
WHERE i.index_id <= 1
AND ic.partition_ordinal > 0
</code>


{{tag> tsql partitioning}}