Category Archives: Partitioning

How to Remove Table Partitioning in SQL Server

In this article we will see how we can remove partitions from a table in a database in SQL server. In my previous post i had demonstrated how we can partition a table via T-SQL. Lets now remove the partitions and merge the data in a single partition. I will start from where we left off in my previous post of partitioning a table.

1) Run the below code to create a database named PartitionDB that would include a table that has been partitioned.

USE master
GO
CREATE DATABASE PartitionDB
ON PRIMARY (NAME = N'PartitionDB'
,FILENAME = N'D:\MSSQL\Data\PartitionDB.mdf'
,SIZE = 50MB, FILEGROWTH = 150MB)
LOG ON (
NAME = N'PartitionDB_log'
,FILENAME = N'D:\MSSQL\Logs\PartitionDB_log.ldf'
,SIZE = 10MB, FILEGROWTH = 100MB);
GO

ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG1;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG2;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG3;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG4;
GO

ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile1,
        FILENAME = 'D:\MSSQL\Data\PartitionFile1.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG1;
GO

ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile2,
        FILENAME = 'D:\MSSQL\Data\PartitionFile2.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG2;
GO

ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile3,
        FILENAME = 'D:\MSSQL\Data\PartitionFile3.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG3;
GO

ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile4,
        FILENAME = 'D:\MSSQL\Data\PartitionFile4.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG4;
GO

CREATE PARTITION FUNCTION PartFunc1 (int)
    AS RANGE LEFT FOR VALUES (10, 20, 30);
GO

CREATE PARTITION SCHEME PartScheme1
    AS PARTITION PartFunc1
    TO (PartitionFG1, PartitionFG2,PartitionFG3,PartitionFG4);
GO

USE [PartitionDB]
GO
CREATE TABLE PartitionTable
    (
    MyID int NOT NULL,
    MyDate datetime NULL,
    Name varchar(50) NULL
    )  ON PartScheme1(MyID)
GO
USE PartitionDB
go
CREATE UNIQUE CLUSTERED INDEX IX_PartitionTable
ON PartitionTable(MyID)
ON PartScheme1 (MyID);
GO

USE PartitionDB
go
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (1,GETDATE(),'Rooney');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (11,GETDATE(),'Van persie');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (22,GETDATE(),'De Gea');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (34,GETDATE(),'Moyes');
GO

Run the below code to see the details of the partitioned table

USE PartitionDB
GO
SELECT
OBJECT_NAME(idx.object_id) AS TableName ,
psh.name AS PartitionSchemeName ,
fnc.name AS PartitionFunctionName,
part.partition_number AS PartitionNumber ,
fg.name AS [Filegroup],
rows AS 'No of Records' ,
CASE boundary_value_on_right WHEN 1 THEN 'less than'
ELSE 'less than or equal to' END AS 'Condition',
value AS 'Range' ,
part.partition_id AS [Partition Id] FROM sys.partitions part
JOIN sys.indexes idx
ON part.object_id = idx.object_id
AND part.index_id = idx.index_id JOIN sys.partition_schemes psh
ON psh.data_space_id = idx.data_space_id
JOIN
sys.partition_functions fnc
ON fnc.function_id = psh.function_id LEFT
JOIN sys.partition_range_values prv
ON fnc.function_id = prv.function_id
AND part.partition_number = prv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = psh.data_space_id
AND dds.destination_id = part.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM
sys.allocation_units GROUP BY container_id) AS au
ON au.container_id = part.partition_id JOIN sys.tables t ON
part.object_id = t.object_id WHERE idx.index_id < 2
ORDER BY TableName,part.partition_number;
GO

Now let us understand how we can remove the partitions from this table.
The easiest way to do this is to drop the Clustered index from this table and recreate it on another filegroup.

Step 1: Drop the clustered index from the table

USE [PartitionDB]
GO
DROP INDEX [IX_PartitionTable] ON
[dbo].[PartitionTable] WITH ( ONLINE = OFF )
GO

Step 2: Re-create the clustered index on another Filegroup. We will use the primary FG as example

USE [PartitionDB]
GO
CREATE UNIQUE CLUSTERED INDEX [IX_PartitionTable] ON
[dbo].[PartitionTable]
(
 [MyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

Step 3: Verify the state of the partitions by running the below code. You will find that there is only one partition with all the 4 rows in it.

USE PartitionDB
go
SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='partitiontable';
GO

P3You can verify the same via SSMS by performing the following steps:
Step 1: Right click on the table
Step 2: Click on properties
Step 3: Click on Storage
Step 4 : Verify that “Table is partitioned” is false.
P4I hope this article was helpful in understanding how we can remove Partitioning from table.

Advertisements

How to Partition a table in SQL Server

In this article we will see how we can partition a table in a database using T-SQL. We will first understand the basics of table partitioning in SQL Server and then go through the scripts that we will use to build a partitioned table. Let us start with the basics of partitioning.

What is table partitioning in SQL Server?
Partitioning is dividing a large table and its indexes into smaller fragments called partitions.

Some of the Benefits?
Its helps performing maintenance operation fragment by fragment basis rather that performing on the entire table. Apart from that SQL queries can be redirected to proper partitions directly rather than scanning the entire table.SQL queries that are properly filtered on the partition column can perform better by making use of partition elimination and parallelism. Archiving data is another major benefit of partitioning. We will not deep dive in this article on the benefits of partitioning.

Components of Partitioning:
1) Partition Function(PF): PF defines which rows goes into what partition in a partitioned table based on a range.
2) Partition Scheme(PS):The partition scheme defines how partitions will be stored on filegroups. Creating a partition scheme assumes that your database already has filegroups.

