Table partitioning is a lay man terms is just like dividing your
table horizontally in various blocks. Let me take an example from an
Engineer’s life. Every engineer during his graduation day need to study
various subjects in order to get pass in the semester examination. Every
subject use to have a book which had 5 units and the questions use to
come from those 5 units. Now if your tear your book unit wise and make 5
different block of unit that is nothing else but partitioning .
In the same way table can be partitioned based on partition criteria. Partitioning the table help us manage data effectively and quickly. To learn more about partitioning you can refer to below MSDN articles
http://technet.microsoft.com/en-us/library/ms190787.aspx
Now let me show you all a demo to partition a table in database :
In this demo we have created a database name SHAZ. In SHAZ database we have a table name employee which has 320000 rows in single file group (primary). The table was having on cluster index PK_employee on table employee.
We have created four different file group and data files. Each data file is allocated to each file group and then we have tried to partition the table on the basis of the ID column. As per our partitioning 0-49999 ID will be in file group 1, 50000-74999 will be in file group 2 , 75000-99999 will be in file group 3 and greater that 100000 will be in file group 4.
Once the partitioning will be completed we can shrink the primary file group which was earlier holding the data and release the space.
Pre checks before doing partitioning :
5. Size of the table and number of rows.
6. Table Structure and take a note of all cluster and non-cluster indexes.
Step by Step procedure to partition a table in SQL SERVER
Step 1 : Take a full backup of the database which is going to be partitioned as a safety measure.
Step 2 : Identify the number of partition you would make and if you want to keep separate partition on separate file group then you need to create those many file group and secondary files. In our demo we will be creating four partition thus we are creating four secondary files and placing them on there respective file groups.
USE shaz
GO
–Add the first filegroup
ALTER DATABASE shaz
ADD FILEGROUP shazemployee1
GO
–Add a database file to the first filegroup
ALTER DATABASE shaz
ADD FILE
( NAME = N’shazemployee1′
, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\shazemployee1.ndf’
, SIZE = 2
, MAXSIZE = 50
, FILEGROWTH = 1)
TO FILEGROUP shazemployee1
GO
–Add the second filegroup
ALTER DATABASE shaz
ADD FILEGROUP shazemployee2
GO
–Add a database file to the second filegroup
ALTER DATABASE shaz
ADD FILE
( NAME = N’shazemployee2′
, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\shazemployee2.ndf’
, SIZE = 2
, MAXSIZE = 50
, FILEGROWTH = 1)
TO FILEGROUP shazemployee2
GO
–Add the third filegroup
ALTER DATABASE shaz
ADD FILEGROUP shazemployee3
GO
–Add a database file to the third filegroup
ALTER DATABASE shaz
ADD FILE
( NAME = N’shazemployee3′
, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\shazemployee3.ndf’
, SIZE = 2
, MAXSIZE = 50
, FILEGROWTH = 1)
TO FILEGROUP shazemployee3
GO
–Add the fourth filegroup
ALTER DATABASE shaz
ADD FILEGROUP shazemployee4
GO
–Add a database file to the fourth filegroup
ALTER DATABASE [shaz]
ADD FILE
( NAME = N’shazemployee4′
, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\shazemployee4.ndf’
, SIZE = 2
, MAXSIZE = 50
, FILEGROWTH = 1)
TO FILEGROUP shazemployee4
GO
The file have been created, do note the size of the files and they would increase once the partitioning will be completed.
Step 3 : Create a partition function.
The partition function created will be used to divide the data horizontally on basis of the values provided. For e.g. in the below syntax the function will partition the values in the range of 0-49999, 49999-74999,75000-99999,100000 – End
CREATE PARTITION FUNCTION employee_PFN(INT)
AS
RANGE RIGHT FOR VALUES (50000,75000,100000)
GO
Step 4 : Create a partition scheme
In the partition scheme the file groups are specified on which the partition function is going to be applies so that the data can be migrated to the new file groups.
CREATE PARTITION SCHEME [employee_PS]
AS
PARTITION [employee_PFN] TO
(shazemployee1, shazemployee2,
shazemployee3, shazemployee4 )
GO
Step 5 : Create partition by dropping an recreating the cluster index.
IF EXISTS (SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID(N’[dbo].[employee]‘)
AND name = N’PK_employee’)
ALTER TABLE [dbo].[employee] DROP CONSTRAINT [PK_employee]
GO
ALTER TABLE [dbo].[employee]
ADD CONSTRAINT [PK_employee]PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (ONLINE = OFF) ON [employee_PS] (ID)
GO
Step 6 : Verify if the data has been migrated to the file groups
SELECT TOP 10 *, $Partition.employee_PFN(id) PartitionNo
FROM dbo.[EMPLOYEE] WHERE id < 50000
SELECT TOP 10 *, $Partition.employee_PFN(id) PartitionNo
FROM dbo.[EMPLOYEE] WHERE id >= 50000 AND id < 75000
SELECT TOP 10 *, $Partition.employee_PFN(id) PartitionNo
FROM dbo.[EMPLOYEE] WHERE id >= 75000 AND id < 100000
SELECT TOP 10 *, $Partition.employee_PFN(id) PartitionNo
FROM dbo.[EMPLOYEE] WHERE id >= 100000
Step 7 : Shrink the file and after that the space is cleared from the primary file group as data has been migrated to new file groups.
File shrunk and now the primary file group is having only few MB’s used space.
In the same way table can be partitioned based on partition criteria. Partitioning the table help us manage data effectively and quickly. To learn more about partitioning you can refer to below MSDN articles
http://technet.microsoft.com/en-us/library/ms190787.aspx
Now let me show you all a demo to partition a table in database :
In this demo we have created a database name SHAZ. In SHAZ database we have a table name employee which has 320000 rows in single file group (primary). The table was having on cluster index PK_employee on table employee.
We have created four different file group and data files. Each data file is allocated to each file group and then we have tried to partition the table on the basis of the ID column. As per our partitioning 0-49999 ID will be in file group 1, 50000-74999 will be in file group 2 , 75000-99999 will be in file group 3 and greater that 100000 will be in file group 4.
Once the partitioning will be completed we can shrink the primary file group which was earlier holding the data and release the space.
Pre checks before doing partitioning :
- Make sure the SQL Server version is enterprise edition on which partitioning has to be done.
- Find out the column name on which the partitioning will be done, generally column with date time are chosen on which the partitioning is being done.
- You can’t restore a database with partitioning to a non-Enterprise Edition instance. 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
- Size of MDF or NDF on which the table resides before partitioning.
5. Size of the table and number of rows.
6. Table Structure and take a note of all cluster and non-cluster indexes.
Step by Step procedure to partition a table in SQL SERVER
Step 1 : Take a full backup of the database which is going to be partitioned as a safety measure.
Step 2 : Identify the number of partition you would make and if you want to keep separate partition on separate file group then you need to create those many file group and secondary files. In our demo we will be creating four partition thus we are creating four secondary files and placing them on there respective file groups.
USE shaz
GO
–Add the first filegroup
ALTER DATABASE shaz
ADD FILEGROUP shazemployee1
GO
–Add a database file to the first filegroup
ALTER DATABASE shaz
ADD FILE
( NAME = N’shazemployee1′
, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\shazemployee1.ndf’
, SIZE = 2
, MAXSIZE = 50
, FILEGROWTH = 1)
TO FILEGROUP shazemployee1
GO
–Add the second filegroup
ALTER DATABASE shaz
ADD FILEGROUP shazemployee2
GO
–Add a database file to the second filegroup
ALTER DATABASE shaz
ADD FILE
( NAME = N’shazemployee2′
, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\shazemployee2.ndf’
, SIZE = 2
, MAXSIZE = 50
, FILEGROWTH = 1)
TO FILEGROUP shazemployee2
GO
–Add the third filegroup
ALTER DATABASE shaz
ADD FILEGROUP shazemployee3
GO
–Add a database file to the third filegroup
ALTER DATABASE shaz
ADD FILE
( NAME = N’shazemployee3′
, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\shazemployee3.ndf’
, SIZE = 2
, MAXSIZE = 50
, FILEGROWTH = 1)
TO FILEGROUP shazemployee3
GO
–Add the fourth filegroup
ALTER DATABASE shaz
ADD FILEGROUP shazemployee4
GO
–Add a database file to the fourth filegroup
ALTER DATABASE [shaz]
ADD FILE
( NAME = N’shazemployee4′
, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\shazemployee4.ndf’
, SIZE = 2
, MAXSIZE = 50
, FILEGROWTH = 1)
TO FILEGROUP shazemployee4
GO
The file have been created, do note the size of the files and they would increase once the partitioning will be completed.
Step 3 : Create a partition function.
The partition function created will be used to divide the data horizontally on basis of the values provided. For e.g. in the below syntax the function will partition the values in the range of 0-49999, 49999-74999,75000-99999,100000 – End
CREATE PARTITION FUNCTION employee_PFN(INT)
AS
RANGE RIGHT FOR VALUES (50000,75000,100000)
GO
Step 4 : Create a partition scheme
In the partition scheme the file groups are specified on which the partition function is going to be applies so that the data can be migrated to the new file groups.
CREATE PARTITION SCHEME [employee_PS]
AS
PARTITION [employee_PFN] TO
(shazemployee1, shazemployee2,
shazemployee3, shazemployee4 )
GO
Step 5 : Create partition by dropping an recreating the cluster index.
IF EXISTS (SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID(N’[dbo].[employee]‘)
AND name = N’PK_employee’)
ALTER TABLE [dbo].[employee] DROP CONSTRAINT [PK_employee]
GO
ALTER TABLE [dbo].[employee]
ADD CONSTRAINT [PK_employee]PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (ONLINE = OFF) ON [employee_PS] (ID)
GO
Step 6 : Verify if the data has been migrated to the file groups
SELECT TOP 10 *, $Partition.employee_PFN(id) PartitionNo
FROM dbo.[EMPLOYEE] WHERE id < 50000
SELECT TOP 10 *, $Partition.employee_PFN(id) PartitionNo
FROM dbo.[EMPLOYEE] WHERE id >= 50000 AND id < 75000
SELECT TOP 10 *, $Partition.employee_PFN(id) PartitionNo
FROM dbo.[EMPLOYEE] WHERE id >= 75000 AND id < 100000
SELECT TOP 10 *, $Partition.employee_PFN(id) PartitionNo
FROM dbo.[EMPLOYEE] WHERE id >= 100000
Step 7 : Shrink the file and after that the space is cleared from the primary file group as data has been migrated to new file groups.
File shrunk and now the primary file group is having only few MB’s used space.
