[交流][转] Sql Server パーティショニングテーブルの概要について

公式ドキュメント

パーティションテーブルを作成する一般的な手順

1、ファイルグループ（Oracleのテーブルスペースに似ている）の確立は、もちろん、行を確立しない、ファイルグループ内のすべてのパーティションを置くこともできます。

2、確立パーティション機能、どのようなデータの割り当ての範囲です。

3、パーティショニングスキームの確立は、関連付けられているパーティション機能は、また、ファイルグループ、いくつかの範囲に分割されたデータのパーティション機能に関連付けられている、あなたはファイルのいくつかのグループに関連付けられている必要があります、もちろん、また、同じファイルグループにこれらのいくつかのパーティションの範囲を置くことができる

4、テーブルを確立し、関連付けられているパーティショニングスキーム

バグの発生

テーブルを直接右クリックしてテーブル構造をエクスポートする場合、データベース-タスク-スクリプト生成の右クリックでしかテーブルのパーティション情報をエクスポートできない。

パーティショニングされたテーブルのいくつかの結論：

1、パーティショニングされたフィールドは、必ずしもインデックスを作成する必要はない。

2、パーティショニングされたフィールドは、クラスタ化インデックスまたは非クラスタ化インデックスとして作成できる。

3、パーティショニングされたフィールドは、クラスタ化されたインデックスであれ、クラスタ化されていないインデックスであれ、クラスタ化されたインデックスとして再構築され、パーティショニングスキームとは関係なく、パーティショニングされたテーブルは非パーティショニングされたテーブルになる。

4、通常のテーブルがパーティションテーブルに変換され、限り、テーブルがパーティショニングスキームの使用上のクラスタ化インデックス、およびクラスタ化インデックスを作成することができます。たとえば、非分割テーブルのフィールド1は、クラスタ化されたインデックスを作成し、パーティショニングスキームに関連付けられている場合、テーブルはパーティションテーブルに変換され、パーティションフィールドはフィールド1です。

5、パーティションテーブルは、インデックスを持つパーティションフィールドがある場合、通常のテーブルに変換され、クラスター化されたインデックスとしてパーティションフィールドを再構築し、パーティショニングスキームに関連付けられていないインデックスなしでパーティションフィールドをすることができますし、新しいクラスター化されたインデックスを作成し、パーティショニングスキームに関連付けられていないパーティションフィールドをすることができます。

6、通常のテーブルにパーティションテーブルまたはパーティションテーブルに、あなただけのクラスタ化されたインデックスを達成するために使用することができますので、クラスタ化されたインデックスでは、テーブルの再分配を達成するためにクラスタ化されたインデックスの再構築を介して、テーブルの組織のインデックスです。通常のテーブルは、クラスタ化されたインデックスにパーティション分割されたフィールドを再構築し、パーティショニングスキームを関連付けることができるパーティション分割されたテーブルになり、通常のテーブルになり、クラスタ化されたインデックスにパーティション分割されたフィールドを再構築しないパーティショニングスキームを関連付けることができます。

7、パーティションテーブルは、一意性制約を作成するには、パーティション化された列を含める必要があります。

8、パーティションスキームを作成するには、ファイルグループの数は、パーティション範囲のセグメントのパーティション機能と一致するようにする必要があります、ファイルグループ名の重複は何もしていない、もちろん、あなたはまた、ALLを使用することができます、ファイルグループ名を指定するので、すべてのパーティション範囲のセグメントデータのパーティション機能は、このファイルグループにダウンしている。

9、パーティション機能とパーティションプログラムは、データベースではなく、全体のインスタンスに直面している。

10、パーティションテーブルが大きすぎるディスク容量の多くを占め、サイズの後にいくつかのフィールドを削除したり、共通のテーブルにパーティションマージまたはパーティションテーブルのこの時間は、サイズがダウンします変更されません！

パーティションテーブルを作成する手順

1.1ファイルグループを作成する例

変更データベースtest1はファイルグループpart1を追加します；

alter database test1 add filegroup part1000；

alter database test1 add filegroup part1000; alter database test1 add filegroup part2000；

