SQL SERVER 分区表

浏览数：22 / 时间：2015年06月12日

由于中途浏览器挂掉，不想重写，贴代码算了

  1 USE master
  2 GO
  3 
  4 -------------------创建数据库与分区文件组--------------------------------------
  5 /******************************************************************************
  6 Create Database test1 ON Primary 
  7 (
  8    name = N‘test1‘,
  9    filename = N‘d:\test1.mdf‘,
 10    size = 3MB,
 11    Maxsize = 100MB,
 12    filegrowth = 10%
 13 ),
 14 
 15 filegroup FG1
 16 (
 17    name = N‘file1‘,
 18    filename = N‘d:\file1.ndf‘,
 19    size = 1MB,
 20    maxsize = 100MB,
 21    filegrowth = 10%
 22 ),
 23 
 24 filegroup FG2
 25 (
 26    name = N‘file2‘,
 27    filename = N‘d:\file2.ndf‘,
 28    size = 1MB,
 29    maxsize = 100MB,
 30    filegrowth = 10%
 31 ),
 32 
 33 filegroup FG3
 34 (
 35    name = N‘file3‘,
 36    filename = N‘d:\file3.ndf‘,
 37    size = 1MB,
 38    maxsize = 100MB,
 39    filegrowth = 10%
 40 )
 41 
 42 LOG ON
 43 (
 44   name = N‘test1_log‘,
 45   filename = N‘d:\test1_log.ldf‘,
 46   size = 1MB,
 47   maxsize = 30MB,
 48   filegrowth = 10%
 49 )
 50 
 51 go
 52 
 53 ******************************************************************/
 54 
 55 USE test1
 56 GO
 57 --------------------------创建分区函数-----------------------------
 58 --Create partition function pf_orderID(int)
 59 --as range left for values(10,20)
 60 
 61 --GO
 62 
 63 --------------------------创建分区方案------------------------------
 64 --Create partition scheme ps_orderID
 65 --As partition pf_orderID
 66 --To (FG1,FG2,FG3)
 67 --GO
 68 
 69 -------------------------创建分区表---------------------------------
 70 /******************************************************************
 71 USE test1
 72 GO
 73 
 74 Create Table orders
 75 (
 76   orderID int not null primary key,
 77   customerID int not null,
 78   customerNM char(20),
 79   orderdate datetime
 80 ) 
 81 
 82 ON ps_orderID(orderID)
 83 GO
 84 
 85 Create Table ordersHistory
 86 (
 87   orderID int not null primary key,
 88   customerID int not null,
 89   customerNM char(20),
 90   orderdate datetime
 91 ) 
 92 
 93 ON ps_orderID(orderID)
 94 
 95 *****************************************************************/
 96 
 97 USE test1
 98 GO
 99 Insert into dbo.orders values (1,1,‘王星‘,GETDATE())
100 Insert into dbo.orders values (2,2,‘张星‘,GETDATE())
101 Insert into dbo.orders values (3,3,‘王五‘,GETDATE())
102 Insert into dbo.orders values (4,4,‘李四‘,GETDATE())
103 Insert into dbo.orders values (5,5,‘吴青‘,GETDATE())
104 Insert into dbo.orders values (6,6,‘王思源‘,GETDATE())
105 Insert into dbo.orders values (7,7,‘张思武‘,GETDATE())
106 Insert into dbo.orders values (8,8,‘陈德娥‘,GETDATE())
107 Insert into dbo.orders values (9,9,‘赵倩‘,GETDATE())
108 Insert into dbo.orders values (10,10,‘王青‘,GETDATE())
109 Insert into dbo.orders values (11,11,‘石顺‘,GETDATE())
110 Insert into dbo.orders values (12,12,‘张峰‘,GETDATE())
111 Insert into dbo.orders values (13,13,‘文雅‘,GETDATE())
112 Insert into dbo.orders values (14,14,‘黄忠‘,GETDATE())
113 Insert into dbo.orders values (15,15,‘无碍‘,GETDATE())
114 Insert into dbo.orders values (16,16,‘黄爱生‘,GETDATE())
115 Insert into dbo.orders values (17,17,‘弑天‘,GETDATE())
116 Insert into dbo.orders values (18,18,‘流苏‘,GETDATE())
117 Insert into dbo.orders values (19,19,‘蛋黄‘,GETDATE())
118 Insert into dbo.orders values (20,20,‘萨芬‘,GETDATE())
119 Insert into dbo.orders values (21,21,‘艾丝凡‘,GETDATE())
120 Insert into dbo.orders values (22,22,‘暗示法‘,GETDATE())
121 Insert into dbo.orders values (23,24,‘色纺‘,GETDATE())
122 Insert into dbo.orders values (25,25,‘个人‘,GETDATE())

查询分区表的数据

use test1
go
select * from orders where $partition.pf_orderID(OrderID) = 3

将orders表分区1中的数据归档到ordersHistory中

use test1
go
alter table orders switch partition 1 to ordersHistory partition 1

添加分区

 1 use test1
 2 go
 3 /******************************
 4 alter database test1 add filegroup FG4
 5 
 6 alter database test1 add file(
 7  name =N‘file4‘,
 8  filename = N‘d:\file4.ndf‘,
 9  size = 3MB,
10  maxsize = 100MB,
11  filegrowth = 10%
12 )
13 TO  filegroup FG4
14 GO
15 
16 ***************/
17 
18 alter partition scheme ps_orderID next used FG4
19 alter partition function pf_orderID() split range(22)

删除分区，将2个分区合成几个即可，如将（0-10）与（10,20）将（0-10）的分区删除，只需要删除10这个临界点

1 use test1
2 go
3 
4 alter partition function pf_orderID() merge range (10)

分区函数

1 use test1
2 go
3 --------------查看分区数据---------------------
4 select * from sys.partition_functions        --查看分区函数
5 select * from sys.partition_range_values     --查看分区临界点
6 select * from sys.partition_schemes          --查看分区方案