This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a new version of TRUNCATE command which used to truncate the partitions and newly introduced in SQL Server 2016.
Always we keen like why we cannot apply WHERE condition or any other filter in TRUNCATE Command. TRUNCATE command is faster than delete because it consumes the fewer resources than DELETE and it is not generating any transaction logs.
Now with the SQL Server 2016 you can perform TRUNCATE basis on Partition Number.
If you have a big size of the table and you already applied proper partitions on that table, you can use the new TRUNCATE command for truncating the data.
Below is a full demonstration on this:
CREATE SAMPLE DATABASE:
|
1 2 |
CREATE DATABASE TruncatePartitions GO |
ADD File Group to New Database:
|
1 2 3 |
ALTER DATABASE TruncatePartitions ADD FILEGROUP [2015] ALTER DATABASE TruncatePartitions ADD FILEGROUP [2016] GO |
Create .ndf file to store partitions data and attached to perticular filegroup:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
ALTER DATABASE TruncatePartitions ADD FILE (NAME = N'Partitions_2015', FILENAME = N'C:\PartitionsData\Partitions_2015.ndf' ) TO FILEGROUP [2015] ALTER DATABASE TruncatePartitions ADD FILE (NAME = N'Partitions_2016', FILENAME = N'C:\PartitionsData\Partitions_2016.ndf' ) TO FILEGROUP [2016] GO |
Create Partition functions:
|
1 2 3 4 5 6 |
CREATE PARTITION FUNCTION DateRangeFN(DATETIME) AS RANGE LEFT FOR VALUES ('2015-12-31', '2016-12-31' ) GO |
Create Partition scheme:
|
1 2 3 4 5 6 |
CREATE PARTITION SCHEME DateRangeScheme AS PARTITION DateRangeFN TO ([2015], [2016], [PRIMARY] ) GO |
Create Sample table:
|
1 2 3 4 5 |
CREATE TABLE tbl_SamplePartitionPartition (ID INT NOT NULL, [SampleDate] DATETIME) ON DateRangeScheme ([SampleDate]); GO |
Insert Sample data:
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO tbl_SamplePartition VALUES (1,'2015-08-06') INSERT INTO tbl_SamplePartition VALUES (2,'2015-05-08') INSERT INTO tbl_SamplePartition VALUES (3,'2015-04-02') INSERT INTO tbl_SamplePartition VALUES (4,'2015-06-25') INSERT INTO tbl_SamplePartition VALUES (5,'2015-09-26') INSERT INTO tbl_SamplePartition VALUES (6,'2016-02-12') INSERT INTO tbl_SamplePartition VALUES (7,'2016-08-16') INSERT INTO tbl_SamplePartition VALUES (8,'2016-04-08') INSERT INTO tbl_SamplePartition VALUES (9,'2016-09-14') GO |
Script to check, if the data exist in Partitions:
|
1 2 3 4 5 6 7 |
SELECT OBJECT_NAME(OBJECT_ID) AS TableName , partition_number AS PartitionNumber , rows AS TotalRows FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='tbl_SamplePartition'; GO |
Final step to TRUNCATE Partitions by Partition Number:
|
1 2 |
TRUNCATE TABLE tbl_Sample WITH (PARTITIONS (1)); GO |
