史丹利好熱

Docs上對TRUNCATE TABLE (Transact-SQL)的解釋

移除所有資料列的資料表或指定的分割區資料表，而不記錄個別資料列刪除。

TRUNCATE TABLE 類似於不含 WHERE 子句的 DELETE 陳述式；不過，TRUNCATE TABLE 比較快，使用的系統資源和交易記錄資源也比較少。

---

建立測試環境

建立資料庫，檔案群組、檔案

--CREATE DataBase [PartitionTableTruncateTestDb]
GO
alter database [PartitionTableTruncateTestDb] add filegroup [PartFG01];
alter database [PartitionTableTruncateTestDb] add filegroup [PartFG02];
GO
alter database [PartitionTableTruncateTestDb] add file ( name=[PartFGFile01],filename='T:\SQL\PartFGFile01.ndf',size=10MB) TO FileGroup[PartFG01]
alter database [PartitionTableTruncateTestDb] add file ( name=[PartFGFile02],filename='T:\SQL\PartFGFile02.ndf',size=10MB) TO FileGroup[PartFG02]

 

建立分割函數及分割配置Scheme(這邊我們想用考試成績作為分割條件，60分及格的一組，不及格的一組)。

CREATE PARTITION FUNCTION PFInt(int) AS RANGE RIGHT FOR VALUES (60)
GO

CREATE PARTITION SCHEME [PSchemeInt] AS PARTITION [PFInt] TO ([PartFG01], [PartFG02])
GO

 

驗證分割函數是否符合預期

SELECT
	59 AS val,$PARTITION.PFInt(59) AS partition UNION ALL
SELECT
	60,$PARTITION.PFInt(60) UNION ALL
SELECT
	61,$PARTITION.PFInt(61)

 

59分是分割1，60分是分割2

 

---

建立分割資料表

新增5個人的成績，巧虎、琪琪、桃樂比、妙妙、小花，但是這次考試琪琪、桃樂比都不及格。

DROP TABLE IF EXISTS [TestPartitionTable]
CREATE TABLE [dbo].[TestPartitionTable](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nchar](300) NOT NULL,
	[Score] [int] NOT NULL,
CONSTRAINT [PK_TestPartitionTable] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[Score] ASC
) ON [PSchemeInt](Score)
)  ON [PSchemeInt](Score)


INSERT INTO [TestPartitionTable]
	VALUES ('巧虎', 100), ('琪琪', 50), ('桃樂比', 40), ('妙妙 ', 80), ('小花 ', 70)

 

查詢分割內的筆數及資料所在的分割

--查詢分割內的資料筆數
SELECT
	OBJECT_NAME(object_id) AS table_name
	,partition_number
	,row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TestPartitionTable')

--查詢資料所在的分割
SELECT 
%%lockres%% as lockres
,%%physloc%% as physloc
,b.file_id
,b.page_id
,b.slot_id
,$PARTITION.PFInt(score) as partition_number
,a.* from [TestPartitionTable] a 
 CROSS APPLY fn_PhysLocCracker(%%physloc%%) b

 

分割1(不及格)有2筆，分割2(及格)有3筆

 

---

指定Partition Number Truncate

TRUNCATE TABLE [TestPartitionTable] 
  WITH(Partitions(1))

也可以指定區間WITH (PARTITIONS (6 TO 8))或清單WITH (PARTITIONS (1, 5))

 

重新查詢資料表，只剩下分割2的資料了

---

刪除大型交易資料表(刪除 vs 截斷)

這樣或許不能展現他的對比效果，來把資料撐大一點，比較刪除和截斷的差異

TRUNCATE TABLE TestPartitionTable
INSERT INTO [TestPartitionTable]
	VALUES ('巧虎', 100), ('琪琪', 50), ('桃樂比', 40), ('妙妙 ', 80), ('小花 ', 70)
GO

INSERT INTO TestPartitionTable SELECT  name,score FROM  TestPartitionTable
GO 20  

EXEC sp_spaceused 'TestPartitionTable'

 

大概58秒完成，我們現在擁有一個524萬筆資料，大概3GB的資料表

查詢分割內的筆數

SELECT
	OBJECT_NAME(object_id) AS table_name
	,partition_number
	,row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('TestPartitionTable')

 

分割1: 209萬，分割2: 314萬

比較兩個分割資料的移除時間

實驗組

SET STATISTICS IO ON
SET STATISTICS TIME ON
TRUNCATE TABLE [TestPartitionTable] 
  WITH(Partitions(2))

SQL Server 執行次數:

CPU 時間 = 0 ms，經過時間 = 1 ms。

 

對照組(刪除剩下的資料表)

SET STATISTICS IO ON
SET STATISTICS TIME ON
DELETE [TestPartitionTable] 

資料表 'TestPartitionTable'。掃描計數 2，邏輯讀取 2258229，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

SQL Server 執行次數:

CPU 時間 = 6235 ms，經過時間 = 15485 ms。

 

---

小結:

移除資料方法	筆數	時間
刪除(Delete)	2,097,152	15,485ms
截斷(Truncate)	3,145,728	1ms

 

SQL 2016也下放有限制的Partition Table功能到Standard版。

 

---

參考

Docs TRUNCATE TABLE (Transact-SQL)