Table Partitioning refers to the organization of table data into numerous smaller storage units known as data partitions built on the defined values in a column or group of columns.
It is the horizontally splitting of the data of a large, logically single table set up into smaller physical pieces. However, when queries are performed, these partitions are stored across more than one filegroup in a database but are treated as a single entity, i.e., a single table.
By distributing a large table into smaller partitions, you can advance query performance, and trim down involved costs. Partitioning large tables has the following management advantages and performance benefits:
Diverse RDBMS propose numerous approaches to a partition of data in a table. If built-in backing and support are not obtainable for your needs, then alternative methods like UNION ALL views can be applied. However, they do not deliver similar performance advantages and gains.
When you deal with partitioning a table in SQL Server, the place to initiate is knowing the logical division of your data sets prior to you essentially implement the same. Here is what you require to comprehend and formerly explore to build partitions in the data table.
Partitioning can turn large tables better manageable and more scalable. Here we have explained four significant steps to build a partitioned table:
Step 1: Make filegroup(s) and related files to encompass the partitions utilized in the partition scheme.
Step 2: Decide on the partitioning column(s) and state a partition function that maps the specified rows of a table into partitions depending on the actual values of the partitioning column(s).
Step 3: Define a precise partition scheme that maps the table partitions linked to the filegroups.
Step 4: Building or modifying the table to agree on the partition scheme as per the exact storage location.
Let’s explore partitioning using Transact-SQL with the involved steps and partitioning processes.
Write code to craft new filegroups, a precise partition function, and an explicit partition scheme and implement it. This example helps builds a new table with the partition scheme stated as the storage location.
-- Adds four new filegroups to the Hospital database ALTER DATABASE Hospital ADD FILEGROUP Cancer; GO ALTER DATABASE Hospital ADD FILEGROUP Surgery; GO ALTER DATABASE Hospital ADD FILEGROUP OPD; GO ALTER DATABASE Hospital ADD FILEGROUP Teaching; -- Adds one file for each filegroup. ALTER DATABASE Hospital ADD FILE ( NAME = CancerData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\CancerData.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP Cancer; ALTER DATABASE Hospital ADD FILE ( NAME = SurgeryData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SurgeryData.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP Surgery; GO ALTER DATABASE Hospital ADD FILE ( NAME = OPDData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\OPDData.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP OPD; GO ALTER DATABASE Hospital ADD FILE ( NAME = TeachingData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\TeachingData.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP Teaching; GO
-- Creates a partition function called DepartmentPF that will partition the table into four partitions based on department ID CREATE PARTITION FUNCTION DepartmentPF (int) AS RANGE LEFT FOR VALUES (101, 102, 103) ; GO
-- Creates a partition scheme called DepartmentRangePS that applies DepartmentPF to the four filegroups created above CREATE PARTITION SCHEME DepartmentRangePS AS PARTITION DepartmentPF TO (Cancer, Surgery, OPD, Teaching) ; GO
-- Creates a partitioned table called PatientsPartitionTable that uses DepartmentRangePS to partition DeptID CREATE TABLE PatientsPartitionTable (patientID int , DeptID int , visitDate DateTime NOT NULL, PRIMARY KEY(patientID,DeptID)) ON DepartmentRangePS (DeptID) ; GO
SELECT * FROM sys.tables AS t JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] AND i.[type] IN (0,1) JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id WHERE t.name = 'PatientsPartitionTable'; GO
Partitioning a table by utilizing the SQL Server Management Studio partitioning wizard
Table partitioning can also be accomplished by the SQL Server Management Studio Partitioning Wizard, accessible in SQL Server 2008 onwards.
Step 1: Right-click on the specified table in the Object Explorer and in the Storage context menu and select the Create Partition command:
Step 2: Choosing the Create Partition preference opens the Create Partition Wizard.
Step 3: In the Select a Partitioning Column window, pick the partitioning column(s) from the obtainable columns:
Other choices right in the dialog comprise the Collocate of this table to the chosen partition table selection utilized to showcase the attached data sets to join with the partitioned column, and Storage Align Non-Unique Indexes as well as Unique Indexes with a particular Indexed Partition Column preference that aligns all the mentioned indexes of the partitioned table with the identical partition scheme.
Step 4: In the subsequent step, Select a Partition Function window, enter the specified partition function name and allow mapping of the table rows right into the partitions grounded and related to the partitioning column values, or pick an already present partition function:
Step 5: In the Select a Partition Scheme window, build the partition scheme for mapping the precise table partitions to diverse filegroups:
Step 6: Subsequent to the Map Partitions window, choose the partitioning range and pick the obtainable filegroups and the precise range boundary.
The Estimate storage selection regulates the Rowcount, the Essential space, and the Accessible space columns based on the detailed table records.
Step 7: The succeeding screen of the wizard provides the preference to implement the script swiftly or to save it. Once an option is chosen, it displays a review of selected assortments made in the wizard.
Step 8: Lastly, click the “Finish” button to end the entire process comprehensively.
We learned here how to use and build a partitioned table. We even explored partitioning a table by using the SQL Server Management Studio partitioning wizard.
Furthermore, we discovered that the partition series is always reliant on the precise row size right in the partition function. So, table partitioning in SQL Server is always useful and supportive for sustaining large tables.
Adding a new array to the partition function should now be an automated task. Empower your teams with powerful approaches to evade manual table activities of partition sustenance by enhancing and automating the processes.