19

I have a table with an identity column that is also a primary key. Currently, it has 50 million rows, with the highest value of the identity column sitting at 148,921,803. The table has a lot of DELETEs and INSERTS performed on it, hence the high value.

We want to change the data type from INT to BIGINT to prepare for the addition of more rows. Note that, there are no references to the PK column.

What is the best way to do this, with minimal downtime? I have two options.

  1. Drop the PK and alter the column; or
  2. The copy-drop-rename method, as described here:
Improve this question
0

4 Answers 4

Reset to default
18

As there is a primary key defined on identity column you wont be able to directly alter this column.

Both the approaches that you have mentioned in your question can be used and downtime depends on how your server is performing and number of rows reside in that table.

  1. Drop the PK and alter the column; or

First drop the PK

/****** Object: DROP Index [PK_DatabaseLog_DatabaseLogID]******/ ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_TableName_ID] GO

Alter Column

ALTER TABLE [dbo].[TableName] ALTER COLUMN [dbo.ID] BIGINT

Add Primary key 

/****** Object: ADD Index [PK_DatabaseLog_DatabaseLogID]******/ ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName_ID] PRIMARY KEY CLUSTERED ( [ID] ASC )

This approach usually does not take much time. In my environment it takes mare seconds on big tables which have more than 5 million rows.

  1. The copy-drop-rename method, as described

You can use this approach as well. However, for this approach you need more downtime than Approach one as you have to sync the tables.

Improve this answer
14

Aaron Bertrand has a 4-part series on this topic, starting with:

Minimizing impact of widening an IDENTITY column – part 1

If you absolutely need to move to bigint, must minimize downtime, and have plenty of time for planning, the approach he documents in part 4 is:

At a very high level, the approach is to create a set of shadow tables, where all the inserts are directed to a new copy of the table (with the larger data type), and the existence of the two sets of tables is as transparent as possible to the application and its users.

In more detail, Aaron says:

  1. Create shadow copies of the tables, with the right data types.
  2. Alter the stored procedures (or ad hoc code) to use bigint for parameters. (This may require modification beyond the parameter list, such as local variables, temp tables, etc., but this is not the case here.)
  3. Rename the old tables, and create views with those names that union the old and new tables.
    • Those views will have instead of triggers to properly direct DML operations to the appropriate table(s), so that data can still be modified during the migration.
    • This also requires SCHEMABINDING to be dropped from any indexed views, existing views to have unions between new and old tables, and procedures relying on SCOPE_IDENTITY() to be modified.
  4. Migrate the old data to the new tables in chunks.
  5. Clean up, consisting of:
    • Dropping the temporary views (which will drop the INSTEAD OF triggers).
    • Renaming the new tables back to the original names.
    • Fixing the stored procedures to revert to SCOPE_IDENTITY().
    • Dropping the old, now-empty tables.
    • Putting SCHEMABINDING back on indexed views and re-creating clustered indexes.
Improve this answer
0

Another option is to set the identity seed to -1 (negative one). This will then start creating rows with a negative Id.

Note that I am NOT saying this is a GOOD idea (in fact, it is a bad idea). But in a pinch this will work until a proper solution can be implemented.

Improve this answer
4
  • Why do you say using negative values in an id field is a bad idea? Do you expect to use them for something? I do not understand. Commented Nov 24, 2022 at 14:25
  • That is a good question, and I really shouldn't use the term bad here on SO. But my thoughts are that it is just not typical. Any time I have seen them used like this is to indicate that there is an underlying problem that needs to be resolved. I just don't want anyone to think I am recommending the approach. Commented Nov 24, 2022 at 14:31
  • In building a data warehouse I routinely use negative starting IDs as surrogate keys for some of the fact tables due to the churn caused by poor system design of the source systems, currently dealing with three. This forces me to delete large blocks of data every time I need to load new records. Commented Nov 25, 2022 at 12:53
  • + 1 for "fact table". That was a new one for me. Commented Nov 26, 2022 at 13:29
0

If you are able to schedule downtime for your application, the following method worked for our mysql conversion. Because we had many tables with FK constraints, we felt like altering tables, removing FKs, adding back FK constraints would have been more error prone. This method felt very straightforward, and because we were able to schedule downtime, and the export file for us was manageable (<10GB), the process was accomplished in less than 30 minutes in our production environment.

  • Export the database to dump.sql
  • Perform a search replace in the dump file for all ids (luckily for us the ids regex was quite simple using sed.
  • sed -i 's/``\(\w*Id\w*\)`` int/``\1`` bigint/g' dump.sql to replace patterns like TeamId or AppUserIdOwner
  • Recreate the database using the modified dump.sql
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.