Let us now understand with an example how we can partition a table in SQL server.
1) Creating a database:

USE master
GO
CREATE DATABASE PartitionDB
ON PRIMARY (NAME = N'PartitionDB'
,FILENAME = N'D:\MSSQL\Data\PartitionDB.mdf'
,SIZE = 50MB, FILEGROWTH = 150MB)
LOG ON (
NAME = N'PartitionDB_log'
,FILENAME = N'D:\MSSQL\Logs\PartitionDB_log.ldf'
,SIZE = 10MB, FILEGROWTH = 100MB);
GO

2) Adding 4 new filegroups to the PartitionDB database.

ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG1;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG2;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG3;
GO
ALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG4;
GO

3) Adding files to the database filegroups.

 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile1,
        FILENAME = 'D:\MSSQL\Data\PartitionFile1.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG1;
GO
 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile2,
        FILENAME = 'D:\MSSQL\Data\PartitionFile2.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG2;
GO
 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile3,
        FILENAME = 'D:\MSSQL\Data\PartitionFile3.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG3;
GO

 

 ALTER DATABASE PartitionDB
    ADD FILE
    (
        NAME = PartitionFile4,
        FILENAME = 'D:\MSSQL\Data\PartitionFile4.ndf',
        SIZE = 20MB, MAXSIZE = 50MB, FILEGROWTH = 5MB
    )
    TO FILEGROUP PartitionFG4;
GO

4) Creating a Partition Function(PF):

 CREATE PARTITION FUNCTION PartFunc1 (int)
    AS RANGE LEFT FOR VALUES (10, 20, 30);
GO

5) Creating a Partition Scheme(PS):

CREATE PARTITION SCHEME PartScheme1
    AS PARTITION PartFunc1
    TO (PartitionFG1, PartitionFG2,PartitionFG3,PartitionFG4);
GO

6) Create a Table:–

USE [PartitionDB]
GO
CREATE TABLE PartitionTable
	(
	MyID int NOT NULL,
	MyDate datetime NULL,
	Name varchar(50) NULL
	)  ON PartScheme1(MyID)
GO

7) Create Index on Partitioned Table

USE PartitionDB
go
CREATE UNIQUE CLUSTERED INDEX IX_PartitionTable
ON PartitionTable(MyID)
ON PartScheme1 (MyID);
GO

8) Insert Data into the Table

USE PartitionDB
go
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (1,GETDATE(),'Rooney');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (11,GETDATE(),'Van persie');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (22,GETDATE(),'De Gea');
INSERT INTO PartitionTable (MyID, MyDate,name)
VALUES (34,GETDATE(),'Moyes');
GO

9) Verify data in the table

SELECT * FROM dbo.partitiontable

10) Verify Rows Inserted in Partitions

USE PartitionDB
go
SELECT * FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='partitiontable';
GO

P1
11) Run the below code to see the details of the partitioned table

USE PartitionDB
GO
SELECT
OBJECT_NAME(idx.object_id) AS TableName ,
psh.name AS PartitionSchemeName ,
fnc.name AS PartitionFunctionName,
part.partition_number AS PartitionNumber ,
fg.name AS [Filegroup],
rows AS 'No of Records' ,
CASE boundary_value_on_right WHEN 1 THEN 'less than'
ELSE 'less than or equal to' END AS 'Condition',
value AS 'Range' ,
part.partition_id AS [Partition Id] FROM sys.partitions part
JOIN sys.indexes idx
ON part.object_id = idx.object_id
AND part.index_id = idx.index_id JOIN sys.partition_schemes psh
ON psh.data_space_id = idx.data_space_id
JOIN
sys.partition_functions fnc
ON fnc.function_id = psh.function_id LEFT
JOIN sys.partition_range_values prv
ON fnc.function_id = prv.function_id
AND part.partition_number = prv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = psh.data_space_id
AND dds.destination_id = part.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM
sys.allocation_units GROUP BY container_id) AS au
ON au.container_id = part.partition_id JOIN sys.tables t ON
part.object_id = t.object_id WHERE idx.index_id &lt; 2
ORDER BY TableName,part.partition_number;
GO

P2 I hope this article was helpful in understanding how we can create table partitions via T-SQL.

%d bloggers like this: