Keeping transaction table and partition table in sync


I have a table with 70 columns and 10 indexes, which will be loaded with some 6-7 million records each month. As data loading has become slow lately, I'm now partitioning the table and will load into a Transaction table which will be switched into the partitioned table. We will also switch out data older than x months.

I have setup the partition function and partition scheme and will rebuild the clustered index on the partitioned table.

Everything seems to work fine, I just have to update the SSIS-package to include Split operation, creation of Check constraints and Switch operation.

Now to my question: When this is all setup, I won't maintain the system. Other developers will be involved in maintaining the system and it's likely that they will want to create new or alter existing indexes on the partitioned table. I can't be sure that they will also maintain the indexes on the Transaction table. So we might end up with inconsistency of indexes between the Transaction table and the partitioned table.

How would you attack this problem? Would you attempt to compare the indexes in partitioned table with transaction table and merge those changes? Or would you drop all indexes on the transaction table and make a create script from the partitioned table?

more ▼

asked Sep 28, 2012 at 10:59 AM in Default

avatar image

Magnus Ahlkvist
22.5k 20 43 43

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

TL/DR: Train them up and let them design the solution to do the index comparison. That should ramp up their abilities and give them a sense of (at least partially) "owning" the project.

You could go for a two-pronged attack!

First of all, I would make sure that the developers that are taking ownership really understand the design of this solution. They need to understand how the partition switching works and the intricacies of how it all interacts. This is best done with a training session and supported with documentation.

Secondly, I would supply the tooling to do the index syncing. I'm not too sure on this part, whether to supply it and let the magic happen, or to maybe assign this as a task to the team that are taking the project on. If they design and implement it, it will further help their understanding of the technologies being used.

more ▼

answered Sep 28, 2012 at 11:11 AM

avatar image

26.2k 18 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 28, 2012 at 10:59 AM

Seen: 1300 times

Last Updated: Sep 28, 2012 at 11:12 AM

Copyright 2018 Redgate Software. Privacy Policy