Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 sliding window scenario on partitioned table

Author  Topic 

db042188
Starting Member

6 Posts

Posted - 2007-11-13 : 15:08:55
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'.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-23 : 05:26:42
Ok, this is just telling you that all parts of the partitioned table must reside in the same filegroup.
Go to Top of Page
   

- Advertisement -