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 masterGOCREATE DATABASE PartitionDBON 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;GOALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG2;GOALTER DATABASE PartitionDB ADD FILEGROUP PartitionFG3;GOALTER 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]GOCREATE TABLE PartitionTable    (    MyID int NOT NULL,    MyDate datetime NULL,    Name varchar(50) NULL    )  ON PartScheme1(MyID)GOUSE PartitionDBgoCREATE UNIQUE CLUSTERED INDEX IX_PartitionTableON PartitionTable(MyID)ON PartScheme1 (MyID);GO USE PartitionDBgoINSERT 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 PartitionDBGOSELECTOBJECT_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 partJOIN sys.indexes idxON part.object_id = idx.object_idAND part.index_id = idx.index_id JOIN sys.partition_schemes pshON psh.data_space_id = idx.data_space_idJOINsys.partition_functions fncON fnc.function_id = psh.function_id LEFTJOIN sys.partition_range_values prvON fnc.function_id = prv.function_idAND part.partition_number = prv.boundary_idJOIN sys.destination_data_spaces ddsON dds.partition_scheme_id = psh.data_space_idAND dds.destination_id = part.partition_numberJOIN sys.filegroups fgON dds.data_space_id = fg.data_space_idJOIN (SELECT container_id, sum(total_pages) as total_pagesFROMsys.allocation_units GROUP BY container_id) AS auON au.container_id = part.partition_id JOIN sys.tables t ONpart.object_id = t.object_id WHERE idx.index_id < 2ORDER 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]GODROP 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]GOCREATE 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

You 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.

I hope this article was helpful in understanding how we can remove Partitioning from table.