笔记104 创建分区表的步骤
1 --创建分区表的步骤
2 --创建分区函数
3 USE partionTest
4 GO
5 CREATE PARTITION FUNCTION PartionByInt(INT )
6 AS RANGE LEFT FOR VALUES (100,200,300)
7 GO
8 --3个分区点就一定是4段分区
9 --left：负无穷-100，101-200，201-300，301-正无穷
10 --right：100-199，200-299，300-399，400-499
11
12 --创建分区方案
13 USE partionTest
14 GO
15 CREATE PARTITION SCHEME PartionByIntScheme
16 AS PARTITION PartionByInt
17 TO (FileGroup001,FileGroup002,FileGroup003,FileGroup004);
18 --这里刚好是4个段，每个段一个文件组，如果加多一个文件组不知道SQL会怎麽分配这些分区到文件组
19
20
21 --创建表
22 USE partionTest
23 GO
24 CREATE TABLE testPartionTable
25 (id INT NOT NULL ,
26 ItemNo CHAR( 20),
27 ItemName CHAR( 40))
28 ON PartionByIntScheme(ID);
29 --id为分区列
30
31
32
33 --插入数据
34 USE partionTest
35 GO
36
37 DECLARE @count INT
38 SET @count =-25
39 WHILE @count <=100
40 BEGIN
41 INSERT INTO dbo.testPartionTable
42 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>0 and <100'
43 SET @count =@count +1
44 END
45
46 SET @count =101
47 WHILE @count <=200
48 BEGIN
49 INSERT INTO dbo.testPartionTable
50 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>100 and <200'
51 SET @count =@count +1
52 END
53
54 SET @count =201
55 WHILE @count <=300
56 BEGIN
57 INSERT INTO dbo.testPartionTable
58 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>200 and <300'
59 SET @count =@count +1
60 END
61
62 SET @count =301
63 WHILE @count <=400
64 BEGIN
65 INSERT INTO dbo.testPartionTable
66 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>300 and <400'
67 SET @count =@count +1
68 END
69
70 SET @count =401
71 WHILE @count <=500
72 BEGIN
73 INSERT INTO dbo.testPartionTable
74 SELECT @count ,'ITEM'+CONVERT( VARCHAR(6),@count),'>400 and <500'
75 SET @count =@count +1
76 END
77
78
79 SELECT * FROM dbo.testPartionTable
80 GO
81
82
83 --查看第三分区中所含记录
84 --$PARTITION系统函数用于为指定的分区函数返回分区号例如：$PARTITION.PartionByInt(ID)=3用于返回给定ID所处的分区号的分区数据
85 USE partionTest
86 GO
87 SELECT * FROM dbo.testPartionTable
88 WHERE $PARTITION.PartionByInt(ID)=3
89 GO
90
91
92 ----------------------------邹建大侠的分区表方案 计算列作为分区列-----------------------------------------
93
94 USE pratice
95 GO
96 -- 分区函数和架构
97 CREATE PARTITION FUNCTION PF_Year(DATETIME)
98 AS
99 RANGE LEFT FOR VALUES (
100 N'2010', N'2011', N'2012'
101 );
102 --负无穷-2010，2011-2011，2012-2012，2012-正无穷
103
104 CREATE PARTITION SCHEME PS_Year
105 AS
106 PARTITION PF_Year TO (
107 [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]
108 )
109 GO
110
111 -- 创建表和计算列，并将计算列用做分区列
112 CREATE TABLE dbo.tb_test(
113 date datetime,
114 date_year as YEAR(date) PERSISTED -- PERSISTED 选项用于持久化存储
115 )ON PS_Year(date_year);
116 --YEAR(date) 里的date是第一列 date
117
118 CREATE CLUSTERED INDEX IXC_date_year ON dbo.tb_test(date_year)
119 --加聚集索引不是唯一索引，加索引只是使查询加快速度，可以不加
120
121
122 GO
