SQL分区

sqlserver 分区

分类： SQLServer 体系结构 2012-11-11 02:37 132人阅读 评论(1) 收藏 举报

SQL SERVER 创建表分区步骤

1. 创建分区函数

2. 创建分区架构

3. 创建表分区

创建如下数据库文件组的路径

[sql] view plain copy print ?

1. -- 1 .创建数据库文件组分区   
2.   
3. IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'PartDB')  
4. DROP DATABASE PartDB  
5. GO  
6.   
7. CREATE DATABASE [PartDB]  
8. ON PRIMARY  
9. (  
10. NAME= 'PartDB_PFG',  
11. FILENAME= 'I:\sqlserver\Partition\Primary\PartDB_PFG.mdf',  
12. SIZE=5MB,  
13. MAXSIZE=50MB,  
14. FILEGROWTH=1MB  
15. ),  
16. FILEGROUP [PartDB_FG01]  
17. (  
18. NAME= 'PartDB_FG01',  
19. FILENAME= 'I:\sqlserver\Partition\filegroup01\PartDB_FG01.mdf',  
20. SIZE=5MB,  
21. MAXSIZE=100MB,  
22. FILEGROWTH=5MB  
23. ),  
24. FILEGROUP [PartDB_FG02]  
25. (  
26. NAME= 'PartDB_FG02',  
27. FILENAME= 'I:\sqlserver\Partition\filegroup02\PartDB_FG02.mdf',  
28. SIZE=5MB,  
29. MAXSIZE=100MB,  
30. FILEGROWTH=5MB  
31. ),  
32. FILEGROUP [PartDB_FG03]  
33. (  
34. NAME= 'PartDB_FG03',  
35. FILENAME= 'I:\sqlserver\Partition\filegroup03\PartDB_FG03.mdf',  
36. SIZE=5MB,  
37. MAXSIZE=100MB,  
38. FILEGROWTH=5MB  
39. ),  
40. FILEGROUP [PartDB_FG04]  
41. (  
42. NAME= 'PartDB_FG04',  
43. FILENAME= 'I:\sqlserver\Partition\filegroup04\PartDB_FG04.mdf',  
44. SIZE=5MB,  
45. MAXSIZE=100MB,  
46. FILEGROWTH=5MB  
47. ),  
48. FILEGROUP [PartDB_FG05]  
49. (  
50. NAME= 'PartDB_FG05',  
51. FILENAME= 'I:\sqlserver\Partition\filegroup05\PartDB_FG05.mdf',  
52. SIZE=5MB,  
53. MAXSIZE=100MB,  
54. FILEGROWTH=5MB  
55. )  
56. LOG ON  
57. (  
58. NAME= 'PartDB_LOG',  
59. FILENAME= 'I:\sqlserver\Partition\logfile\PartDB_LOG.ldf',  
60. SIZE=1MB,  
61. MAXSIZE=50MB,  
62. FILEGROWTH=1MB  
63. )  
64.   
65.   
66. USE PartDB  
67. GO  
68.   
69. -- 2. 创建范围分区函数   
70.   
71. CREATE PARTITION FUNCTION pf_tab_ProductKey(int)  
72. AS RANGE LEFT  
73. FOR VALUES (300,400,500,600);  
74.   
75.   
76.   
77. -- 3. 创建分区架构   
78.   
79. CREATE PARTITION SCHEME schema_pf_tab_ProductKey  
80. AS PARTITION pf_tab_ProductKey  
81. TO(PartDB_FG01,PartDB_FG02,PartDB_FG03,PartDB_FG04,PartDB_FG05)  
82.   
83.   
84. -- 4. 创建表   
85.   
86. CREATE TABLE [dbo].[partTable](  
87.     [ProductKey] [int] NULL,  
88.     [CustomerKey] [int] NULL,  
89.     [OrderDateKey] [int] NULL,  
90.     [UnitPrice] [money] NULL,  
91.     [UnitPriceDiscountPct] [float] NULL,  
92.     [dizhi] [varchar](50) NULL  
93. ) ON schema_pf_tab_ProductKey(ProductKey) --指定分区方案,以ProductKey为分区列   
94.   
95. INSERT INTO partTable SELECT * from Sales_DW.dbo.tab --我从另一个数据库表取数据,60398条数据   
96.   
97.   
98.   
99.   
100. -- 5. 查看使用情况    
101. SELECT * FROM sys.partition_functions  
102. SELECT * FROM sys.partition_schemes  
103. SELECT * FROM sys.partition_range_values  
104. SELECT * FROM sys.partition_parameters  
105. SELECT * FROM sys.partitions  
106.   
107. -- 6. 新增分区方案。先指定下一个文件组，再添加划分范围   
108. -- 分割时分区锁定，数据迁移重组，消耗大量IO   
109. ALTER PARTITION SCHEME schema_pf_tab_ProductKey NEXT USED 'PartDB_FG05'  
110.   
111. ALTER PARTITION FUNCTION pf_tab_ProductKey()  
112. SPLIT RANGE (700)  
113.   
114.   
115. -- 7. 合并分区(相当删除)   
116. --如果分区函数定义的是left，左分区合并到右分区；right，右分区合并到左分区   
117. ALTER PARTITION FUNCTION pf_tab_ProductKey()  
118. MERGE RANGE (700)  

-- 1 .创建数据库文件组分区

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'PartDB')
DROP DATABASE PartDB
GO

