SQL Server Table Partitioning

1 Create Databasetest;2  Usetest;3 4 --create filegroups, classify files and manage them5 Alter DatabaseTestAddfilegroup Before2013;6 Alter DatabaseTestAddfilegroup T2013;7 Alter DatabaseTestAddfilegroup T2014;8 Alter DatabaseTestAddfilegroup After2014;9 Ten --Create a file and add it to the corresponding filegroup One Alter DatabaseTestAdd file(Name=N'Before2013', filename='E:\SQLSERVER\BEFORE20131.NDF', size=5mb,maxsize=100mb,filegrowth=5MB) tofilegroup Before2013; A Alter DatabaseTestAdd file(Name=N'T2013', filename='E:\SQLSERVER\T20131.NDF', size=5mb,maxsize=100mb,filegrowth=5MB) tofilegroup T2013; - Alter DatabaseTestAdd file(Name=N'T2014', filename='E:\SQLSERVER\T20141.NDF', size=5mb,maxsize=100mb,filegrowth=5MB) tofilegroup T2014; - Alter DatabaseTestAdd file(Name=N'After2014', filename='E:\SQLSERVER\AFTER20141.NDF', size=5mb,maxsize=100mb,filegrowth=5MB) tofilegroup After2014; the  - --write the partition function, with time as the partition basis - CreatePartitionfunctionRangetime (datetime) asRange Left  for Values('2012-12-31','2013-12-31','2014-12-31'); -  + --Write a partitioning scheme that corresponds to filegroup one by one - CreatePartition Scheme Rangescheme_createtime asPartition Rangetime to(before2013,t2013,t2014,after2014); +  A --Create a partitioned table, and the primary key is finally defined at Create TableShop ( -Idvarchar( -) not NULL , -Namevarchar( -), -Createtimedatetime -) onRangescheme_createtime (createtime); -  in --inserting test Data - Insert  intoShopValues(NEWID(),'test1','2011-04-05'); to Insert  intoShopValues(NEWID(),'test2','2013-04-15'); + Insert  intoShopValues(NEWID(),'test2','2013-04-15'); - Insert  intoShopValues(NEWID(),'test3','2014-04-25'); the Insert  intoShopValues(NEWID(),'test3','2014-04-25'); * Insert  intoShopValues(NEWID(),'test3','2014-04-25'); $ Insert  intoShopValues(NEWID(),'test4','2015-04-05');Panax Notoginseng  - Select *  fromShop ; the  + --count the amount of data per partition A Select$partition. Rangetime (Createtime) as  Number,COUNT(*) asRcount fromShopGroup  by$partition. Rangetime (createtime) the --Number Rcount + --1 1 - --2 2 $ --3 3 $ --4 1 -  - --determine which partition a time is in the Select$partition.[Rangetime]('2014-12-2') as 'Partitioning' - --PartitioningWuyi --3 the  - --finding data for 3 partitions Wu Select *  fromShopwhere$partition. Rangetime (Createtime)=3 - --ID Name Createtime About --26a7dbba-5a3d-43bc-81a9-68311eb724c3 test3 2014-04-25 00:00:00.000 $ --E5D53AB5-DE33-451E-82B9-A5B4268AE5CB test3 2014-04-25 00:00:00.000 - --51cabe36-febc-41f0-b9aa-a2cf72853220 test3 2014-04-25 00:00:00.000

SQL Server Table Partitioning