SQL server 分区表示例

最新推荐文章于 2018-02-07 11:12:00 发布

manbufenglin

最新推荐文章于 2018-02-07 11:12:00 发布

阅读量634

点赞数

分类专栏： SQL Server 文章标签： sql server scheme join function database insert

本文链接：https://blog.csdn.net/manbufenglin/article/details/7550489

版权

SQL Server 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

本文详细介绍了如何使用SQL Server创建包含PRIMARY分区在内的多分区数据库，并通过分区函数和分区方案实现数据高效存储与管理。包括数据库创建、分区定义、数据插入、分区查询以及关键的分区调整操作如分割与合并，最后提供了查询分区信息的SQL语句。

摘要由CSDN通过智能技术生成

--我们创建包含PRIMARY分区在内一共3分区的数据库
CREATE DATABASE [db_partition_test] 
ON  PRIMARY 
( 
    NAME = N'db_partition_test', 
    FILENAME = N'E:\SQLData\db_partition_test.mdf',
    Size=3MB,
	MaxSize=50MB,
	FileGrowth=10%
), 
FILEGROUP [FG1] 
( 
    NAME = N'db_partition_test_fg1_1', 
    FILENAME = N'E:\SQLData\db_partition_test_fg1_1.ndf',
    SIZE=2MB,
	MAXSIZE=10MB,
	FILEGROWTH=5%
), 
 FILEGROUP [FG2] 
( 
    NAME = N'db_partition_test_fg2_1', 
    FILENAME = N'E:\SQLData\db_partition_test_fg2_1.ndf',
    SIZE=2MB,
	MAXSIZE=20MB,
	FILEGROWTH=15%
)
 LOG ON 
( 
    NAME = N'db_partition_test_log', 
    FILENAME = N'E:\SQLData\db_partition_test_log.ldf',
    SIZE=1MB,
	MAXSIZE=10MB,
	FILEGROWTH=10%
)
GO

USE [db_partition_test]
GO

--创建分区函数，分区范围为  id<=10000 |  10000<id<=20000  | 20000<id<=30000 | id>30000
CREATE PARTITION FUNCTION [id_range_pf_1](int) AS RANGE LEFT FOR VALUES (10000, 20000,30000)
GO

--创建分区方案 ,分区一到PRIMARY,分区二到FG1,分区三\四到FG2
CREATE PARTITION SCHEME [id_range_ps_1] AS PARTITION [id_range_pf_1] TO ([PRIMARY],FG1,FG2,fg2);
GO

--创建分区表
CREATE TABLE dbo.lovesql
(
    ID INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL PRIMARY KEY,
    Col Uniqueidentifier DEFAULT(NEWID())
) ON id_range_ps_1(ID)
 
--插入测试数据 50001条
INSERT INTO dbo.lovesql DEFAULT VALUES
GO 50001

--查看表分区以后的分区行数
SELECT $PARTITION.id_range_pf_1(ID) [PartitionNum],COUNT(1) [PartitionRowCount]
FROM dbo.lovesql
GROUP BY $PARTITION.id_range_pf_1(ID)
ORDER BY [PartitionNum]

--------------分割分区-----------------
/*分割现有的分区需要两个步骤:
     1.首先告诉SQL SERVER新建立的分区放到哪个文件组
     2.建立新的分割点
*/
--指定分区框架后续所使用的文件组名称
ALTER PARTITION SCHEME id_range_ps_1 NEXT USED 'FG2';
--分割分区，指定分割点45000
ALTER PARTITION FUNCTION id_range_pf_1() SPLIT RANGE(45000)
/*
PartitionNum	PartitionRowCount
1	10000
2	10000
3	10000
4	15000
5	5001
*/

-----------合并分区----------------
/*这里值得注意的是，假设分区3和分区4不再一个文件组，则合并后应该存在哪个文件组呢？
换句话说，是由分区3合并到分区4还是由分区4合并到分区3？
这个需要看我们的分区函数定义的是left还是right.如果定义的是left.则由左边的分区3合并到右边的分区4.
反之，则由分区4合并到分区3*/
--指定合并分区点 45000
ALTER PARTITION FUNCTION id_range_pf_1() 
MERGE RANGE(45000)
---结果:--
/*
PartitionNum	PartitionRowCount
1	10000
2	10000
3	10000
4	20001
*/

查询分区及分区文件组、分区范围SQL：

select convert(varchar(50), ps.name) as partition_scheme,
	p.partition_number, 
	convert(varchar(10), ds2.name) as filegroup, 
	convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
	str(p.rows, 9) as rows
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object_id = object_id('lovesql')
and i.index_id in (0, 1) 
order by p.partition_number
/*--查询结果:
partition_scheme	partition_number	filegroup	range_boundary	rows
id_range_ps_1	1	PRIMARY	10000	    10000
id_range_ps_1	2	FG1	20000	    10000
id_range_ps_1	3	FG2	30000	    10000
id_range_ps_1	4	FG2		    20001
*/