数据表分区, 普通表转分区表

继续使用上一节的数据库.

--填充一个事实表, 230W条记录

if exists(select 1 from sys.objects where name='fact_SaleCar' and type='U')     
drop table fact_SaleCar 
create table fact_SaleCar(     
SaleCarID nvarchar(20) not null primary key,     Salename nvarchar(50),     CheckOutDate datetime,     Attribute1 nvarchar(50),     
Attribute2 nvarchar(50),     Attribute3 nvarchar(50),     Attribute4 nvarchar(50),     Attribute5 nvarchar(50),     
Attribute6 nvarchar(50),     Attribute7 nvarchar(50),     Attribute8 nvarchar(50),     Attribute9 nvarchar(50),     
Attribute10 nvarchar(50),     Attribute11 nvarchar(50),     Attribute12 nvarchar(50) 
) 
go   

--插入测试数据 
begin 
	begin tran   
		declare @num int, @temp nvarchar(50); 
		set @num=1; 
--2010年导入80W 
		while @num<=800000 
		begin     
			set @temp=rtrim(year(dateadd(year,-2,getdate())))+RTRIM(@num);     
			insert into dbo.fact_SaleCar     
			select @temp,             '商店'+@temp,             dateadd(year,-2,getdate()),             
			'1'+@temp,'2'+@temp,'3'+@temp,'4'+@temp,'5'+@temp,'6'+@temp,'7'+@temp,             
			'8'+@temp,'9'+@temp,'10'+@temp,'11'+@temp,'12'+@temp         
			set @num=@num+1     
			if @@ERROR<>0     
			begin             
				rollback tran             
				return;     
			end   
		end   

		set @num=1; 
		--2011年导入50W 
		while @num<=500000 
		begin     
			set @temp=rtrim(year(dateadd(year,-1,getdate())))+RTRIM(@num);     
			insert into dbo.fact_SaleCar     
					select @temp,             
					'商店'+@temp,             
					dateadd(year,-1,getdate()),             
					'1'+@temp,'2'+@temp,'3'+@temp,'4'+@temp,'5'+@temp,'6'+@temp,'7'+@temp,             
					'8'+@temp,'9'+@temp,'10'+@temp,'11'+@temp,'12'+@temp         
					set @num=@num+1     
			if @@ERROR<>0     
			begin             
				rollback tran             
				return;    
			end   
		end   

		set @num=1; 
		--2011年导入100W 
		while @num<=1000000 
		begin     
			set @temp=rtrim(year(dateadd(year,0,getdate())))+RTRIM(@num);         
			insert into dbo.fact_SaleCar     
						select @temp,             
						'商店'+@temp,             
						dateadd(year,0,getdate()),             
						'1'+@temp,'2'+@temp,'3'+@temp,'4'+@temp,'5'+@temp,'6'+@temp,'7'+@temp,             
						'8'+@temp,'9'+@temp,'10'+@temp,'11'+@temp,'12'+@temp         
			set @num=@num+1     
			if @@ERROR<>0     
			begin             
				rollback tran             
				return;     
			end   
		end;   
commit tran 
end;

 

然后新建分区函数和分区方案

--create partition function
--PT_Sales_Test230W_Fun_fact_SaleCar_Date(datetime) as 
--range left for values('2011-1-1','2012-1-1')

--create partition scheme 
--PT_Sales_Test230W_Scheme_fact_SaleCar_Date as partition PT_Sales_Test230W_Fun_fact_SaleCar_Date
--to(PT_Sales_Test230W_p1,PT_Sales_Test230W_p2,PT_Sales_Test230W_p3)

 

删除已有表的主键和聚集索引.

alter table fact_SaleCar drop constraint PK_Fact_SaleCar

 

新建不带聚集索引的主键

alter table Fact_SaleCar add constraint PK_Fact_SaleCar primary key nonclustered(

SaleCarID Asc

) on [Primary]

go

 

再新建聚集索引, 不过这个时候聚集索引要指向分区方案. 这样就可以把表已有的数据分散到各个文件上.

create clustered index CT_Fact_SaleCar on Fact_SaleCar(checkOutDate)

on PT_Schme_Fact_SaleCar_CheckOutDate(CheckOutDate)