防竄貨系統分割槽表指令碼例項

上校發表於2017-01-04
指令碼

分割槽ImportCode

/*準備工作：加入時間欄位*/
alter table ImportCode add CreateTime datetime default getdate()

update  ImportCode set createtime = b.ScanDate from ImportCode a inner join ImportInfo b  on a.BatchId=b.Id



/*第一步：建立分割槽函式*/
Create partition function Part_func_ImportCode(datetime) as range right 
/*正式區間for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/
for values('20140101','20150101','20160101','20170101');
--CREATE PARTITION FUNCTION [Part_func_Bag](datetime) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2013-01-01T00:00:00.000', N'2014-01-01T00:00:00.000')

go

/*第二步：建立檔案組和檔案*/
alter database db_haidilao20141015 add filegroup [ImportCode_1]; 
alter database db_haidilao20141015 add filegroup [ImportCode_2];
alter database db_haidilao20141015 add filegroup [ImportCode_3];
alter database db_haidilao20141015 add filegroup [ImportCode_4];
alter database db_haidilao20141015 add filegroup [ImportCode_5];
go

alter database db_haidilao20141015 add file (name = ImportCode1_data,filename = 'D:\MSSQL\haidilao20141015\ImportCode1_data.ndf',size = 3MB) to filegroup [ImportCode_1];
alter database db_haidilao20141015 add file (name = ImportCode2_data,filename = 'D:\MSSQL\haidilao20141015\ImportCode2_data.ndf',size = 3MB) to filegroup [ImportCode_2];
alter database db_haidilao20141015 add file (name = ImportCode3_data,filename = 'D:\MSSQL\haidilao20141015\ImportCode3_data.ndf',size = 3MB) to filegroup [ImportCode_3]; 
alter database db_haidilao20141015 add file (name = ImportCode4_data,filename = 'D:\MSSQL\haidilao20141015\ImportCode4_data.ndf',size = 3MB) to filegroup [ImportCode_4]; 
alter database db_haidilao20141015 add file (name = ImportCode5_data,filename = 'D:\MSSQL\haidilao20141015\ImportCode5_data.ndf',size = 3MB) to filegroup [ImportCode_5];
go

/*第三步：建立分割槽方案並關聯到分割槽函式*/
Create partition scheme Part_func_ImportCode_scheme as partition Part_func_ImportCode to ([ImportCode_1],[ImportCode_2],[ImportCode_3],[ImportCode_4],[ImportCode_5]); 
--CREATE PARTITION SCHEME [Part_func_Bag_scheme] AS PARTITION [Part_func_Bag] TO ([Bag_1], [Bag_2], [Bag_3], [Bag_4], [PRIMARY])
go


/*第四步 重建索引（刪除聚集索引以及需要分割槽欄位的索引後重建該類索引，表被按分割槽值將分配到各檔案組。資料在這一步開始轉移。）*/ 
EXEC sp_helpindex N'ImportCode' --檢視orders中使用的索引 
--刪除主鍵(主鍵是聚集索引，分割槽表只能有一個聚集索引，索引要刪除主鍵)
alter table [db_haidilao20141015].[dbo].[ImportCode] drop constraint PK_ImportCode
go

--建立聚集索引並開始遷移資料
create clustered index idx_cl_od on ImportCode(createTime) 
on Part_func_ImportCode_scheme(createTime); 
go

 

分割槽ExportCode

/*準備工作：加入時間欄位*/
alter table ExportCode add CreateTime datetime default getdate()

update  ExportCode set createtime = b.ScanDate from ExportCode a inner join ExportInfo b  on a.BatchId=b.Id

alter table ExportCode add CreateTime datetime default getdate()

update  ExportCode set createtime = b.ScanDate from ExportCode a inner join ExportInfo b  on a.BatchId=b.Id


/*第一步：建立分割槽函式*/
Create partition function Part_func_ExportCode(datetime) as range right 
/*正式區間for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/
for values(N'2015-01-01T00:00:00.000',N'2016-01-01T00:00:00.000',N'2016-07-01T00:00:00.000',N'2017-01-01T00:00:00.000');
--CREATE PARTITION FUNCTION [Part_func_Bag](datetime) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2013-01-01T00:00:00.000', N'2014-01-01T00:00:00.000')

go

/*第二步：建立檔案組和檔案*/
alter database db_sishuang2test20170105 add filegroup [ExportCode_1]; 
alter database db_sishuang2test20170105 add filegroup [ExportCode_2];
alter database db_sishuang2test20170105 add filegroup [ExportCode_3];
alter database db_sishuang2test20170105 add filegroup [ExportCode_4];
alter database db_sishuang2test20170105 add filegroup [ExportCode_5];
go

alter database db_sishuang2test20170105 add file (name = ExportCode1_data,filename = 'D:\MSSQL\sishuang2test20170105\ExportCode1_data.ndf',size = 3MB) to filegroup [ExportCode_1];
alter database db_sishuang2test20170105 add file (name = ExportCode2_data,filename = 'D:\MSSQL\sishuang2test20170105\ExportCode2_data.ndf',size = 3MB) to filegroup [ExportCode_2];
alter database db_sishuang2test20170105 add file (name = ExportCode3_data,filename = 'D:\MSSQL\sishuang2test20170105\ExportCode3_data.ndf',size = 3MB) to filegroup [ExportCode_3]; 
alter database db_sishuang2test20170105 add file (name = ExportCode4_data,filename = 'D:\MSSQL\sishuang2test20170105\ExportCode4_data.ndf',size = 3MB) to filegroup [ExportCode_4]; 
alter database db_sishuang2test20170105 add file (name = ExportCode5_data,filename = 'D:\MSSQL\sishuang2test20170105\ExportCode5_data.ndf',size = 3MB) to filegroup [ExportCode_5];
go

