sqlserver数据表（计算列）表分区

     
--分区函数： [myPartFunc]

CREATE TABLE [Calculate](
	[id] [int] identity(1,1) ,
	[name] [varchar](20) NULL,
	[val] [int] NULL,
	[cal]  AS ([val]-(1)) PERSISTED
)

/*
添加主键
*/

/*
	添加测试数据
*/
declare @rowCount int
set @rowCount=1
while @rowCount<4000001
begin
	insert into [Calculate]([name],[val])
		select 'Name_'+CAST(@rowCount as varchar(10)) as [name],@rowCount
	set @rowCount=@rowCount+1
end




/*
1 建立分区函数
*/
create partition function myCalPartFunc(int)  
as range left   
for values(  
      1000000,
      2000000,
      3000000,
      4000000,
      5000000
)     
;
/*
2.1建立分区文件组
*/

ALTER DATABASE Calculate ADD FILEGROUP [party1000000]    
ALTER DATABASE Calculate ADD FILEGROUP [party2000000] 
ALTER DATABASE Calculate ADD FILEGROUP [party3000000] 
ALTER DATABASE Calculate ADD FILEGROUP [party4000000] 
ALTER DATABASE Calculate ADD FILEGROUP [party5000000] 
;
/*
2.2建立文件组地址
*/


ALTER DATABASE Calculate    
ADD FILE     
(NAME = N'[party1000000]',FILENAME = N'D:\sqlserverTest\分区测试\Calculate\party1.ndf',SIZE = 5MB,FILEGROWTH = 5MB)    
TO FILEGROUP [party1000000]


ALTER DATABASE Calculate    
ADD FILE     
(NAME = N'[party2000000]',FILENAME = N'D:\sqlserverTest\分区测试\Calculate\party2.ndf',SIZE = 5MB,FILEGROWTH = 5MB)    
TO FILEGROUP [party2000000]

ALTER DATABASE Calculate    
ADD FILE     
(NAME = N'[party3000000]',FILENAME = N'D:\sqlserverTest\分区测试\Calculate\party3.ndf',SIZE = 5MB,FILEGROWTH = 5MB)    
TO FILEGROUP [party3000000]


ALTER DATABASE Calculate    
ADD FILE     
(NAME = N'[party4000000]',FILENAME = N'D:\sqlserverTest\分区测试\Calculate\party4.ndf',SIZE = 5MB,FILEGROWTH = 5MB)    
TO FILEGROUP [party4000000]

ALTER DATABASE Calculate    
ADD FILE     
(NAME = N'[party5000000]',FILENAME = N'D:\sqlserverTest\分区测试\Calculate\party5.ndf',SIZE = 5MB,FILEGROWTH = 5MB)    
TO FILEGROUP [party5000000]
;
/*
3.0 建立分区架构
*/

CREATE PARTITION SCHEME [myPartFunc]    
AS    
PARTITION myCalPartFunc TO    
(   
[party1000000],  
[party2000000],
[party3000000],
[party4000000],
[party5000000],
[PRIMARY] 
)    
GO  

;



/*
4.0 对已存在的表添加表分区函数 进行数据分区
*/




select  COUNT(0) id from dbo.Calculate
--直接添加表分区 不用设置聚集主键
--创建一个新的聚集索引，在该聚集索引中使用分区方案 
/*
	4.1 适用于 无主键
*/
--CREATE CLUSTERED INDEX index_CalCu ON Calculate([cal]) ON myPartFunc([cal])
--

/*
   4.2适用于 有主键 同时主键为 聚集索引的表
*/
/*

ALTER TABLE [Calculate]
    ADD CONSTRAINT PK_Calculate
       PRIMARY KEY CLUSTERED(id)


  ALTER TABLE Calculate
    DROP CONSTRAINT PK_Calculate
     WITH(MOVE TO myPartFunc(Cal))
     
*/   
     
--分区完成以后 数据为185M



/*
新建一个表 使用相同的 分区函数
*/
create table SomeCalculate
(
	[id] [int] identity(1,1) ,
	[name] [varchar](20) NULL,
	[val] [int] NULL,
	[cal]  AS ([val]-(1)) PERSISTED
)


;

declare @rowCount int
set @rowCount=1
while @rowCount<4000001
begin
	insert into SomeCalculate([name],[val])
		select 'Name_'+CAST(@rowCount as varchar(10)) as [name],@rowCount
	set @rowCount=@rowCount+1
end


CREATE CLUSTERED INDEX index_CalCu ON SomeCalculate([cal]) ON myPartFunc([cal])
--第二次使用相同函数分区以后 340M