SQL Server

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

by

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.

Read more

Check the Space Used by a Table in SQL Server

by

SQL Server has a system stored procedure called sp_spaceused that enables you to check the size of a table in a database.

You can use it to check the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or the disk space reserved and used by the whole database.

Read more

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

by

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

Read more