Tag: t-sql

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

Posted on by Ian

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”

Posted on by Ian

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)

Posted on by Ian

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 →

SQL Server CASE Expression

Posted on by Ian

In SQL Server, the T-SQL CASE expression is a scalar expression that returns a value based on conditional logic. It evaluates a list of conditions and returns a value, based on the outcome of those conditions..

In some ways, the SQL Server CASE expression is similar to IF...ELSE. However, CASE allows you to check for multiple conditions, whereas IF...ELSE doesn’t.

Continue reading →