mssql

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

by

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.

Read more

How Implicit Transactions Work in SQL Server

by

There are four transaction modes in SQL Server. One of these is implicit mode.

In SQL Server, an implicit transaction is when a new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

This is not to be confused with autocommit mode, where the transaction is started and ended implicitly.

Read more

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

by

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.

Read more

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

by

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.

Read more

How to Fix “The select list for the INSERT statement contains fewer items than the insert list”

by

SQL Server error 120 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.

To be more specific, it happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.

This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.

Read more

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