SQL Server 2016、SQL Database v12 では TRUNCATE TABLE で WITH (PARTITIONS) が使用できるので、パーティション単位での TRUNCATE TABLE が実行できるようになっています。
これにより、スイッチせずに特定のパーティションを瞬時に削除できるのですが、2015/8/3 時点の SQL Database v12 で使用する場合、少し注意する点がありましたので、まとめてみたいと思います。
追記 : 現状の SQL Database では動作に問題はありません。
最初に以下のクエリでデータを作成します。
IF OBJECT_ID('HashTable') IS NOT NULL
DROP TABLE HashTable
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'HashTable_PS')
DROP PARTITION SCHEME HashTable_PS
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'HashTable_PF')
DROP PARTITION FUNCTION HashTable_PF
CREATE PARTITION FUNCTION [HashTable_PF](int) AS RANGE RIGHT FOR VALUES (1,2,3)
GO
CREATE PARTITION SCHEME [HashTable_PS] AS PARTITION [HashTable_PF] ALL TO ([PRIMARY])
GO
CREATE TABLE [dbo].[HashTable](
[Col1] int IDENTITY NOT NULL,
[Col2] nvarchar (36),
[PartitionHash] AS ABS(CONVERT(int,HASHBYTES('MD2',CONVERT(nvarchar(10), Col2)))) % 4 PERSISTED NOT NULL
)
CREATE CLUSTERED INDEX CIX_HashTable ON HashTable (Col1) ON HashTable_PS(PartitionHash)
SET NOCOUNT ON
DECLARE @cnt int = 1
BEGIN TRAN
WHILE (@cnt <= 5000)
BEGIN
INSERT INTO HashTable(Col2) VALUES(NEWID())
SET @cnt += 1
END
COMMIT TRAN
このテーブルに対して以下のクエリでデータを確認してみると以下のような情報が取得できます。
SELECT OBJECT_NAME(sp.object_id) , si.name , sp.partition_number , sp.rows , sp.data_compression_desc , si.type_desc FROM sys.partitions sp LEFT JOIN sys.indexes as si ON si.object_id = sp.object_id AND si.index_id = sp.index_id WHERE sp.object_id IN (OBJECT_ID(N'dbo.HashTable')) GO
1～4 までのパーティションが作成されていることが確認できますね。
それではパーティション 1 に対して TRUNCATE TABLE を実行してみます。
TRUNCATE TABLE HashTable WITH (PARTITIONS (1))
実行後の件数が以下になるのですが、パーティション 1 を TRUNCATE していますので想定通りの動作ですね。
それでは、このテーブルの構成が以下のような場合はどうなるでしょう。
CREATE TABLE [dbo].[HashTable](
[Col1] int IDENTITY NOT NULL,
[Col2] nvarchar (36),
[PartitionHash] AS ABS(CONVERT(int,HASHBYTES('MD2',CONVERT(nvarchar(10), Col2)))) % 4 PERSISTED NOT NULL
)
CREATE CLUSTERED INDEX CIX_HashTable ON HashTable (Col1) ON HashTable_PS(PartitionHash)
ALTER TABLE HashTable WITH CHECK ADD CONSTRAINT PK_HashTable PRIMARY KEY NONCLUSTERED (Col1) ON [PRIMARY]
GO
新たにインデックスを設定していますが、このインデックスはテーブルと同様の定義のパーティション分割はされておらず、1 パーティションのみの構成となります。
それでは、この状態で、パーティション 1 を TRUNCATE するとどうなるでしょう。
SQL Database v12 では以下のような状態となります。
ベーステーブルに固定されていないインデックスはすべてのデータが削除されていますね。
これにより以下のようなクエリを実行した場合、本来であれば同一の件数が取得されるはずなのですが、インデックスについては 0 件になってしまっているので実行結果に差が出ます。
このような動作をしてしまうことで、ベーステーブルに固定されていないインデックスが使用された場合は結果にいろいろと不整合が出るケースがあるかと。
同様の動作をパーティション単位の TRUNCATE が実装されている SQL Server 2016 で実行すると以下のようなエラーとなり実行することはできません。
メッセージ 3756、レベル 16、状態 1、行 1 TRUNCATE TABLE statement failed. Index 'PK_HashTable' is not partitioned, but table 'HashTable' uses partition function 'HashTable_PF'. Index and table must use an equivalent partition function.
動作としては SQL Server 2016 のものが正しいのでしょうね。
製品によって差が出るのはよろしくない内容ですので、Connect に翻訳こんにゃくを使いながらフィードバックはしてみました。