创建分区数据库

-- 创建分区数据库

USE master

GO

CREATE DATABASE partitiontest

ON PRIMARY

( NAME = db_dat,

FILENAME = 'c:/test/db.mdf',

SIZE = 3MB),

FILEGROUP FG1

( NAME = FG1_dat,

FILENAME = 'c:/test/FG1.ndf',

SIZE = 2MB),

FILEGROUP FG2

( NAME = FG2_dat,

FILENAME = 'c:/test/FG2.ndf',

SIZE = 2MB),

FILEGROUP FG3

( NAME = FG3_dat,

FILENAME = 'c:/test/FG3.ndf',

SIZE = 2MB),

FILEGROUP FG4

( NAME = FG4_dat,

FILENAME = 'c:/test/FG4.ndf',

SIZE = 2MB)

LOG ON

( NAME = db_log,

FILENAME = 'c:/test/log.ndf',

SIZE = 2MB,

FILEGROWTH = 10% );

GO

USE partitiontest

GO

 

-- 创建分区函数

CREATE PARTITION FUNCTION partfunc (int) AS

RANGE LEFT FOR VALUES (1000, 2000)

GO

 

 

-- 查看分区函数的边界值

SELECT * FROM sys.partition_range_values;

 

-- 创建分区方案

CREATE PARTITION SCHEME partscheme AS

PARTITION partfunc TO

( [FG1], [FG2], [FG3]);

 

-- 查看分区方案

SELECT * FROM sys.partition_schemes;

 

-- 创建分区表

CREATE TABLE dbo.t1 (

id INT

, v CHAR(1000) DEFAULT 'aaaa', 

CONSTRAINT ci_t1_id PRIMARY KEY CLUSTERED (id))

ON partscheme(id);

 

-- 查看分区表

SELECT * FROM sys.partitions

WHERE object_id = OBJECT_ID('dbo.t1');

 

truncate table dbo.t1

 

-- 添加测试数据

SET NOCOUNT ON

DECLARE @i INT

SET @i=10

WHILE @i<=3000

BEGIN

INSERT dbo.t1 (id) SELECT @i

SET @i=@i+10

END

GO

 

-- 查看数据

SELECT * from dbo.t1

 

 

SELECT * FROM sys.partitions

WHERE object_id = OBJECT_ID('dbo.t1');

 

-- 检查特定值是否与分区数一致

SELECT $partition.partfunc (1001) as [PartitionNum];

 

-- 限定分区查询

SELECT * FROM dbo.t1 

WHERE $partition.partfunc(id)=3

 

SELECT $partition.partfunc(id) AS [PartitionNum], count(*) [NumRows]

FROM dbo.t1 GROUP BY $partition.partfunc(id)

ORDER BY $partition.partfunc(id);

 

-- 添加修改数据

SET NOCOUNT ON

DECLARE @i INT, @max INT

SELECT @max=MAX(id) + 10 FROM dbo.t1 

SET @i= @max 

WHILE @i<= @max + 3000 - 10

BEGIN

INSERT dbo.t1 (id) SELECT @i

SET @i=@i+10

END

GO

 

SELECT $partition.partfunc(id) AS [PartitionNum], count(*) [NumRows]

FROM dbo.t1 GROUP BY $partition.partfunc(id)

ORDER BY $partition.partfunc(id);

 

-- 合并分区和

ALTER PARTITION FUNCTION partfunc()

MERGE RANGE (1000);

 

-- 修改分区方案将 FG4 文件组标记为 NEXT USED

ALTER PARTITION SCHEME partscheme 

NEXT USED [FG4];

 

-- 添加一个新分区

ALTER PARTITION FUNCTION partfunc()

SPLIT RANGE (4000);

 

SELECT * FROM sys.partitions

WHERE object_id = OBJECT_ID('dbo.t1')

 

SELECT $partition.partfunc(id) AS [PartitionNum], count(*) [NumRows]

