SE の雑記

SQL Database V12 Preview では新しい機能としてテーブルのパーティショニングがサポートされました。
この機能を少し試してみたいと思います。

テーブルのパーティショニング自体は、SQL Server 2005 の Enterprise Edition からサポートされており、V12 になって SQL Database でも利用できるようになりました。
テーブルのパーティショニングをする目的としては、

1. 複数のファイルグループを使用することでパーティション単位にディスク I/O を分散させる
2. 特定のパーティションのデータをアーカイブテーブルにスイッチ
3. アーカイブテーブルへのスイッチ→ アーカイブテーブルTruncate を実施することで、特定パーティションのデータを瞬時に削除
   (DROP PARTITION がないため、一度他のテーブルに移し、Truncate Table する必要があります。)
4. パーティション単位でインデックスのメンテナンス

というようなことが考えられるかと思います。
上記の 3 つの理由で考えた場合、SQL Database のパーティショニングでは [2] ～ [4] に対応することができます。
V12 の新機能として、DMV の拡充があります。
DMV と合わせて、システムビューも拡充されており、様々な情報が取得できるようになりました。
# Preview なので GA した際には動作が変わる可能性があります。
以下のクエリを実行してみます。

SELECT * FROM sys.filegroups
SELECT * FROM sys.database_files

 
このクエリではファイルグループとデータベースのファイルの情報を取得することができます。
# 本投稿で実行しているクエリは V12 Preview でのみ動作可能なものが大半です。現行のバージョンでは動作しません。

SQL Database では、 PRIMARY のファイルグループを使用しており、データファイルとログファイルの 2 ファイルでデータベースが構成されていることが確認できます。
SQL Database では PRIMARY ファイルグループのみがサポートされているため、ファイルグループを追加して、パーティションを分散させるということができません。
それでは、実際にパーティションテーブルを作成してみたいと思います。
V12 ではヒープテーブルが作成できるようになりましたので、今回はヒープで作成してイン佐生。

CREATE PARTITION FUNCTION PF_PTTEST(datetime2(0))
AS RANGE RIGHT FOR
VALUES ('2011-01-01', '2012-01-01', '2013-01-01', '2014-01-01', '2015-01-01', '2016-01-01')
GO
CREATE PARTITION SCHEME PS_PTTEST
AS PARTITION PF_PTTEST ALL TO([PRIMARY])
GO
CREATE TABLE PTTEST
(Col1 uniqueidentifier,
Col2 datetime2(0))
ON PS_PTTEST(Col2)

 
今回は日付を使用したパーティションテーブルを作成しています。
SQL Database では、PRIMARY ファイルグループのみが使用できますので、すべてのパーティションのデータは PRIMARY ファイルグループに格納をするように定義しています。
# 物理的なデータの配置ディスクを分割するのではなく、単一のファイルグループを使用した論理的な分割を行います。
このテーブルに対して以下のクエリでデータを挿入します。

SET NOCOUNT ON
GO
DECLARE @cnt int = 0, @datetime datetime2(0) = '2010-01-01'
BEGIN TRAN
WHILE (@cnt <= 100000)
BEGIN
	INSERT INTO PTTEST VALUES(NEWID(), @datetime)
	SET @cnt += 1
	SET @datetime = DATEADD(d, 1, @datetime)
	IF @datetime >= '2017-01-01' SET @datetime = '2010-01-01'
END
COMMIT TRAN

 
どのようなデータが入っているかを確認してみます。

SELECT
	MIN(Col2),MAX(Col2)
FROM
	PTTEST
SELECT
	$PARTITION.PF_PTTEST(Col2) AS PartitionNo,
	COUNT(*), MIN(Col2), MAX(Col2)
FROM
	PTTEST
GROUP BY
	$PARTITION.PF_PTTEST(Col2)

2010-01-01 から 2016-12-31 までのデータがパーティションに分散されて格納されているのが確認できます。
パーティションの構成も確認をしてみたいと思います。

SELECT
	*
FROM
	sys.partition_range_values prv
	INNER JOIN
	sys.partition_functions pf
	ON
	prv.function_id = pf.function_id
	AND
	pf.name = 'PF_PTTEST'

設定した構成でパーティションが作成されているのが確認できますね。
それでは、次にパーティションのスイッチをしてみたいと思います。

CREATE TABLE PTTEST_ARCHIVE
(Col1 uniqueidentifier,
Col2 datetime2(0))
ALTER TABLE PTTEST
SWITCH PARTITION 2
TO PTTEST_ARCHIVE

2 番目のパーティションを移動しましたので、2011 年のデータが移動されているはずですね。
先ほどの確認用のクエリを実行して確認をしてみます。

2011 年のデータが移動されていますね。
アーカイブ用のテーブルを確認すると、2011 年のデータが存在していることが確認できます。
 

SELECT
	MIN(Col2),MAX(Col2)
FROM
	PTTEST_ARCHIVE

今回は移動先は非パーティションテーブルですが、アーカイブされたデータを戻すこともできます。
ここまでの状態で以下のスイッチを実行してみます。
 

ALTER TABLE PTTEST_ARCHIVE
SWITCH TO PTTEST
PARTITION 2

この状態では以下のエラーとなります。

アーカイブテーブルにはスイッチ先のパーティションに格納しようとしているデータ以外 (2011 年以外のデータ) が入っていることが保障されていません。
パーティションのスイッチで戻す場合には、スイッチ元のテーブルがスイッチ先のパーティションの範囲のデータであることを保証する必要があります。
そこで、以下のチェック制約を付与します。

ALTER TABLE PTTEST_ARCHIVE
ADD CONSTRAINT [CHK_PTTEST_ARCHIVE_01]
CHECK (Col2 BETWEEN '2011-01-01' AND '2011-12-31' AND Col2 IS NOT NULL)
GO

これにより、アーカイブからデータを戻すことが可能となります。
注意するポイントとしては NOT NULL を保証するというところです。
NOT NULL を保証していない場合、以下のエラーとなってしまいます。

 
操作方法としては、通常のパーティションテーブルを触るのと同じ方法で使用することができそうですね。