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