FROM dbo.t1 GROUP BY $partition.partfunc(id)

ORDER BY $partition.partfunc(id);

 

-- 创建临时表

CREATE TABLE dbo.t2 (

id INT 

, v CHAR(1000) DEFAULT 'bbbb', 

CONSTRAINT ci_t2_id PRIMARY KEY CLUSTERED (id)

, CONSTRAINT check_t2 CHECK (ID>6000)

) ON [FG3]

GO

 

 

SET NOCOUNT ON

DECLARE @i INT, @max INT

SELECT @max=MAX(id) + 10 FROM dbo.t1 

SET @i= @max 

WHILE @i<= @max + 6000 - 10

BEGIN

INSERT dbo.t2 (id) SELECT @i

SET @i=@i+10

END

GO

 

-- 增加文件组 FG5

ALTER DATABASE [partitiontest]

ADD FILEGROUP [FG5]

GO

 

ALTER DATABASE [partitiontest]

ADD FILE 

( NAME = db5_dat,

FILENAME = 'c:/test/FG5.ndf',

SIZE = 2MB)

TO FILEGROUP [FG5]

GO

 

 

ALTER PARTITION SCHEME partscheme 

NEXT USED [FG5];

GO

 

ALTER PARTITION FUNCTION partfunc()

SPLIT RANGE (6000);

 

-- 查看表 dbo.t1

SELECT * FROM sys.partitions

WHERE object_id = OBJECT_ID('dbo.t1')

 

SELECT $partition.partfunc(id) AS [PartitionNum], count(*) [NumRows] 

FROM dbo.t1 GROUP BY $partition.partfunc(id)

ORDER BY $partition.partfunc(id)

GO

 

-- 查看表 dbo.t2

SELECT * FROM sys.partitions

WHERE object_id = OBJECT_ID('dbo.t2')

 

-- 查看两个表的最大值和最小值

SELECT COUNT(*), MIN(id), MAX(id) FROM dbo.t2

GO

SELECT COUNT(*), MIN(id), MAX(id) FROM dbo.t1

GO

 

ALTER TABLE dbo.t2

SWITCH TO dbo.t1 PARTITION 4

GO

 

-- 测量大数据的性能添加行数据

SET NOCOUNT ON

DECLARE @i INT, @max INT

SELECT @max=MAX(id) + 10 FROM dbo.t1 

SET @i= @max 

WHILE @i<= @max + 1000000 - 10

BEGIN

INSERT dbo.t2 (id) SELECT @i

SET @i=@i+10

END

GO

 

--select * into 花费大约秒

select getdate()

set nocount on

select * into dbo.t3 from dbo.t2

select getdate()

 

 

SELECT count(*) FROM dbo.t1 

WHERE $partition.partfunc(id)=4

 

 

delete from dbo.t1 

WHERE $partition.partfunc(id)=4

 

-- 瞬间完成果然效率非常之高哈哈！

select getdate()

ALTER TABLE dbo.t2

SWITCH TO dbo.t1 PARTITION 4

GO

select getdate()

 

 

SELECT count(*) FROM dbo.t1 

WHERE $partition.partfunc(id)=4

 

 

SELECT COUNT(*), MIN(id), MAX(id) FROM dbo.t2

GO

SELECT COUNT(*), MIN(id), MAX(id) FROM dbo.t1

GO

 

drop table dbo.t5

 

CREATE TABLE dbo.t5 (

id INT

, v CHAR(1000) DEFAULT 'aaaa', 

CONSTRAINT ci_t5_id PRIMARY KEY CLUSTERED (id))

ON [FG3]

 

 

-- 在表之间切换分区将已分区表的一个分区中的所有数据重新分配给现有的未分区的表

 

select getdate()

ALTER TABLE dbo.t1 SWITCH PARTITION 4 TO dbo.t5 ;

GO

select getdate()

 

select count(*) from dbo.t5