alter database test1 add filegroup part1000; alter database test1 add filegroup part2000; alter database test1 add filegroup part3000；

1.2、ファイルの例を作成し、関連するファイルグループ

ALTER DATABASE test1 ADD FILE(NAME = test1part1,FILENAME = 'G:◆test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP part1.

ALTER DATABASE test1 ADD FILE(NAME = test1part1000,FILENAME='G:◆test1part1000.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)をFILEGROUPに追加する。part1000; ALTER DATABASE test1 ADABASE

ALTER DATABASE test1 ADD FILE(NAME = test1part2000,FILENAME = 'G:◆test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUPpart2000に追加します。

ALTER DATABASE test1 ADD FILE(NAME = test1part3000,FILENAME='G:◆test1part3000.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)をFILEGROUPに追加します。PART3000; ALTER DATABASE test1 ADABASE

ALTER DATABASE test1 ADD FILE(NAME = test1part4000,FILENAME = 'G:♪test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUPpart4000に保存する。

2、パーティション関数の確立の例、パーティション関数名partfun1

CREATE PARTITION FUNCTION partfun1 (int)

左の値の範囲として ('1000','2000','3000','4000')

--VALUES（'1000'、'2000'、'3000'、'4000'）は、テーブルが5つのゾーンに分割されることを示し、パーティションにテーブルのフィールドの値のサイズに基づいている、5つのゾーンは最小です - 1000、1000から2000、2000から3000、3000から4000、4000 - 最大！

3、プログラムの例の確立は、関連付けられているパーティション関数partfun1、関連するファイルグループ

パーティションスキームpartschema1を作成します。

AS PARTITION partfun1

TO (part1,part1000,part2000,part3000,part4000).

--part1,part1000,part2000,part3000,part4000にビルドする。

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

TO (part1,[PRIMARY],[PRIMARY],[PRIMARY])；

--part1、[PRIMARY]ファイルグループでビルドし、part1を[PRIMARY]に置き換えても問題ありませんので、[PRIMARY]ファイルグループ上のすべてのビルドと同様です。

CREATE PARTITION SCHEME partschema3

AS PARTITION partfun1

ALL TO (part1)。

--すべてpart1ファイル・グループへ

CREATE PARTITION SCHEME partschema4

AS PARTITION partfun1

ALL TO ([PRIMARY]); --すべて[PRIMARY]上に作成。

--PRIMARY]ファイル・グループ上に作成されたもの。

4.パーティショニングされたテーブルの作成例

CREATE TABLE parttable1(

[ID] [int] NOT NULL, [IDText] [nv]; --すべて[PRIMARY]ファイル・グループを作成する。

[IDText] [nvarchar](max) NULL、

[日付] [datetime] NULL)

ON [partschema1](ID).

parttable1 value (1,'1',getdate()-4) に挿入します；

parttable1 value (1001,'1001',getdate()-3) に挿入します；

parttable1 の値 (4001,'4001',getdate()) に挿入します；

パーティショニングされたテーブルのデータを検証する。

SELECT * FROM parttable1。

--パーティションテーブルのすべての行を返す

SELECT distinct $PARTITION.[partfun1](4) FROM parttable1；

--IDフィールド値が4の行が属するパーティションを返します。

SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2

--IDはパーティション・フィールドIDです。

注：SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2がパーティションテーブルであるという結果を持っているためではなく、この論文7の最後のテストは、テーブルが非パーティションテーブルですが、SELECT * FROM parttable1 where $PARTITION.[[partfun1](ID)=2の実装は、テーブルがパーティションテーブルであるという結果を持っている、この論文最後のテスト7は、テーブルが非パーティションテーブルですが、SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2の実装は、テーブルがパーティションテーブルであるという結果を持っている、SELECT * FROM parttable1 where $PARTITION.[partfun1](ID)=2の実装は、テーブルがパーティションテーブルであるという結果を持っている。partfun1](ID)=2はまだ結果を持つ

新しいパーティションを追加する

1、使用可能なパーティション・スキームのファイル・グループを指定します（新しいパーティション・スキームのファイル・グループ）。

2、パーティション関数を修正する（データ範囲の新しいパーティション関数）

ALTER PARTITION SCHEME partschema1 NEXT USED [PRIMARY].

ALTER PARTITION FUNCTION partfun1() SPLIT RANGE ('4500')

select p.partition_number,p.rows from sys.indexes i inner join sys.partitions p on p.object_id = i.object_id and i.object_id = object_id('parttable2') order by 1

--パーティショニング・スキームがALL TO ([PRIMARY])を使用している場合、最初のステートメントは実行されません。

--2番目の文は、4000-4500の範囲に新しいパーティションを追加します。

--3番目の文は、新しいパーティションが存在し、行数が存在することを確認します。

パーティションの削除/マージ

ALTER PARTITION FUNCTION partfun1() MERGE RANGE ('2000')

パーティション1000-2000が削除され、1000-3000にマージされます。

--オラクルのようにALTER TABLE TABLENAME DROP PARTITION PARTITIONNAMEを実行できません。

パーティション・テーブルと対応するファイル・グループの削除

削除の順序は、パーティション・テーブルの削除、パーティション・スキームの削除、パーティション・ファンクションの削除、ファイル・グループの削除である。

パーティションテーブルを通常テーブルに、通常テーブルをパーティションテーブルに変換する例

DROP TABLE parttable1；

CREATE TABLE parttable1(

[Name] [varchar](16) NOT NULL, [Id2][int] IDENTITY(1,1)

[Id2] [int] NOT NULL

) ON partschema1(Id2)；

parttable1の値('1',1)に挿入します；

parttable1 values ('1001',1001) に挿入します；

parttable1の値('2001',2001)に挿入; parttable1の値('1',1)に挿入; parttable1の値('1001',1001)に挿入; parttable1の値('2001',2001)に挿入

parttable1の値('3001',3001)に挿入; parttable1の値('1001',1001)に挿入; parttable1の値('2001',2001)に挿入; parttable1の値('2001',2001)に挿入

parttable1の値('4001',4001)に挿入します；

1.パーティショニングされたテーブルに作成された一意制約は、パーティショニングされた列を含んでいなければなりません。

ALTER TABLE parttable1 ADD CONSTRAINT PK_prattable1_id PRIMARY KEY CLUSTERED ([ID] ASC)

エラーが報告されました 列 'Id2' はインデックス 'PK_prattable1_id' のパーティショニング列です。 一意インデックスのパーティション列は、インデックス・キーのサブセットでなければなりません。

2、パーティショニング列id2新しいクラスタ化インデックス、parttable1またはパーティショニングされたテーブル

parttable1(id2)にクラスタ化インデックスCI_prattable1_id2を作成します；

3、パーティション列id2が非クラスタ化インデックス、parttable1またはパーティション化テーブルを作成します。

parttable1上のインデックスCI_prattable1_id2を削除します；

parttable1(id2)に非クラスタ化インデックスNCI_prattable1_id2を作成します；

4、非パーティションカラムidカラムは、クラスタ化インデックス、parttable1またはパーティションテーブルを作成するために、その非パーティションカラムは、インデックス列をクラスタ化することができます。

parttable1(id)にクラスタ化インデックスCI_prattable1_idを作成します；

5.パーティショニングされた列id2は、非クラスタ化インデックスとして再構築され、パーティショニングスキームを使用しません。

parttable1(id2) WITH (DROP_EXISTING = ON) ON [PRIMARY]に非クラスタ化インデックスNCI_prattable1_id2を作成します；

6、パーティション列id2は、ON条件、parttable1またはパーティションテーブルなしでクラスタ化インデックスに再構築されます。

parttable1上のインデックスCI_prattable1_idを削除します；

parttable1上のインデックスNCI_prattable1_id2を削除します；

parttable1(id2)にクラスタ化インデックスCI_prattable1_id2を作成します；

parttable1(id2)にクラスタ化インデックスCI_prattable1_id2をWITH (DROP_EXISTING = ON)で作成します；

7、クラスター化インデックスとON条件のために再構築されたパーティション列id2ですが、パーティショニングスキームを使用せず、parttable1をパーティショニングされていないテーブルにします。

クラスター化インデックスCI_prattable1_id2をparttable1(id2)にWITH (DROP_EXISTING = ON) ON [PRIMARY]で作成します；

パーティショニングされた列id2がクラスタ化インデックスとして再構築され、パーティショニングスキームが使用され、parttable1がパーティショニングされたテーブルに変わります。

partschema1(Id2)上のparttable1(id2)にクラスタ化インデックスCI_prattable1_id2をWITH (DROP_EXISTING = ON)で作成します；

9, 上記8つのクラスタ化インデックス、parttable1またはパーティションテーブルを削除します。

partstable1上のインデックスCI_prattable1_id2を削除します；

10、新しいクラスタ化インデックスid2列をパーティショニングし、パーティショニングスキームを使用せず、parttable1はパーティショニングされていないテーブルになります。

クラスター化インデックスCI_prattable1_id2をparttable1(id2) ON [PRIMARY]に作成します；

11.上記10でクラスタ化インデックスを削除した後も、parttable1はパーティショニングされていないテーブルです。

parttable1のインデックスCI_prattable1_id2を削除します；

12、非クラスタ化インデックスのために新たにid2列をパーティショニングしますが、パーティショニングスキームの使用は、まだ非パーティション化テーブルです。

partschema1(Id2)上のparttable1(id2)に非クラスタ化インデックスNCI_prattable1_id2を作成します；

パーティショニングされたテーブルが通常のテーブルに変換され、パーティショニングされたフィールドがプライマリ・キーの場合、プライマリ・キー制約が削除され、元のプライマリ・キー・フィールドがクラスタ・インデックスに再構築されるか、プライマリ・キーに再構築されますが、どちらもパーティショニング・スキームとは関連付けられません。

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (>)

CREATE CLUSTERED INDEX PK_NAME ON Table_name（カラム） WITH（ON [PRIMARY].

または

ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY CLUSTERED(column) WITH (ON [PRIMARY]；

通常のテーブルをパーティショニングされたテーブルに変換するには、元の主キーを保持し、主キー制約を削除し、主キーを作成するがクラスタ化インデックスとして設定せず、新しいクラスタ化インデックスを作成し、このクラスタ化インデックスでパーティショニング・スキームを使用します。

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME WITH (>)

ALTER TABLE Table_name ADD CONSTRAINT PK_NAME PRIMARY KEY NONCLUSTERED(column) WITH (ON [PRIMARY].

--プライマリ・キーを作成するが、クラスタ化インデックスには設定しない。

CREATE CLUSTERED INDEX index_name ON Table_name(column) ON パーティショニング・スキーム（パーティショニング・フィールド）。

--新しい集約インデックスを作成し、その集約インデックスにパーティショニング・スキームを設定する。

パーティショニングされたテーブル、例えばテーブルcrm.EmailLogの総行数とサイズを問い合わせます。

exec sp_spaceused 'crm.EmailLog'；

パーティショニングされたテーブルの情報、例えばcrm.EmailLogテーブルの各パーティションに何行あるかを問い合わせます。

テーブルcrm.EmailLog, select convert(varchar(50), ps.name

) as partition_scheme、

p.partition_number、

ファイルグループとしてconvert(varchar(10), ds2.name

) as filegroup, convert(varchar(19))

convert(varchar(19), isnull(v.value, ''), 120) as range_boundary、

str(p.rows, 9) as rows

from sys.indexes i

join sys.partition_schemes ps on i.data_space_id = ps.data_space_id

join sys.destination_data_spaces dds

on ps.data_space_id = dds.partition_scheme_id

join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id

join sys.partitions p on dds.destination_id = p.partition_number

on dds.destination_id = p.partition_number and p.object_id = i.object_id and p.index_id = i.index_id

join sys.partition_functions pf on ps.function_id = pf.function_id

LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id

そしてv.boundary_id = p.partition_number - pf.boundary_value_on_rightです。

WHERE i.object_id = object_id('crm.EmailLog')

かつ i.index_id in (0, 1)

order by p.partition_number

パーティション関数をクエリする

select * from sys.partition_functions

パーティション・スキーマを見る

select * from sys.partition_schemes