question

Randy.Roberts78 avatar image
Randy.Roberts78 asked

Upgrading tables from int to BigInt

We are up grading from one version to another in our new application (from v1 to v2). Most of our users are on SQL Server 2012. We have larger customers now with a lot more data than we did when we started this application 8 years ago. We decided on our next version that we were going to go to bigint. Now we are there and there are eight tables I need to be upgraded to bigint. I've written some SQL code that will drop all the indexes related to the tables that are being updated along with their foreign keys. Then drop the table original table and rename the temp table to the proper table name and copy all the data. Then reapply all indexes and foreign keys. After I run this upgrade it works fine, but DB is just about twice the size it was before I started. Is this the right approach or should I be doing something different?
sql-server-2012
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Adedba avatar image Adedba commented ·
It may be possible to Alter column and change the data type from Int to Bigint after you have removed your indexes and constraints this would save some database space and log space as you will be able to ignore your copy data step leaving you just the recreating of constraints and indexes. Depending on how much free space you have in the data file and log file then there is potential for the database to grow out with operations such as these, you can truncate the log back to its normal operating size when done following a transaction log backup or if the option is available to be able to lose a point in time you could switch to bulk logged recovery whilst carrying out the process but you will lose your point in time restore potential possibly not a great option.
0 Likes 0 ·
Avi1 avatar image
Avi1 answered
I think use Adedba suggestion, it will save both time and database space. Changing int to bigint is not going to be issue
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Altering table doesn't necessarily save you any time at all. If you bulk copy all the eight tables to the new tables, using eg SSIS, whilst in simple recovery mode, you will probably get better performance than doing an ALTER TABLE ALTER COLUMN, since ALTER TABLE ALTER COLUMN is fully logged and a bulk load can be minimally logged. The size of the database will grow substantially from altering datatypes to bigint. A bigint is twice the size of a an int. If the database growing to twice the size of the original is normal or not depends on whatever other data you have in the database.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Randy.Roberts78 avatar image
Randy.Roberts78 answered
I like this one, but I would have a problem changing to simple recovery mode for customers at are on AlwaysOn or mirroring DB's.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.