CREATE DATABASE [PartDB]
ON PRIMARY
(
NAME= 'PartDB_PFG',
FILENAME= 'I:\sqlserver\Partition\Primary\PartDB_PFG.mdf',
SIZE=5MB,
MAXSIZE=50MB,
FILEGROWTH=1MB
),
FILEGROUP [PartDB_FG01]
(
NAME= 'PartDB_FG01',
FILENAME= 'I:\sqlserver\Partition\filegroup01\PartDB_FG01.mdf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
),
FILEGROUP [PartDB_FG02]
(
NAME= 'PartDB_FG02',
FILENAME= 'I:\sqlserver\Partition\filegroup02\PartDB_FG02.mdf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
),
FILEGROUP [PartDB_FG03]
(
NAME= 'PartDB_FG03',
FILENAME= 'I:\sqlserver\Partition\filegroup03\PartDB_FG03.mdf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
),
FILEGROUP [PartDB_FG04]
(
NAME= 'PartDB_FG04',
FILENAME= 'I:\sqlserver\Partition\filegroup04\PartDB_FG04.mdf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
),
FILEGROUP [PartDB_FG05]
(
NAME= 'PartDB_FG05',
FILENAME= 'I:\sqlserver\Partition\filegroup05\PartDB_FG05.mdf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
)
LOG ON
(
NAME= 'PartDB_LOG',
FILENAME= 'I:\sqlserver\Partition\logfile\PartDB_LOG.ldf',
SIZE=1MB,
MAXSIZE=50MB,
FILEGROWTH=1MB
)


USE PartDB
GO

-- 2. 创建范围分区函数

CREATE PARTITION FUNCTION pf_tab_ProductKey(int)
AS RANGE LEFT
FOR VALUES (300,400,500,600);



-- 3. 创建分区架构

CREATE PARTITION SCHEME schema_pf_tab_ProductKey
AS PARTITION pf_tab_ProductKey
TO(PartDB_FG01,PartDB_FG02,PartDB_FG03,PartDB_FG04,PartDB_FG05)


-- 4. 创建表

CREATE TABLE [dbo].[partTable](
	[ProductKey] [int] NULL,
	[CustomerKey] [int] NULL,
	[OrderDateKey] [int] NULL,
	[UnitPrice] [money] NULL,
	[UnitPriceDiscountPct] [float] NULL,
	[dizhi] [varchar](50) NULL
) ON schema_pf_tab_ProductKey(ProductKey) --指定分区方案,以ProductKey为分区列

INSERT INTO partTable SELECT * from Sales_DW.dbo.tab --我从另一个数据库表取数据,60398条数据




-- 5. 查看使用情况 
SELECT * FROM sys.partition_functions
SELECT * FROM sys.partition_schemes
SELECT * FROM sys.partition_range_values
SELECT * FROM sys.partition_parameters
SELECT * FROM sys.partitions

-- 6. 新增分区方案。先指定下一个文件组，再添加划分范围
-- 分割时分区锁定，数据迁移重组，消耗大量IO
ALTER PARTITION SCHEME schema_pf_tab_ProductKey NEXT USED 'PartDB_FG05'

ALTER PARTITION FUNCTION pf_tab_ProductKey()
SPLIT RANGE (700)


-- 7. 合并分区(相当删除)
--如果分区函数定义的是left，左分区合并到右分区；right，右分区合并到左分区
ALTER PARTITION FUNCTION pf_tab_ProductKey()
MERGE RANGE (700)

[sql] view plain copy print ?

1. /*  
2.   
3. 【创建分区函数】  
4. CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )  
5. AS RANGE [ LEFT | RIGHT ]   
6. FOR VALUES ( [ boundary_value [ ,...n ] ] )   
7. [ ; ]  
8.   
9. partition_function_name:  
10.     是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。  
11.       
12. input_parameter_type:  
13.     是用于分区的列的数据类型。当用作分区列时，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR 用户定义数据类型外，所有数据类型均有效。  
14.       
15.     实际列（也称为分区列）是在 CREATE TABLE 或 CREATE INDEX 语句中指定的  
16.   
17. boundary_value:  
18.     为使用 partition_function_name 的已分区表或索引的每个分区指定边界值。  
19.     如果 boundary_value 为空，则分区函数使用 partition_function_name 将整个表或索引映射到单个分区。  
20.     只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的一个分区列。  
21.   
22.     指定 boundary_value 提供的值的数目，不能超过 999  
23.       
24. LEFT | RIGHT:     
25.     指定当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧）。  
26.     如果未指定，则默认值为 LEFT  
27.   
28.   
29.   
30. 【创建个分区架构】  
31. CREATE PARTITION SCHEME partition_scheme_name  
32. AS PARTITION partition_function_name  
33. [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )  
34. [ ; ]  
35.   
36. partition_scheme_name  
37.     分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。  
38.   
39. partition_function_name  
40.     使用分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。  
41.     partition_function_name 必须已经存在于数据库中。  
42.   
43. ALL  
44.     指定所有分区都映射到在 file_group_name 中提供的文件组，或映射到主文件组（如果指定了 [PRIMARY]。  
45.     如果指定了 ALL，则只能指定一个 file_group_name。  
46.   
47. file_group_name | [ PRIMARY ] [ ,...n]  
48.     指定用来持有由 partition_function_name 指定的分区的文件组的名称。  
49.     file_group_name 必须已经存在于数据库中。  
50.   
51. */  
52. 原作者的博客名：kk185800961