/*第三步：建立分割槽方案並關聯到分割槽函式*/
Create partition scheme Part_func_ExportCode_scheme as partition Part_func_ExportCode to ([ExportCode_1],[ExportCode_2],[ExportCode_3],[ExportCode_4],[ExportCode_5]); 
--CREATE PARTITION SCHEME [Part_func_Bag_scheme] AS PARTITION [Part_func_Bag] TO ([Bag_1], [Bag_2], [Bag_3], [Bag_4], [PRIMARY])
go


/*第四步 重建索引（刪除聚集索引以及需要分割槽欄位的索引後重建該類索引，表被按分割槽值將分配到各檔案組。資料在這一步開始轉移。）*/ 
EXEC sp_helpindex N'ExportCode' --檢視orders中使用的索引 
--刪除主鍵(主鍵是聚集索引，分割槽表只能有一個聚集索引，索引要刪除主鍵)
alter table [db_sishuang2test20170105].[dbo].[ExportCode] drop constraint PK_ExportCode
go

--建立聚集索引並開始遷移資料
create clustered index idx_cl_od on ExportCode(createTime) 
on Part_func_ExportCode_scheme(createTime); 
go

分割槽CodeProduct

/*準備工作：加入時間欄位*/

alter table CodeProduct add CreateTime datetime default getdate()

update  CodeProduct set createtime = b.createtime from CodeProduct a inner join ExportCode b  on a.FlowCode=b.FlowCode


/*第一步：建立分割槽函式*/
Create partition function Part_func_CodeProduct(datetime) as range right 
/*正式區間for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/
for values(N'2014-01-01T00:00:00.000',N'2015-01-01T00:00:00.000',N'2016-01-01T00:00:00.000',N'2017-01-01T00:00:00.000');
--CREATE PARTITION FUNCTION [Part_func_Bag](datetime) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2013-01-01T00:00:00.000', N'2014-01-01T00:00:00.000')

go

/*第二步：建立檔案組和檔案*/
alter database db_sishuang2test20170104 add filegroup [CodeProduct_1]; 
alter database db_sishuang2test20170104 add filegroup [CodeProduct_2];
alter database db_sishuang2test20170104 add filegroup [CodeProduct_3];
alter database db_sishuang2test20170104 add filegroup [CodeProduct_4];
alter database db_sishuang2test20170104 add filegroup [CodeProduct_5];
go

alter database db_sishuang2test20170104 add file (name = CodeProduct1_data,filename = 'D:\MSSQL\sishuang2test20170104\CodeProduct1_data.ndf',size = 3MB) to filegroup [CodeProduct_1];
alter database db_sishuang2test20170104 add file (name = CodeProduct2_data,filename = 'D:\MSSQL\sishuang2test20170104\CodeProduct2_data.ndf',size = 3MB) to filegroup [CodeProduct_2];
alter database db_sishuang2test20170104 add file (name = CodeProduct3_data,filename = 'D:\MSSQL\sishuang2test20170104\CodeProduct3_data.ndf',size = 3MB) to filegroup [CodeProduct_3]; 
alter database db_sishuang2test20170104 add file (name = CodeProduct4_data,filename = 'D:\MSSQL\sishuang2test20170104\CodeProduct4_data.ndf',size = 3MB) to filegroup [CodeProduct_4]; 
alter database db_sishuang2test20170104 add file (name = CodeProduct5_data,filename = 'D:\MSSQL\sishuang2test20170104\CodeProduct5_data.ndf',size = 3MB) to filegroup [CodeProduct_5];
go

/*第三步：建立分割槽方案並關聯到分割槽函式*/
Create partition scheme Part_func_CodeProduct_scheme as partition Part_func_CodeProduct to ([CodeProduct_1],[CodeProduct_2],[CodeProduct_3],[CodeProduct_4],[CodeProduct_5]); 
--CREATE PARTITION SCHEME [Part_func_Bag_scheme] AS PARTITION [Part_func_Bag] TO ([Bag_1], [Bag_2], [Bag_3], [Bag_4], [PRIMARY])
go


/*第四步 重建索引（刪除聚集索引以及需要分割槽欄位的索引後重建該類索引，表被按分割槽值將分配到各檔案組。資料在這一步開始轉移。）*/ 
EXEC sp_helpindex N'CodeProduct' --檢視orders中使用的索引 
--刪除主鍵(主鍵是聚集索引，分割槽表只能有一個聚集索引，索引要刪除主鍵)
alter table [db_sishuang2test20170104].[dbo].[CodeProduct] drop constraint PK_CodeProduct
go

--建立聚集索引並開始遷移資料
create clustered index idx_cl_od on CodeProduct(createTime) 
on Part_func_CodeProduct_scheme(createTime); 
go

 

相關文章