SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

2015/8/3 時点の SQL Database v12 のパーティション単位の TRUNCATE の問題点

leave a comment

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 までのパーティションが作成されていることが確認できますね。

image

それではパーティション 1 に対して TRUNCATE TABLE を実行してみます。

TRUNCATE TABLE HashTable WITH (PARTITIONS (1))

image

実行後の件数が以下になるのですが、パーティション 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 では以下のような状態となります。

image

ベーステーブルに固定されていないインデックスはすべてのデータが削除されていますね。

これにより以下のようなクエリを実行した場合、本来であれば同一の件数が取得されるはずなのですが、インデックスについては 0 件になってしまっているので実行結果に差が出ます。

image

このような動作をしてしまうことで、ベーステーブルに固定されていないインデックスが使用された場合は結果にいろいろと不整合が出るケースがあるかと。

同様の動作をパーティション単位の 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 に翻訳こんにゃくを使いながらフィードバックはしてみました。

Share

Written by Masayuki.Ozawa

8月 3rd, 2015 at 8:41 pm

Posted in SQL Server

Tagged with

Leave a Reply