パーティション分割されたテーブルでデータの移動や削除をどのように行うのかを確認するために、以降は、SQL Server 2005に付属するAdventureWorksサンプルデータベース注 で、受注の履歴データを格納するTransactionHistoryテーブルと履歴データのアーカイブ用のTransactionHistoryArchiveテーブルにパーティションを設定する手順を紹介します。TransactionHistoryテーブルとTransactionHistoryArchiveテーブルは、同じテーブル構造を持ちProductionスキーマに定義されています。
注） AdventureWorksサンプルデータベース
SQL Server 2005に付属するデータベースで、既定ではインストールされない。事前にインストールしておく場合は、SQL Server 2005のセットアップ時に、詳細設定から追加する。
図2 TransactionHistoryテーブルとTransactionHistoryArchiveテーブルのテーブル構造1. TransactionHistoryテーブルを分割するためのパーティション関数を定義
リスト4のTransactionHistoryPFパーティション関数では、月ごとのデータを格納するためdatetime型の列に格納された値に基づきTransactionHistoryテーブル、およびインデックスを12のパーティションに分割します。作成されたパーティション関数を確認する場合、「SQL Server Management Studio」のオブジェクトエクスプローラから、[Databases] - [AdventureWorks] - [Storage] - [Partition Functions] を展開し、表示されるオブジェクトを確認します。
USE AdventureWorks
GO
CREATE PARTITION FUNCTION TransactionHistoryPF (datetime)
AS RANGE RIGHT FOR VALUES (
'10/01/2003', '11/01/2003', '12/01/2003',
'1/01/2004', '2/01/2004', '3/01/2004', '4/01/2004',
'5/01/2004', '6/01/2004', '7/01/2004', '8/01/2004');
GO |
| リスト4 パーティション関数の定義 |
2. TransactionHistoryテーブル用のパーティションスキームを定義し、パーティション関数で指定されたパーティションを配置するファイルグループを指定
リスト5のパーティションスキームは、TransactionHistoryPFパーティション関数で指定されたパーティションを同じPRIMARYファイルグループに配置することを指定しています。作成されたパーティションスキームを確認する場合、「SQL Server Management Studio」のオブジェクトエクスプローラから、[Databases] - [AdventureWorks] - [Storage] - [Partition Schemes] を展開し、表示されるオブジェクトを確認します。
CREATE PARTITION SCHEME TransactionHistoryPS
AS PARTITION TransactionHistoryPF
TO ([PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY]);
GO |
| リスト5 パーティションスキームの定義 |
3. 定義されたパーティションスキームを使って、テーブルを再作成
リスト6のスクリプトでは、TransactionHistoryテーブルでTransactionHistoryPSパーティションスキームを使用するためテーブルの再作成を行っています。
IF OBJECT_ID(
'[Production].[FK_TransactionHistory_Product_ProductID]',
'F') IS NOT NULL
ALTER TABLE [Production].[TransactionHistory]
DROP CONSTRAINT
[FK_TransactionHistory_Product_ProductID];
GO
IF OBJECT_ID('[Production].[TransactionHistory]', 'U')
IS NOT NULL
DROP TABLE [Production].[TransactionHistory];
GO
CREATE TABLE [Production].[TransactionHistory](
[TransactionID] [int] IDENTITY (1, 1) NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineNumber] [smallint] NOT NULL
CONSTRAINT
[DF_TransactionHistory_ReferenceOrderLineNumber]
DEFAULT (0),
[TransactionDate] [datetime] NOT NULL
CONSTRAINT [DF_TransactionHistory_TransactionDate]
DEFAULT (GETDATE()),
[TransactionType] [nchar](1) NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [DF_TransactionHistory_ModifiedDate]
DEFAULT (GETDATE()),
CONSTRAINT [CK_TransactionHistory_TransactionType]
CHECK (
[TransactionType] IN ('W', 'S', 'P', 'w', 's', 'p'))
) ON TransactionHistoryPS (TransactionDate);
GO
ALTER TABLE [Production].[TransactionHistory] ADD
CONSTRAINT [FK_TransactionHistory_Product_ProductID]
FOREIGN KEY
(
[ProductID]
) REFERENCES [Production].[Product](
[ProductID]
);
GO |
| リスト6 パーティションスキームを使ったテーブルの再作成 |
4. TransactionHistoryArchiveテーブルを分割するためのパーティション関数を定義
リスト7のTransactionHistoryArchivePFパーティション関数では、月ごとのデータを格納するためdatetime型の列に格納された値に基づきTransactionHistoryArchiveテーブル、およびインデックスを2つのパーティションに分割します。
CREATE PARTITION FUNCTION
TransactionHistoryArchivePF (datetime)
AS RANGE RIGHT FOR VALUES ('9/01/2003');
GO |
| リスト7 テーブル分割のためのパーティション関数定義 |
5.TransactionHistoryArchiveテーブル用のパーティションスキームを定義し、パーティション関数で指定されたパーティションを配置するファイルグループを指定
リスト8のパーティションスキームは、TransactionHistoryArchivePFパーティション関数で指定されたパーティションを同じPRIMARYファイルグループに配置することを指定しています。
CREATE PARTITION SCHEME TransactionHistoryArchivePS
AS PARTITION TransactionHistoryArchivePF
TO ([PRIMARY], [PRIMARY]);
GO |
| リスト8 パーティションスキームの定義 |
6.定義されたパーティションスキームを使って、テーブルを再作成
リスト9のスクリプトでは、TransactionHistoryArchiveテーブルでTransactionHistoryArchivePSパーティションスキームを使用するためテーブルの再作成を行っています。
IF OBJECT_ID('[Production].[TransactionHistoryArchive]', 'U')
IS NOT NULL
DROP TABLE [Production].[TransactionHistoryArchive];
GO
CREATE TABLE [Production].[TransactionHistoryArchive](
[TransactionID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineNumber] [smallint] NOT NULL
CONSTRAINT
[DF_TransactionHistoryArchive_ReferenceOrderLineNumber]
DEFAULT (0),
[TransactionDate] [datetime] NOT NULL
CONSTRAINT [DF_TransactionHistoryArchive_TransactionDate]
DEFAULT (GETDATE()),
[TransactionType] [nchar](1) NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [DF_TransactionHistoryArchive_ModifiedDate]
DEFAULT (GETDATE()),
CONSTRAINT [CK_TransactionHistoryArchive_TransactionType]
CHECK ([TransactionType] IN
('W', 'S', 'P', 'w', 's', 'p'))
) ON TransactionHistoryArchivePS (TransactionDate);
GO |
| リスト9 パーティションスキームを使ったテーブル再作成 |
7. TransactionHistoryテーブルとTransactionHistoryArchiveテーブルへのデータロード
リスト10のスクリプトで、TransactionHistoryテーブルとTransactionHistoryArchiveテーブルにデータをロードします。TransactionHistoryArchiveテーブルには8万9253件、TransactionHistoryテーブルには11万3443件の受注データがロードされるはずです。TransactionHistoryArchiveテーブルには、2001年5月17日から2003年8月31日までのデータが格納されます。TransactionHistoryテーブルには、2003年9月1日から2004年9月3日までのデータが格納されます。
DECLARE
@retcode INT
,@data_path NVARCHAR(256)
EXECUTE @retcode = master.dbo.xp_instance_regread
'HKEY_LOCAL_MACHINE',
'Software\Microsoft\Microsoft SQL Server\Setup',
'SQLPath',
@param = @data_path OUTPUT
SELECT @data_path = LEFT(@data_path,
PATINDEX('%\MSSQL%', @data_path)) +
'90\Tools\Samples\1041\awdb\'
EXECUTE (N'BULK INSERT
[AdventureWorks].[Production].[TransactionHistoryArchive]
FROM ''' + @data_path + N'TransactionHistoryArchive.csv''
WITH (
CODEPAGE=''ACP'',
DATAFILETYPE = ''char'',
FIELDTERMINATOR= ''\t'',
ROWTERMINATOR = ''\n'' ,
KEEPIDENTITY,
TABLOCK
)');
EXECUTE (N'BULK
INSERT [AdventureWorks].[Production].[TransactionHistory]
FROM ''' + @data_path + N'TransactionHistory.csv''
WITH (
CODEPAGE=''ACP'',
DATAFILETYPE = ''char'',
FIELDTERMINATOR= ''\t'',
ROWTERMINATOR = ''\n'' ,
KEEPIDENTITY,
TABLOCK
)'); |
| リスト10 各テーブルにデータをロード |
次ページでは、実際にサンプルデータベースを使って、データを移動する作業を解説します。（次ページに続く）
ITmediaはアイティメディア株式会社の登録商標です。