--创建分区数据库
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