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 test1goselect * from orders where $partition.pf_orderID(OrderID) = 3

use test1goalter 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 FG414 GO15 16 ***************/17 18 alter partition scheme ps_orderID next used FG419 alter partition function pf_orderID() split range(22)

1 use test12 go3 4 alter partition function pf_orderID() merge range (10)

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