Return the Partition Number for Each Row When Querying a Partitioned Table in SQL Server (T-SQL)

If you’ve got a partitioned table in SQL Server, and you now want to run a query that includes the partition number on each row returned by the query, you can use the $PARTITION system function to do just that.

The $PARTITION function returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.

You can therefore use it in your SELECT statement to return the partition that each row belongs to.

Continue reading

How to Map Multiple Partitions to a Single Filegroup in SQL Server (T-SQL)

If you’re familiar with creating partitioned tables in SQL Server, you might be used to creating a separate filegroup for each partition. This has its benefits, and may very well be the method you would choose in most scenarios.

However, you also have the option of mapping multiple partitions to a single filegroup.

In this article I share two examples of mapping multiple partitions to a single filegroup.

  • Example 1 maps all partitions to a single filegroup.
  • Example 2 maps some partitions to one filegroup, and some to another.
Continue reading

Find Out Which Partition a Given Value Would be Mapped to in SQL Server (T-SQL)

If you have a partitioned table or index in SQL Server, and you want to determine which partition a given value would be mapped to, you can do this nice and quickly with the $PARTITION system function.

All you need to know is the name of the partition function (and of course, the value you’re interested in).

Continue reading

3 Ways to Return the Number of Rows in Each Partition in SQL Server (T-SQL)

If you’ve previously created a partitioned table in SQL Server, and you now want to know how many rows are being stored in each partition, here are three queries you can use.

In particular, you can:

  • Query the sys.dm_db_partition_stats view
  • Query the sys.partitions view
  • Use the $PARTITION function in a query

Below are examples of all three.

Continue reading

Find Out if a Partition is Compressed in SQL Server (T-SQL)

In SQL Server, you can query the sys.partitions system catalog view to find out whether or not a partition has been compressed.

In particular, the data_compression column tells you whether it’s compressed or not. The data_compression_desc column tells you what type of compression it uses. If it isn’t compressed, it returns NONE.

Continue reading

Return All Rows From a Specific Partition in SQL Server (T-SQL)

When you create a partitioned table in SQL Server, you specify which values go into each partition.

This is done when you create the partition function. When you create the partition function, you specify boundary values, which determine which values go into each partition.

Once you’ve created your partitioned table, and you’ve inserted data, you can run a normal SELECT statement to return data, just as you would with a non-partitioned table (actually, even non-partitioned tables have one partition).

But did you know that you can also specify which partition you want data from?

You can do this with the help of the $PARTITION system function in your WHERE clause.

Continue reading

How to Fix “ALTER TABLE SWITCH statement failed”

If you’re getting error 4939 while trying to switch a partition in SQL Server, it’s probably because you’re trying to switch to a partition that uses a different filegroup.

One of the requirements of switching partitions is that both the source table or partition, and the target table or partition, must be located in the same filegroup.

Continue reading

Switch-Out a Partition in SQL Server (T-SQL)

In SQL Server, partition switching allows you to load large amounts of data in or out of a table very quickly. This saves you from having to run delete or insert statements, and can be very useful when working with large data sets.

You can use the ALTER TABLE statement to switch a partition in or out of a table.

To switch a partition out of a table, the code goes like this:

ALTER TABLE Table1
SWITCH PARTITION x TO Table2

This switches partition x from Table1 to Table2 (where x is the partition number).

Continue reading