0

I'm looking for some advice on the best way to change the data type on a column from an INT to a BIGINT on a table with 220 million rows, as it has reached the upper limit of the INT data type.

The database is on SQL Azure DB, and there is a clustered index with a primary key constraint, a foreign key constraint, and a non-clustered index. The table the foreign key is from also has 3 non-clustered indexes which include the key.

What is the fastest/most efficient way to achieve this? Ideally in such a way as to minimize the length of time the table will be inaccessible.

I am planning to test by dropping the constraints and indexes, making the change, and recreating them, as well as moving the data into a new table and creating the appropriate constraints and indexes there, but I wanted to find out if anyone could offer some advice on this first. I've seen a few ideas online such as moving data in batches, but I'm not sure what will work best. The table itself is only about 12GB, but I tested the whole operation on a local copy of the database and it generated 150GB of log.

  • I wrote a series about this, it was specifically targeted at IDENTITY columns but most of the concepts are valid without it. There are breadcrumbs at the bottom to walk you through the 4 parts: sqlperformance.com/2016/01/sql-indexes/… – Aaron Bertrand Dec 21 '16 at 14:03
  • That's great, I'll have a look over them. Thanks Aaron. – PaulM Dec 21 '16 at 14:15
0

You didn't say whether this column was part of any of the FKEY or indexes. Assuming it is not, the simplest way to achieve this is to add a new bigint column and update the data there, and then prepare a script to apply the changes to other affected objects. Potentially, the changes could be done in one transaction and have near-zero downtime.

  • Sorry, that was what I meant in the second paragraph. The column is included in the primary key and also a non-clustered index, as well as in the foreign key on another table, including 3 non-clustered indexes there. – PaulM Dec 21 '16 at 11:40
0

Just for reference in case anyone else comes across this. What I ended up doing is covered in Aaron's series of articles referenced above, specifically in part 4: https://sqlperformance.com/2016/08/sql-indexes/widening-identity-column-4

I did not go the full route of creating views and triggers to join the old and new tables for controlling writes, but instead stopped writes to the table through the application, created 2 new tables and selected all of the data from the existing tables into those before adding the appropriate constraints and creating indexes, dropping the original tables and renaming the new ones, then updating the data type in stored procedures which referenced the column being altered. The entire process took just under 25 minutes on an Azure DB performance tier with 1000 DTUs.

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.