SQL Server Table Partition

Partitioning a table is an easy step to get a performance, it has been introduced in SQL Server 2005. Partitioning is nothing but we are splitting the big size records into small pieces.
For example, we have a table called “Sales” which is having more than 100000 records, to query this table will take time, so to improve the performance we split the table like 25000 records in one piece and from 25000 to 49999 to another piece likewise for all the records.

FYI, we will not get multiple table names since it is partitioned, the same table stored the records into multiple pieces.

Steps to create partitioned table.

Check the below scripts to create your own partitioned tables.

Step 1:

--Create Partition function with a range of values

CREATE PARTITION FUNCTION Prt_table (INT) AS RANGE LEFT FOR VALUES

(25000,50000,75000,100000)

GO

Step 2:

Map the file group for the range of values.

--Create partition Scheme

CREATE PARTITION SCHEME Prt_Table_Schema AS PARTITION Prt_table TO

([PRIMARY],[PRIM[PRIMARY],[PRIMARY], [PRIMARY])

GO

Step 3:

    You need to specify the ON partition scheme name with column name which you want to use for split the records

--Create a table

CREATE TABLE [Table_Partitioned](

            [SalesOrderID] [int] NOT NULL,

            [SalesOrderDetailID] [int],

            [CarrierTrackingNumber] [nvarchar](25) NULL,

            [OrderQty] [smallint] NOT NULL,

            [ProductID] [int] NOT NULL,

            [SpecialOfferID] [int] NOT NULL,

            [UnitPrice] [money] NOT NULL,

            [UnitPriceDiscount] [money] NOT NULL)

            ON Prt_Table_Schema (SalesOrderDetailID)

GO

Step 4:

 For Demo purpose, I used the adventureworks table.

--Populate the table

INSERT INTO Table_Partitioned ([SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],

                        [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice],[UnitPriceDiscount])

SELECT  [SalesOrderID]

      ,[SalesOrderDetailID]

      ,[CarrierTrackingNumber]

      ,[OrderQty]

      ,[ProductID]

      ,[SpecialOfferID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

GO

Step 5:

--Select the table to check the inserted data

SELECT * FROM Table_Partitioned

GO

Step 6:

--Check the partitioned information

SELECT * FROM SYS.partitions

WHERE OBJECT_ID=OBJECT_ID('Table_Partitioned')

GO