Database table partitioning in SQL Server

What is a database table partitioning?
Partitioning is the database process where very large tables are divided into multiple smaller parts. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan. The main of goal of partitioning is to aid in maintenance of large tables and to reduce the overall response time to read and load data for particular SQL operations.

There are two way of Partitioning on SQL Server tables:

• Vertical Partitioning
• Horizontal Partitioning

Vertical Partitioning on SQL Server tables

Vertical table partitioning is mostly used to increase SQL Server performance especially in cases where a query retrieves all columns from a table that contains a number of very wide text or BLOB columns. In this case to reduce access times the BLOB columns can be split to its own table. Another example is to restrict access to sensitive data e.g. passwords, salary information etc. Vertical partitioning splits a table into two or more tables containing different columns:

Database table partitioning in SQL Server

Horizontal Partitioning on SQL Server tables

Horizontal partitioning divides a table into multiple tables that contain the same number of columns, but fewer rows. For example, if a table contains a large number of rows that represent monthly reports it could be partitioned horizontally into tables by years, with each table representing all monthly reports for a specific year. This way queries requiring data for a specific year will only reference the appropriate table. Tables should be partitioned in a way that queries reference as few tables as possible.

Database table partitioning in SQL Server

Note: You can’t restore a database with partitioning to a non-Enterprise Edition instance. Important to bear this in mind.  Any partition functions and schemes would need to be removed prior to backing up the database and restoring it to a non Enterprise edition instance.

SQL Server Database Partitioning Myths and Truths

Myth 1: Partitioning is a “Scale-Out” solution.

Partitions cannot span servers or instances. Partitions have to be in the same instance and in the same database. Partitioning therefore is a scale-up solution.

Myth 2: Partitions must be created on different filegroups.

The partition scheme definition defines on which filegroup a partition resides. It’s a common misconception that you have to spread your partitions out among multiple filegroups.

Myth 3: To partition a non-partitioned table you will need to drop and recreate it.

Not true. You can partition an existing table by creating a clustered index (or rebuilding an existing clustered index) on your new Partition Scheme. This will effectively partition your data as the leaf level of a clustered index is essentially the data.

Myth 4: Partitioning an existing table is a strictly offline operation.

It’s true that rebuilding or creating a clustered index is indeed an offline operation. Your table will not be available for querying during this operation. However, partitioning is an Enterprise feature, so we have the online index rebuild feature available to use. The ONLINE = ON option allows us to still query the table while under the covers the partitioning operation is going on. SQL Server does this by using an internal snapshot of the data. Obviously, there is a performance hit and I don’t recommend you do this during a busy time but if you have a requirement for 24×7 availability then this is a possible solution.

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.