3

I have a large table that is clustered index on a bigint. We would like to change it to just an int to reduce the space and improve the performance. However, dropping the clustered index and recreate the index is extremely slow.

Is there a way to speed this process, or by design this is the only to change the data type?

[Update] Just want to elaborate a bit about my question, the column that I like to update is a date dimension key. It is currently a bigint (8 bytes), and I would like to convert it to int (4 bytes). This should reduce the size of the database, and it should theoretically improve the database performance in general.

  • How do you know it will improve performance? I doubt that you can really measure the difference. – a_horse_with_no_name May 16 '13 at 21:26
  • My first question would be does your data have values that are beyond an int. If so then don't even try the change it won't work. – Kenneth Fisher May 16 '13 at 21:59
  • @Kenneth, the column is a date dimension key and we are pretty sure we don't need an bigint to store 365 days/year worth of data. I probably won't be alive before we ran out of just int data type. :) – dsum May 16 '13 at 22:17
  • 1
    One key per day? You could store 90+ years in a smallint. Or you could use an actual date (how novel!) and use 3 bytes plus have validation and everything else that comes with the right data type. – Aaron Bertrand May 16 '13 at 22:43
  • 1
    Smallint is <= 32767. Would take longer than your lifetime to use those up. – Aaron Bertrand May 16 '13 at 22:56
1

To change datatype, you have to drop and recreate the Index as below (alternatively you can use SSMS, which does similar thing behind the scenes) :

  1. drop any foreign keys in other tables that references this index.
  2. drop the index
  3. change the column datatype
  4. rebuild the index
  5. Put back any foreign keys that you dropped in step 1.

Alternatively, you can wrap below in a transaction with TABLE_LOCK so that no one can insert/update/delete while the change is occurring --

  1. Create another table with Name_staging and columns with correct datatype
  2. create Indexes, FK, etc
  3. Insert the data into the Name_staging table
  4. Rename the Name_staging to Original table.

Note: Its best to perform above task in maintenance window.

|improve this answer|||||
  • The first method is pretty much what I did. I may try the 2nd method by moving data to a new table. However I have to say it is still hard to say if the 2nd method would be faster or not until I test it out. – dsum May 16 '13 at 22:14
3

If you have Enterprise Edition, are on 2008 or greater (always useful to specify version), and most or all of the values in this column are less than the upper bound of INT, you can get all the space back and probably quite a bit more by applying data compression.

My guess is you can improve I/O by at least as much, probably more depending on the nature of the rest of the data (and your workload), but without having to make the change to the column directly (and hence all of the constraints that apply to it, foreign key columns in other tables, etc).

Something that's certainly worth testing, especially since it's so much less complicated than changing the data type.

Of course, you should establish first whether I/O is even your bottleneck, and whether stripping 4 bytes from this particular column is likely to help you much at all. On its own, I'd have to guess: no.

|improve this answer|||||
  • This is for standard edition SQL Server 2008. Is there data compression a feature in Enterprise Edition that help reduce the size where Standard Edition doesn't have? We actually tested this out on a smaller database and it does improve the I/O. However, if the server has a lot of memory, then it probably won't make much different. The 4 bytes are stripped from 30+ tables. It just that one of the table is extremely large. Our client doesn't like down time so I am optimizing the upgrade scripts to reduce its down time. – dsum May 16 '13 at 22:30

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.