Horizontal Partitioning in SQL Server

HORIZONTAL PARTTITION

• Horizontal Partitioning divides a large table into smaller manageable parts without having to create separate tables for each part.
• Data in a partitioned table is physically stored in groups of rows called a partition. Each partition can be accessed and maintained separately.

EXAMPLE

• First I have created a TEST database and this database present in our local file system.

LOCAL FILE SYSTEM

• I have created a DATA table it contains 299398 records. There is only 1 partition so we partition applies on the column by date.

OBJECT ID AND PARTITION NUMBER

• To check the table is partitioned or not right-click on table → Goto properties → Click on storage. Table partitioned is set to false which means there is no partitioned on the table.

TABLE IS NOT PARTITIONED

• Firstly we create a separate filegroup. We need to do is prepare the database by creating files and filegroups which we will use to store the table partitions.
• In the object explorer → Right-click on TEST Database → Goto on property → In the Filegroups tab, we add the filegroup by clicking Add Filegroup.

FILEGROUP

• Here we created files under the filegroups. In the file enter a logical name and select filegroup. Then click on ok.

FILES

• After adding files and filegroup we are partition the table.
• In the object explorer expand the database → Right-click on the TEST table → Goto the storage option → Click on Create Partition.

STEPS TO CREATE PARTITION

• When we click on Create partition new pop up window is open. This wizard helps to partition an existing table in the database.

CREATE PARTITION WIZARD

PARTITION COLUMN

• Data in a partitioned table is partitioned based on a single column. The partition column also called the partition key.
• Select the column name on which you want to partition your table. Here we select the CreationDate column. And click on Next.

SELECT PARTITIONING COLUMN

PARTITION FUNCTION

• The partition function will map the rows from the original table to the partition table. It defines how to partition data based on the partition column.
• Create a new partition function or else we can use existing partition function.

PARTITION FUNCTION

PARTITION SCHEME

• The partition scheme will map the partition table with the filegroups. It is possible to map each partition to its own filegroup or all partitions to one filegroup.
• Create a new partition scheme or else we use the existing partition scheme.

PARTITION SCHEME

MAP PARTITIONS

• The partition functions are created as either the left range or the right range.
• Left Boundary is an actual boundary value belongs to its left partition. It is the last value in the left partition.
• Right Boundary is an actual boundary value that belongs to its right partition. It is the first value in the right partition.
• The next step is to map partitions to filegroups and specify range values. In the Set Boundary Values, we can set the start and the end date for each partition.

SET BOUNDARY VALUES

• The Estimate storage option displays the Rowcount, Required Space, and the Available Space in the table.

MAP PARTITIONS

• We select an output option like create a script for partitioning the table, Run the script immediately or schedule a job for partitioning.

SELECT AN OUTPUT OPTION

• Review Summary window shows the review of the selections for the wizard.

REVIEW SUMMARY

• This is a progress wizard window for creating a partition.

PARTITION WIZARD PROGRESS

• After the finishing process, the new query window is open in SQL Server we need to execute that query.

QUERY

• If we check storage in table property in the filegroup the table has been partitioned also it shows the information about partitioning like partition scheme, partition column as well as number of partitions.

TABLE IS PARTITIONED

• By using a query we can check the number of the partition.

RESULT

• If we insert a new record in the table but date defined as ‘01-May-1997’ so this record automatically assigns to the primary partition and rows converted into 1.

INSERT A NEW RECORD

FILEGROUPS

In SQL Server three types of file

• The Primary file contains the startup information for the database and points to the other files in the database. Every database has one primary file. The file extension of the primary file is .mdf.
• The Secondary file is a user-defined file. The file extension of the secondary file is .ndf.
• The Transaction Log files hold the log information that is used to recover the database. There must be at least one log file for each database. The file extension of the transaction log file is .ldf.
• After creating a filegroup .ndf file has created for each file-group in the local system.

.NDF FILE

If you are a newbie to database learning — SQL Server recommended is the following must-watch video:-