I have a table with a PK based on a bigint identity and will have a clustered index on a datetime2 column. I've created a partition function based on calendar quarter and was planning on two partition schemes (one each for clustered and non-clustered indexes) to able to spread out I/O. However, if I plan to implement a sliding window to age out older data, do I need to keep all indexes in the same partition scheme or it doesn't matter as long as the partition function is the same and using the same datetime2 key column?

  • Have you tried implementing that scheme in a test environment? – Max Vernon Nov 16 '15 at 18:51
  • @MaxVernon I had mixed results in my test environment. I followed an example that I discovered here (blogs.msdn.com/b/hanspo/archive/2009/08/21/…) and I had success in moving a partition of old data into a staging table which I later dropped and then merged the range. However, when I retested using Manage Partition wizard with SSMS, it's indicating my table doesn't qualify due to an index not being storage aligned. I'm opting to believe the wizard and rebuilding my non-clustered index to use the same scheme. – MattyZDBA Nov 16 '15 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.