In SQL Server, you can use the ALTER PARTITION FUNCTION to merge two partitions into one partition.
To do this, use the MERGE RANGE argument, while providing the boundary value of the partition to drop.
This operation drops the partition and merges any values that exist in the partition into a remaining partition.
If you have a partitioned table or index in SQL Server, but you need more partitions, you can add a partition to the partition function using the ALTER PARTITION FUNCTION statement with the SPLIT RANGE argument.
When you do this, you split an existing partition into two.
You’ll get error 7710 if you try to split a partition in SQL Server, but you haven’t specified a “next used” filegroup.
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.
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.
Here are a couple of ways to return partition info for a table in SQL Server.
sys.partitions system catalog view to return partition info for a table and most kinds of views.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.
You can use the sys.partition_functions system catalog view to return a list of partition functions in SQL Server.
You can use the sys.partition_schemes system catalog view to return a list of partition schemes in SQL Server.
If you get error message 4982 in SQL Server, it’s because your source table doesn’t have a constraint that restricts data to only the range supported by the partition that you’re trying to switch to.
If you need to find out if a table has been partitioned in SQL Server, you can run a join against the sys.tables, sys.indexes, and sys.partition_schemes views.