温习表分区

第一部分，为表创建分区

第二部分，扩新分区

第三部分，交换分区

USE [SPIRITDW]
GO
--1. Add filegroup
alter database [SPIRITDW] add filegroup FAP2015fg
alter database [SPIRITDW] add filegroup FAP2016fg
alter database [SPIRITDW] add filegroup FAP2017fg
--2. Add file to filegroup
alter database [SPIRITDW] add file
(name=N'FAP2015f',filename=N'T:\data\data_srv\dbagroup\mssql\log\FAP2015f.ndf',size=5Mb,filegrowth=5mb)
to filegroup FAP2015fg
alter database [SPIRITDW] add file
(name=N'FAP2016f',filename=N'T:\data\data_srv\dbagroup\mssql\log\FAP2016f.ndf',size=5Mb,filegrowth=5mb)
to filegroup FAP2016fg
alter 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 rules
CREATE PARTITION FUNCTION [FAPfnP](int) AS RANGE RIGHT FOR VALUES (N'20160101', N'20170101')
--4. Add SCHEME, each period has a fileGROUP
CREATE PARTITION SCHEME [FAPschP] AS PARTITION [FAPfnP] TO ([FAP2015fg], [FAP2016fg], [FAP2017fg])
--5. Add a table to partition by creating CLUSTERED INDEX
CREATE 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 filegroup
alter database [SPIRITDW] add filegroup FAP2018fg
--2. Add file to filegroup
alter 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 filegroup
CREATE CLUSTERED INDEX [ClusteredIndex_on_FAPschA] ON [dw].[FactActivePositionA]
(
[DateKey]
)ON [FAP2015fg]
--3. Switch partition to archive
ALTER TABLE [dw].[FactActivePositionP] SWITCH PARTITION 1 TO [dw].[FactActivePositionA]
--4. If you want to switch back, you need a constraint
alter table [dw].[FactActivePositionA]
add constraint CK_FactActivePositionA_C1 check ([DateKey] < 20160101);
--5. switch back with the constraint
ALTER TABLE [dw].[FactActivePositionA] SWITCH TO [dw].[FactActivePositionP] PARTITION 1