SQL Server Portal

Truncate table is always my preference when I need to delete all the records from any table. The reason for the preference is, actually Truncate Table is faster than delete and it takes less system and transaction log resources. However, there is a problem with TRUNCATE TABLE and that is when you EXECUTE TRUNCATE TABLE, it actually deletes all the records in the table and you cannot specify any criteria (WHERE CLAUSE) for deletion like Delete.

The Good News is in SQL Server 2016 you can specify the partition you want to delete using Truncate Table.

Let me create a sample partition table and insert some sample data in that table and then demonstrate how it works in few easy steps.

Sample :

USE master
GO
ALTER DATABASE [Sample_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
--Drop Sample database
DROP DATABASE [Sample_DB]
GO
--Create Sample database
CREATE DATABASE Sample_DB
GO

-- Create Filegroups
ALTER DATABASE Sample_DB ADD FILEGROUP [2014]
GO
ALTER DATABASE Sample_DB ADD FILEGROUP [2015]
GO

--Create one file for each filegroup and
--so that you can store partition data

ALTER DATABASE Sample_DB
ADD FILE
(NAME = N'FG_2014',
FILENAME = N'C:\Data\FG_2014.ndf'
)
TO FILEGROUP [2014]
GO

ALTER DATABASE Sample_DB
ADD FILE
(NAME = N'FG_2015',
FILENAME = N'C:\Data\FG_2015.ndf'
)
TO FILEGROUP [2015]
GO

USE Sample_DB
GO

-- Drop the partition function if it exists
IF( EXISTS( SELECT * FROM sys.partition_functions
WHERE name = 'OrderDateRangePFN' ) )
BEGIN
DROP PARTITION FUNCTION OrderDateRangePFN
END
GO

--Create partition function
CREATE PARTITION FUNCTION OrderDateRangePFN(DATETIME)  AS
RANGE LEFT FOR VALUES
('2014-12-31',
'2015-12-31'
)
GO

-- Drop the partition Scheme if it exists
IF( EXISTS( SELECT * FROM sys.partition_schemes
WHERE name = 'OrderDateRangePScheme' ) )
BEGIN
DROP PARTITION SCHEME OrderDateRangePScheme
END
GO

--Create partition scheme
CREATE PARTITION SCHEME OrderDateRangePScheme AS
PARTITION OrderDateRangePFN  TO
([2014],
[2015],
[PRIMARY]  )
GO

-- Drop sample table if exists
IF( OBJECT_ID( 'tbl_Sample', 'U' ) IS NOT NULL )
BEGIN
DROP TABLE tbl_Sample
END
GO

-- Create sample table
CREATE TABLE tbl_Sample
(OrderID INT NOT NULL,
[OrderDate] DATETIME)
ON OrderDateRangePScheme ([OrderDate]);
GO

-- Insert some sample data
insert into tbl_Sample Values (1,'2014-01-01')
insert into tbl_Sample Values (2,'2014-02-01')
insert into tbl_Sample Values (3,'2014-03-01')
insert into tbl_Sample Values (4,'2015-01-01')
insert into tbl_Sample Values (5,'2015-02-01')
insert into tbl_Sample Values (6,'2015-03-01')
insert into tbl_Sample Values (7,'2015-04-01')
insert into tbl_Sample Values (8,'2016-01-01')
insert into tbl_Sample Values (9,'2016-02-01')
GO

SELECT * FROM tbl_Sample
GO

Step 1 :
Lets find out if the data exists in the partitions properly. Given below is the script.

USE Sample_DB
GO
-- Check if data exists in the partition properly
SELECT OBJECT_NAME(OBJECT_ID) AS [Table Name]
, partition_number AS [Partition Number]
, rows AS [Number of rows]
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tbl_Sample';
GO

Step 2 :
Lets TRUNCATE TABLE the data of PARTITION 2 in the sample table. Given below is the script.

USE Sample_DB
GO
TRUNCATE TABLE tbl_Sample
WITH (PARTITIONS (2));
GO

Step 3 :
Lets find out if the data of partition 2 has been deleted in the sample table or as usual TRUNCATE TABLE has deleted all the data in the table. Given below is the script.

USE Sample_DB
GO
-- Check if data exists in the partition properly
SELECT OBJECT_NAME(OBJECT_ID) AS [Table Name]
, partition_number AS [Partition Number]
, rows AS [Number of rows]
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tbl_Sample';
GO

Wow, as expected only partition 2 data has been deleted from sample data. This feature is very helpful for the DBAs handling lots of partition in a single table and if they want to delete any particular partition(s) data quickly.

Reference : MSDN

Read Full Post »