Skip to content

SQL Server Partitioning

    In SQL Server Partitioning the data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned so that ‘groups of rows’ are mapped into individual partitions

    Table Partitioning is an Enterprise Edition feature only

    What are primary files and filgroups?

    A filegroup can be considered a logical storage unit to house database objects that maps to a file system file or multiple files

    SQL Server Partitioned Table Creation

    Information about partition

    SELECT ps.name,pf.name,boundary_id,value
    FROM sys.partition_schemes ps
    INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
    INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id
    

    Benefits of partitioning

    • Data partitioning improves the performance of SQL queries, data management, index management and storage management
    • Allows you to spread data onto different physical disks, leveraging the concurrent performance of those disks to optimize query performance
    • Separate the data into partition based on date and then only index the most current date data
    • transfer or access subsets of data quickly and efficiently
    • You can perform maintenance operations on one or more partitions more quickly
    • operations are more efficient because they target only these data subsets, instead of the whole table
    • When SQL Server performs data sorting for I/O operations, it sorts the data first by partition. SQL Server accesses one drive at a time, and this might reduce performance
    • Improve performance by enabling lock escalation at the partition level to reduce lock contention on the table
    • extracting, moving, removing information from tables with hundreds of millions of rows without creating blockages
    • Partitioning is not visible to end users, a partitioned table behaves like one logical table when queried
    • Less frequently accessed data can be placed on slower disks and more frequently accessed data can be placed on faster disks.
    • Historical, unchanging data can be set to read-only and then be excluded from regular backups.
    • If data needs to be restored it is possible to restore the partitions with the most critical data first.

    Partition function

    Partition function defines ranges of data to be partitioned; the last partition function catches collects all data outside the boundaries of the predefined ranges

    Partition scheme

    Partition scheme uses the partition function to map data ranges to appropriate file groups

    Scripts

    USE SQL2
    GO
    
    SELECT * FROM [dbo].[People2]--1000
    
    SELECT * FROM [dbo].[People3] -- 10,000,000  --2 MIN 10 SECS
    
    SELECT * FROM [dbo].[People] -- 100,000,000  --LONGER THAN 10,000,000
    
    
    --DROP DATABASE
    --use master
    --go
    
    --DROP DATABASE AdventureWorks2012
    --GO
    
    ----RESTORE DATABASE
    
    USE [master]
    RESTORE DATABASE [AdventureWorks2012] 
    FROM  DISK = N'C:\backup122215\Database backups\ADVENTUREWORKS2012.BAK' 
    WITH  FILE = 1,  
    MOVE N'AdventureWorks2012_Data' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf',  
    MOVE N'AdventureWorks2012_Log' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf',  
    NOUNLOAD,  
    STATS = 5
    GO
    
    ---- row count
    
    SELECT COUNT(*) AS Total_Rows
    FROM AdventureWorks2012.Production.TransactionHistoryArchive
    
    ----89253
    
    ----Find distinct datetime per year so as to partition
    
    SELECT DISTINCT YEAR(TransactionDate) AS Year, COUNT(*) AS Total_Rows
    FROM  AdventureWorks2012.Production.TransactionHistoryArchive
    GROUP BY YEAR(TransactionDate)
    ORDER BY 1
    
    --1: Before creating the file groups, create 3 seperate folders
    --on drive C: to represent 'physical drives' (drive d, drive e, drive f)
    
    ----Create 4 file groups for each year - 2005, 2006, 2007 and all data not in first three partitions
    --1 ADD FILEGROUPS FOR EACH YEAR PARTITION
    
    USE [master]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILEGROUP [DRIVE D]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILEGROUP [DRIVE E]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILEGROUP [DRIVE F]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILEGROUP [DRIVE G]
    GO
    
    
    ----With in those four folders (drives d, drive e, drive f, drive g), create 4 seperate data files (.ndf) 
    ----for each year in the table
    
    
    ---- Create a partition function for each year range (note no table has been assigned to any partition as of yet)
    ----thus, you can associate any table to the partition at this point
    
    
    USE AdventureWorks2012
    GO
    
    SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
    FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id=p.object_id
    INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
    WHERE o.name LIKE '%TransactionHistoryArchive%'
    
    
    USE [AdventureWorks2012]
    GO
    
    --2: CREATE 4 DATA (NDF)FILES IN FILEGROUP FOR EACH PARTITION
    
    USE [master]
    GO
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILE ( NAME = N'TRANS2005', 
    FILENAME = N'C:\drive d\TRANS2005.ndf' , 
    SIZE = 4096KB , 
    FILEGROWTH = 1024KB ) 
    TO FILEGROUP [DRIVE D]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILE ( NAME = N'TRANS2006', 
    FILENAME = N'C:\drive e\TRANS2006.ndf' , 
    SIZE = 4096KB , 
    FILEGROWTH = 1024KB ) 
    TO FILEGROUP [DRIVE E]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILE ( NAME = N'TRANS2007', 
    FILENAME = N'C:\drive f\TRANS2007.ndf' , 
    SIZE = 4096KB , 
    FILEGROWTH = 1024KB ) 
    TO FILEGROUP [DRIVE F]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILE ( NAME = N'TRANS2015', 
    FILENAME = N'C:\drive g\TRANS2015.ndf' , 
    SIZE = 4096KB , 
    FILEGROWTH = 1024KB ) 
    TO FILEGROUP [DRIVE G]
    GO
    
    --AT THIS POINT WE HAVE CREATED THE 'CONTAINERS' AND FILES FOR THE TABLE TO BE PARTITIONED TOO!!
    
    -- CREATE PARTITION ON TABLE [TransactionHistoryArchive]
    
    USE [AdventureWorks2012]
    GO
    
    BEGIN TRANSACTION
    
    CREATE PARTITION FUNCTION [FUNCTION_TRANSHISTORY](datetime) 
    AS RANGE LEFT 
    FOR VALUES (N'2005-12-31T23:59:59.997', N'2006-12-31T23:59:59.997', N'2007-12-31T23:59:59.997')
    
    
    CREATE PARTITION SCHEME [SCHEMA_TRANSHISTORY] 
    AS PARTITION [FUNCTION_TRANSHISTORY] 
    TO ([DRIVE D], [DRIVE E], [DRIVE F], [PRIMARY])
    
    
    ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
    
    ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED 
    ([TransactionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    
    
    CREATE CLUSTERED INDEX [ClusteredIndex_on_SCHEMA_TRANSHISTORY_635922165997540276] ON [Production].[TransactionHistoryArchive]
    ([TransactionDate]
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [SCHEMA_TRANSHISTORY]([TransactionDate])
    
    DROP INDEX [ClusteredIndex_on_SCHEMA_TRANSHISTORY_635922165997540276] ON [Production].[TransactionHistoryArchive]
    
    COMMIT TRANSACTION
    
    --VERIFY
    
    USE AdventureWorks2012
    GO
    
    SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
    FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id=p.object_id
    INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
    WHERE o.name LIKE '%TransactionHistoryArchive%'
    
    
    -- INSERT DATA TO VERIFY DIRECTION OF DATA TO PARTITION
    
    USE [AdventureWorks2012]
    GO
    
    INSERT INTO [Production].[TransactionHistoryArchive]
               ([TransactionID]
               ,[ProductID]
               ,[ReferenceOrderID]
               ,[ReferenceOrderLineID]
               ,[TransactionDate]
               ,[TransactionType]
               ,[Quantity]
               ,[ActualCost]
               ,[ModifiedDate])
    VALUES
    (89254,	1,1,1,'2016-02-28 00:00:00.000','P',999,50.2600,'2016-02-28 00:00:00.000')
    GO
    
    
    --VIEW ACTULA DATA IN THE PARTITION
    
    SELECT * FROM [Production].[TransactionHistoryArchive]
    WHERE $PARTITION.[FUNCTION_TRANSHISTORY](TransactionDate) = 4 ;--<< PARTITION 4 CONTAIN ONLY DATA THAT IS NOT IN RANGE OF PARTITION 1,2,3
    
    
    [DRIVE D]
    [DRIVE E]
    [DRIVE F]
    [DRIVE G]
    
    TRANS2005
    TRANS2006
    TRANS2007
    TRANS2015
    
    2005-12-31T23:59:59.997
    2006-12-31T23:59:59.997
    2007-12-31T23:59:59.997
    
    
    BACKUP DATABASE
    
    BACKUP DATABASE [AdventureWorks2012] 
    TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ADVENTUREWORKS2012.BAK' 
    WITH  COPY_ONLY, 
    NOFORMAT, 
    NOINIT,  
    NAME = N'AdventureWorks2012-Full Database Backup', 
    SKIP, 
    NOREWIND, 
    NOUNLOAD,  
    STATS = 10
    GO
    
    
    --USE SQL2
    --GO
    
    --SELECT * FROM [dbo].[People2]--1000
    
    --SELECT * FROM [dbo].[People3] -- 10,000,000  --2 MIN 10 SECS
    
    --SELECT * FROM [dbo].[People] -- 100,000,000  --LONGER THAN 10,000,000
    
    
    --DROP DATABASE
    use master
    go
    
    DROP DATABASE AdventureWorks2012
    GO
    
    --RESTORE DATABASE
    
    USE [master]
    RESTORE DATABASE [AdventureWorks2012] 
    FROM  DISK = N'C:\backup122215\Database backups\ADVENTUREWORKS2012.BAK' 
    WITH  FILE = 1,  
    MOVE N'AdventureWorks2012_Data' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf',  
    MOVE N'AdventureWorks2012_Log' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf',  
    NOUNLOAD,  
    STATS = 5
    GO
    
    ---- row count
    
    SELECT COUNT(*) AS Total_Rows
    FROM AdventureWorks2012.Production.TransactionHistoryArchive
    
    ----89253
    
    ----Find distinct datetime per year so as to partition
    
    SELECT DISTINCT YEAR(TransactionDate) AS Year, COUNT(*) AS Total_Rows
    FROM  AdventureWorks2012.Production.TransactionHistoryArchive
    GROUP BY YEAR(TransactionDate)
    ORDER BY 1
    
    --1: Before creating the file groups, create 4 seperate folders
    --on drive C: to represent 'physical drives' (drive d, drive e, drive f, drive g)
    
    ----Create 4 file groups for each year - 2005, 2006, 2007 and all data not in first three partitions
    --1 ADD FILEGROUPS FOR EACH YEAR PARTITION
    
    USE [master]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILEGROUP [DRIVE D]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILEGROUP [DRIVE D]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILEGROUP [DRIVE F]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILEGROUP [DRIVE G]
    GO
    
    
    ----With in those four folders (drives d, drive e, drive f, drive g), create 4 seperate data files (.ndf) 
    ----for each year in the table
    
    
    ---- Create a partition function for each year range (note no table has been assigned to any partition as of yet)
    ----thus, you can associate any table to the partition at this point
    
    
    USE AdventureWorks2012
    GO
    
    SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
    FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id=p.object_id
    INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
    WHERE o.name LIKE '%TransactionHistoryArchive%'
    
    SELECT DISTINCT YEAR(TransactionDate) AS Year, COUNT(*) AS Total_Rows
    FROM  AdventureWorks2012.Production.TransactionHistoryArchive
    GROUP BY YEAR(TransactionDate)
    ORDER BY 1
    
    
    USE [AdventureWorks2012]
    GO
    
    --2: CREATE 4 DATA (NDF)FILES IN FILEGROUP FOR EACH PARTITION
    
    USE [master]
    GO
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILE ( NAME = N'TRANS2005', 
    FILENAME = N'C:\drive d\TRANS2005.ndf' , 
    SIZE = 4096KB , 
    FILEGROWTH = 1024KB ) 
    TO FILEGROUP [DRIVE D]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILE ( NAME = N'TRANS2006', 
    FILENAME = N'C:\drive e\TRANS2006.ndf' , 
    SIZE = 4096KB , 
    FILEGROWTH = 1024KB ) 
    TO FILEGROUP [DRIVE E]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILE ( NAME = N'TRANS2007', 
    FILENAME = N'C:\drive f\TRANS2007.ndf' , 
    SIZE = 4096KB , 
    FILEGROWTH = 1024KB ) 
    TO FILEGROUP [DRIVE F]
    GO
    
    ALTER DATABASE [AdventureWorks2012] 
    ADD FILE ( NAME = N'TRANS2015', 
    FILENAME = N'C:\drive g\TRANS2015.ndf' , 
    SIZE = 4096KB , 
    FILEGROWTH = 1024KB ) 
    TO FILEGROUP [DRIVE G]
    GO
    
    --AT THIS POINT WE HAVE CREATED THE 'CONTAINERS' AND FILES FOR THE TABLE TO BE PARTITIONED TOO!!
    
    -- CREATE PARTITION ON TABLE [TransactionHistoryArchive]
    
    USE [AdventureWorks2012]
    GO
    
    BEGIN TRANSACTION
    
    CREATE PARTITION FUNCTION [FUNCTION_TRANSHISTORY](datetime) 
    AS RANGE LEFT 
    FOR VALUES (N'2005-12-31T23:59:59.997', N'2006-12-31T23:59:59.997', N'2007-12-31T23:59:59.997')
    
    
    CREATE PARTITION SCHEME [SCHEMA_TRANSHISTORY] 
    AS PARTITION [FUNCTION_TRANSHISTORY] 
    TO ([DRIVE D], [DRIVE E], [DRIVE F], [drive g])
    
    
    ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
    
    ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED 
    ([TransactionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    
    
    CREATE CLUSTERED INDEX [ClusteredIndex_on_SCHEMA_TRANSHISTORY_635922165997540276] ON [Production].[TransactionHistoryArchive]
    ([TransactionDate]
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [SCHEMA_TRANSHISTORY]([TransactionDate])
    
    DROP INDEX [ClusteredIndex_on_SCHEMA_TRANSHISTORY_635922165997540276] ON [Production].[TransactionHistoryArchive]
    
    COMMIT TRANSACTION
    
    --VERIFY
    
    USE AdventureWorks2012
    GO
    
    SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
    FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id=p.object_id
    INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
    WHERE o.name LIKE '%TransactionHistoryArchive%'
    
    
    -- INSERT DATA TO VERIFY DIRECTION OF DATA TO PARTITION
    
    USE [AdventureWorks2012]
    GO
    
    INSERT INTO [Production].[TransactionHistoryArchive]
               ([TransactionID]
               ,[ProductID]
               ,[ReferenceOrderID]
               ,[ReferenceOrderLineID]
               ,[TransactionDate]
               ,[TransactionType]
               ,[Quantity]
               ,[ActualCost]
               ,[ModifiedDate])
    VALUES
    (89265,	1,1,1,'2016-03-04 00:00:00.000','P',999,50.2600,'2016-03-04  00:00:00.000')
    GO
    
    
    --VIEW ACTULA DATA IN THE PARTITION
    
    SELECT * FROM [Production].[TransactionHistoryArchive]
    WHERE $PARTITION.[FUNCTION_TRANSHISTORY](TransactionDate) = 4 ;--<< PARTITION 4 CONTAIN ONLY DATA THAT IS NOT IN RANGE OF PARTITION 1,2,3
    

    Also See:

    SQL Server 2019