SQL SERVER 分区表

  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

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)

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          --查看分区方案