Sliding Window Scenario On Partitioned Table

I began with a partition function as follows:

CREATE PARTITION FUNCTION [TimeTicksRangePFN](bigint) AS RANGE RIGHT FOR VALUES (633294720000000000, 633320640000000000, 633347424000000000, 633374208000000000, 633399264000000000)

These numbers happen to correspond to the dates 11/1/7, 12/1/7, 1/1/8, 2/1/8 and 3/1/8 in ticks respectively.

I began with a partition scheme as follows:

CREATE PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION [TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001], [FG_xxx_EventArchive00002], [FG_xxx_EventArchive00003], [FG_xxx_EventArchive00004], [FG_xxx_EventArchive00005], [PRIMARY])

While running my “sliding window script� , which I hoped would 1) roll off the oldest partition of my EventArchive table and 2) add a new partition with a tick boundary that equates to 3/5/8, I get an error related to my switch out table's index, the same table's Filegroup and Primary.

After getting the error, I scripted the partition function as a create in mgt studio and got…

CREATE PARTITION FUNCTION [TimeTicksRangePFN](bigint) AS RANGE RIGHT FOR VALUES (633320640000000000, 633347424000000000, 633374208000000000, 633399264000000000, 633402720000000000)

...which looks like what I had intended cuz the last boundary is the tick representation of 3/5/8 and the oldest has rolled off

scripting the scheme produced...

CREATE PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION [TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001], [FG_xxx_EventArchive00003], [FG_xxx_EventArchive00004], [FG_xxx_EventArchive00005], [PRIMARY], [FG_xxx_EventArchive00001])

which looks nothing like what I intended, I thought I’d end up with …00002,…00003,…00004,…00005,…00001,PRIMARY

the script steps that seem most relevant start at the 5th step as follows...

5) creates table [dbo].Switch on the switch out filegroup with columns, PK and indexes matching exactly those of [dbo].EventArchive

6) switches partition 1 of [dbo].EventArchive to [dbo].Switch

7) ALTER PARTITION FUNCTION TimeTicksRangePFN() MERGE RANGE (633294720000000000) --this was the oldest date corresponding to 11/1/7

8) truncates [dbo].Switch

9) drops all indexes on [dbo].Switch except a clustered index (IX_TimeTicks), leaves PK constraint alone

10) ships the new data whose values range from 3/1/8 to less than 3/5/8 to [dbo].Switch and deletes them from their source

11) recreates all non clustered indexes on [dbo].Switch

12)ALTER TABLE [dbo].[Switch] WITH CHECK ADD CONSTRAINT RangeCK CHECK ([TimeTicks] < the number of ticks represented by 3/5/8)

13)ALTER PARTITION SCHEME TimeTicksRangePScheme NEXT USED [FG_xxx_EventArchive00001] --fg isnt really hardcoded

14)ALTER PARTITION FUNCTION TimeTicksRangePFN() SPLIT RANGE (the number of ticks represented by 3/5/8)

15)ALTER TABLE [dbo].[Switch] SWITCH TO [dbo].[EventArchive] PARTITION 5

step 15 is the one that fails with message "ALTER TABLE SWITCH statement failed. index 'xxx.dbo.Switch.IX_TimeTicks' is in filegroup 'FG_xxx_EventArchive00001' and partition 5 of index 'xxx.dbo.EventArchive.IX_TimeTicks' is in filegroup 'PRIMARY'.