SQL2005資料分割範例

/*

分割範例-建立 TranLog 資料表 

*/

CREATE TABLE [dbo].[TranLog](

[TID] [bigint] IDENTITY(1,1) NOT NULL,

[TCreate] [datetime] NULL,

[TStatus] [nvarchar](50) NULL,

 CONSTRAINT [PK_TranLog] PRIMARY KEY CLUSTERED 

(

[TID] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

/*

  TranLog 資料表新增預設資料，2007 年度 60 筆, 2008 年度 60 筆 , 2009 年度 60 筆

*/

declare @datetime datetime

set @datetime=getdate()

set @datetime=DateAdd(yy,-2,@datetime)

declare @items int

set @items = 60

while(@items>0)

begin

set @datetime=DateAdd(d,1,@datetime)

insert into TranLog (TCreate,TStatus) values (@datetime,'OK')

set @items=@items-1

end

set @datetime=DateAdd(yy,1,@datetime)

set @items = 60

while(@items>0)

begin

set @datetime=DateAdd(d,1,@datetime)

insert into TranLog (TCreate,TStatus) values (@datetime,'OK')

set @items=@items-1

end

set @datetime=DateAdd(yy,1,@datetime)

set @items = 60

while(@items>0)

begin

set @datetime=DateAdd(d,1,@datetime)

insert into TranLog (TCreate,TStatus) values (@datetime,'OK')

set @items=@items-1

end

select * from TranLog

/*

   Demo 

*/

--Partition Function的名稱，及要分割條件

CREATE PARTITION FUNCTION pf_TranLog_Date (datetime)   

AS RANGE RIGHT 

FOR VALUES (convert(datetime,'2008 01 01'), convert(datetime,'2009 01 01'))  

CREATE PARTITION SCHEME ps_TranLog_Date        --建立Partition scheme 

AS PARTITION pf_TranLog_Date                   --依所建立的Partition function設定分割點 

TO (TranLog_FG_2007, TranLog_FG_2008, TranLog_FG_2009)   --將Partition scheme指定給三個filegroup 

GO 

-- Create partitione table     --建立Partition Table 

CREATE TABLE TranLog_Partitioned

( 

   TID int IDENTITY(1,1) NOT NULL, 

   TCreate datetime NOT NULL,    

   TStatus nvarchar(50)  NULL 

) 

ON ps_TranLog_Date(TCreate) 

GO 

-- 將原本 TranLog 的資料匯入到  TranLog_Partitioned 分割資料表

insert into TranLog_Partitioned ( TCreate,TStatus )

select TCreate , TStatus from TranLog

select * from TranLog

select * from TranLog_Partitioned

-- 建立  對照組 資料表 (不建任何索引 不切割)

CREATE TABLE TranLog_NoAnyThing

( 

   TID int IDENTITY(1,1) NOT NULL, 

   TCreate datetime NOT NULL,    

   TStatus nvarchar(50)  NULL 

) 

--將 TranLog 資料匯入到 對照資料表 TranLog_NoAnyThing

insert into TranLog_NoAnyThing ( TCreate,TStatus )

select TCreate , TStatus from TranLog

/*  使用【顯示估計執行計畫】分析以下資料 */

-- 查詢條件:  不設定

select * from TranLog 

select * from TranLog_Partitioned  

select * from TranLog_NoAnyThing 

--查詢條件: 日期 大於 20090401

select * from TranLog  where TCreate  < convert(datetime,'2008 01 01')

select * from TranLog_Partitioned  where TCreate < convert(datetime,'2008 01 01')

select * from TranLog_NoAnyThing  where TCreate < convert(datetime,'2008 01 01')

--查詢條件: By ID

select * from TranLog  where TID  =1  

select * from TranLog_Partitioned  where TID  =1 

select * from TranLog_NoAnyThing  where TID  =1 

-- 查詢條件:  By ID  + 日期小於 20080101

select * from TranLog  where TID  =1 and TCreate  < convert(datetime,'2008 01 01')

select * from TranLog_Partitioned  where TID  =1 and TCreate  < convert(datetime,'2008 01 01')

select * from TranLog_NoAnyThing  where TID  =1 and TCreate  < convert(datetime,'2008 01 01')

/*

 讓每個 Table 各增加 9 萬筆，提升資料量後再重新分析一次

 */

declare @iCount int

set @iCount=30000

declare @tDate datetime

set @tDate = convert(datetime,'2007 04 01')

while (@iCount>0)

begin

insert into TranLog (TCreate,TStatus) values (@tDate,'OK')

insert into TranLog_NoAnyThing (TCreate,TStatus) values (@tDate,'OK')

insert into TranLog_Partitioned (TCreate,TStatus) values (@tDate,'OK')

end

set @iCount=30000

set @tDate = convert(datetime,'2008 04 01')

while (@iCount>0)

begin

insert into TranLog (TCreate,TStatus) values (@tDate,'OK')

insert into TranLog_NoAnyThing (TCreate,TStatus) values (@tDate,'OK')

insert into TranLog_Partitioned (TCreate,TStatus) values (@tDate,'OK')

end

set @iCount=30000

set @tDate = convert(datetime,'2009 04 01')

while (@iCount>0)

begin

insert into TranLog (TCreate,TStatus) values (@tDate,'OK')

insert into TranLog_NoAnyThing (TCreate,TStatus) values (@tDate,'OK')

insert into TranLog_Partitioned (TCreate,TStatus) values (@tDate,'OK')

end

[以google 文件瀏覽 ]