Get the Boundary Values for a Partitioned Table in SQL Server (T-SQL)

In SQL Server, you can use the sys.partition_range_values system catalog view to find out the boundary values used for a partitioned table.

However, you’d need to know the function_id of the partition function before you use that view.

But as always, you can run a join against a bunch of other tables to get the desired info.

Continue reading

Check the Parameter Type of a Partition Function in SQL Server (T-SQL)

If you’ve got a partitioned table or index in SQL Server, and you want to check the parameter type of the partition function, you can use the sys.partition_parameters system catalog view.

This view returns a row for each parameter of a partition function.

The parameter type should match or be implicitly convertible to the data type of the partitioning column in the table or index.

Continue reading

2 Ways to Get Partition Information For a Table in SQL Server (T-SQL)

Here are a couple of ways to return partition info for a table in SQL Server.

  • You can use the sys.partitions system catalog view to return partition info for a table and most kinds of views.
  • You can use the sys.dm_db_partition_stats system dynamic management view to return page and row-count information for every partition in the current database.

If a table or index hasn’t been partitioned, these views will still return partition info (with a partition_number of 1). This is because all tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

Continue reading