August 6, 2018 at 2:34 pm
I am using the partition switch method on Table1 with aligned indexes with a identical staging table in the middle.
Tested successfully in DEV environment.
I am confused by the results as I was expecting having only the data and index of Table1 switched to the archive filegroup, but it is not the case.
I can see that both Table1 and Table2 are now in archiving filegroup for the month I am archiving. I can see this by the row count in sys.partitions.
The script switches the data of Table1 only (ALTER TABLE Table1_staging SWITCH PARTITION $PARTITION.PFD01(201501) TO Table1 PARTITION $PARTITION.PFD01(201501);
Before recreating the indexes prior to the Switch above, I am splitting the PFD01 and PFI01 on the value, which was merged before. (See script attached)
Running some queries for the Table2 is returning data as normal.
Question 1a: Is this the expected behavior?
Question 2a: If so, should I rebuild the indexes on Table2?
Thanks in advance for any help!
August 6, 2018 at 8:23 pm
I don't know why what you said happened actually happened but I'd take it as a lesson learned and have two different partition schemes. I've not run into the problem before because, it's just my nature, I keep such things separate "just in case".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2018 at 6:29 am
Hi Jeff,
Actually, the first thing I said to myself when I initially saw the partition structure (environment I inherited) is to why have they decided to use the same partition scheme and same partition function.
I will definitely put that in my lessons learned book, yes!
Thanks for your comment.
PS. The more I think about it, the behavior is more ok than what I thought yesterday. What a good night sleep can do!
August 7, 2018 at 7:06 am
Thanks for the feedback on my comment.
Shifting gears a bit, I've found that partitioned tables are pretty annoying compared to partitioned views. I can do everything I can do (provided that I don't need thousand of partitions) with partitioned views, instead, and they have some additional advantages like not needing to have all indexes aligned to do "Switch"-like functionality and it MUCH easier to copy, say, only the last 3 months to a lesser environment (Dev, QA, UAT, whatever). You also don't need to mess around with the likes of partition schemes or partition functions.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2018 at 10:44 am
Something I will definitely look at.
Thanks again.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy