温习表分区

第一部分，为表创建分区

第二部分，扩新分区

第三部分，交换分区

USE [SPIRITDW]GO--1. Add filegroupalter database [SPIRITDW] add filegroup FAP2015fgalter database [SPIRITDW] add filegroup FAP2016fgalter database [SPIRITDW] add filegroup FAP2017fg--2. Add file to filegroupalter database [SPIRITDW] add file(name=N'FAP2015f',filename=N'T:\data\data_srv\dbagroup\mssql\log\FAP2015f.ndf',size=5Mb,filegrowth=5mb)to filegroup FAP2015fgalter database [SPIRITDW] add file(name=N'FAP2016f',filename=N'T:\data\data_srv\dbagroup\mssql\log\FAP2016f.ndf',size=5Mb,filegrowth=5mb)to filegroup FAP2016fgalter database [SPIRITDW] add file(name=N'FAP2017f',filename=N'T:\data\data_srv\dbagroup\mssql\log\FAP2017f.ndf',size=5Mb,filegrowth=5mb)to filegroup FAP2017fg--3. Add function, determine the split rulesCREATE PARTITION FUNCTION [FAPfnP](int) AS RANGE RIGHT FOR VALUES (N'20160101', N'20170101')--4. Add SCHEME, each period has a fileGROUPCREATE PARTITION SCHEME [FAPschP] AS PARTITION [FAPfnP] TO ([FAP2015fg], [FAP2016fg], [FAP2017fg])--5. Add a table to partition by creating CLUSTERED INDEXCREATE CLUSTERED INDEX [ClusteredIndex_on_FAPschP] ON [dw].[FactActivePositionP]([DateKey])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [FAPschP]([DateKey])　　　--1. Add a new filegroupalter database [SPIRITDW] add filegroup FAP2018fg--2. Add file to filegroupalter database [SPIRITDW] add file(name=N'FAP2018f',filename=N'T:\data\data_srv\dbagroup\mssql\log\FAP2018f.ndf',size=5Mb,filegrowth=5mb)to filegroup FAP2018fg--3. Alter scheme by adding another fileGROUP to "NEXT USED"alter partition SCHEME [FAPschP]NEXT USED [FAP2018fg]--4. Alter function by "split"alter partition FUNCTION [FAPfnP]()split range(N'20180101')　　--1. Create a same table (must be in same db)SELECT * INTO [dw].[FactActivePositionA] FROM [dw].[FactActivePositionP] WHERE 1=2--2. Create the same index on the same filegroupCREATE CLUSTERED INDEX [ClusteredIndex_on_FAPschA] ON [dw].[FactActivePositionA]([DateKey])ON [FAP2015fg]--3. Switch partition to archiveALTER TABLE [dw].[FactActivePositionP] SWITCH PARTITION 1 TO [dw].[FactActivePositionA]--4. If you want to switch back, you need a constraintalter table [dw].[FactActivePositionA]add constraint CK_FactActivePositionA_C1 check ([DateKey] < 20160101);--5. switch back with the constraintALTER TABLE [dw].[FactActivePositionA] SWITCH TO [dw].[FactActivePositionP] PARTITION 1