SqlServer表分區

標籤：

 1 create database test; 2 use test; 3  4 --建立檔案組，對檔案進行分類管理 5 alter database test add filegroup Before2013; 6 alter database test add filegroup T2013; 7 alter database test add filegroup T2014; 8 alter database test add filegroup After2014; 9 10 --建立檔案，並且添加到對應的檔案組11 alter database test add file (Name=N‘Before2013‘,filename=‘E:\sqlserver\Before20131.ndf‘,size=5mb,maxsize=100Mb,filegrowth=5mb) to filegroup Before2013;12 alter database test add file (Name=N‘T2013‘,filename=‘E:\sqlserver\T20131.ndf‘,size=5mb,maxsize=100Mb,filegrowth=5mb) to filegroup T2013;13 alter database test add file (Name=N‘T2014‘,filename=‘E:\sqlserver\T20141.ndf‘,size=5mb,maxsize=100Mb,filegrowth=5mb) to filegroup T2014;14 alter database test add file (Name=N‘After2014‘,filename=‘E:\sqlserver\After20141.ndf‘,size=5mb,maxsize=100Mb,filegrowth=5mb) to filegroup After2014;15 16 --編寫分區函數，以時間為分區依據17 create partition function RangeTime(datetime) as range left for values(‘2012-12-31‘,‘2013-12-31‘,‘2014-12-31‘);18 19 --編寫資料分割配置，與檔案組一一對應20 create partition scheme RangeScheme_createTime as partition RangeTime to (Before2013,T2013,T2014,After2014);21 22 --建立分區表,主鍵最後再定義23 create table shop(24  id varchar(50) not null ,25  name varchar(50),26  createTime datetime27 )on RangeScheme_createTime(createTime);28 29 --插入測試資料30 insert into shop values(NEWID(),‘test1‘,‘2011-04-05‘);31 insert into shop values(NEWID(),‘test2‘,‘2013-04-15‘);32 insert into shop values(NEWID(),‘test2‘,‘2013-04-15‘);33 insert into shop values(NEWID(),‘test3‘,‘2014-04-25‘);34 insert into shop values(NEWID(),‘test3‘,‘2014-04-25‘);35 insert into shop values(NEWID(),‘test3‘,‘2014-04-25‘);36 insert into shop values(NEWID(),‘test4‘,‘2015-04-05‘);37 38 select * from shop;39 40 --統計每個分區的資料量41 select $partition.RangeTime(createTime) as number,COUNT(*) as rcount from shop group by $partition.RangeTime(createTime)42 --number    rcount43 --1            144 --2            245 --3            346 --4            147 48 --判斷某個時間在哪個分區49 select $partition.[RangeTime](‘2014-12-2‘) as ‘分區‘50 --分區51 --352 53 --尋找3分區的資料54 select * from shop where $partition.Rangetime(createTime)=355 --id                                    name    createTime56 --26A7DBBA-5A3D-43BC-81A9-68311EB724C3    test3    2014-04-25 00:00:00.00057 --E5D53AB5-DE33-451E-82B9-A5B4268AE5CB    test3    2014-04-25 00:00:00.00058 --51CABE36-FEBC-41F0-B9AA-A2CF72853220    test3    2014-04-25 00:00:00.000

 

SqlServer表分區