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分区测试Calculateparty1.ndf',SIZE = 5MB,FILEGROWTH = 5MB)    
TO FILEGROUP [party1000000]


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

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


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

ALTER DATABASE Calculate    
ADD FILE     
(NAME = N'[party5000000]',FILENAME = N'D:sqlserverTest分区测试Calculateparty5.ndf',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,[cal]  AS ([val]-(1)) PERSISTED
)


;

declare @rowCount int
set @rowCount=1
while @rowCount<4000001
begin
	insert into SomeCalculate([name],@rowCount
	set @rowCount=@rowCount+1
end


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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!