创建分区数据库

-- 创建分区数据库
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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值