--分区函数: [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