由于中途浏览器挂掉，不想重写，贴代码算了
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 --查看分区方案