This month’s T-SQL Tuesday, “T-SQL Tuesday #010: What are your thoughts on Database Indexes?” is hosted by Michael J Swart (Blog/Twitter) aka the Database Whisperer. The topic is: Indexes.
Riddle me this:
Which method is faster?
- Partitioning an entire table, that is currently not partitioned, based on rebuilding a clustered index
Or
- Moving data from a temporary table to an empty table that is partitioned based on the clustered index
We are currently testing both situations as we can’t logically figuring the fastest way based on theory. There have been lots of debates and lots of theories on which is going to hold up.
Why?
You may ask why would one care about the faster option, well, here’s the situation we are currently in:
One of our logging tables in our production environment has an ID column that is an int. This ID column is currently at about the 1,900,000,000 range – that alone makes me say YIKES! An int “only” goes up to 2,147,483,647, so we are approaching this mark fast. The solution that we are going with is to convert that column to a bigint data type. A bigint’s max value goes up to 9,223,372,036,854,775,807 – now that ought to do it! 
Our Options:
Here’s our current options with the basic steps to change the data type:
Option 1
- Drop all the indexes on the table
- Drop the partition schema
- Drop the partition function
- Alter table to the new data type
- Create the partition function
- Create the partition schema
- Recreate the indexes on new functions which will partition the table
- Test
Option 2
- Copy data to new table
- Drop existing table
- Drop the partition schema
- Drop the partition function
- Create the partition function
- Create the partition schema
- Create table and indexes
- Copy data back to original table
- Test
We’ve started a pool on what’s going to be the fastest. It’s all coming down to the indexing – leave a comment and let me know your educated guess and why. I’ll definitely be blogging more about this one!