Changing INT to BigInt

user_109035 0
Viewed 92099

I have a warehouse table with 16 tons of data in it. I have a few Integer columns in it. We have to cast these into BIGINT for every query we write, because the SUM is too large to fit in an INT.

We now have a new datamart under development. So we thought, why not change all these columns into BIGINT and we have less to worry for the new set of queries.

Since the data is already loaded, I figured I would use Management Studio and change the data type. But I first get a warning:

Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

Then I get an error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How do I get around this?

sql-server-2005 sql
7 Answers

If you are on SQL Server 2016 then we can enable compression on table and indexes(if any) and then we can run this as Metadata operation. In case if your table is compressed then you can take full advantage of it.

1)if it is compressed table then look for any other non clustered indexes. 2)You can compress them either using ROW or page.It is not mandatory to have same compression to that of base table. 3)once all the non clustered indexes are compressed then we are ready to go with Alter command.

I have done this in my company and posted the article in my blog. https://www.chiranjeevivamsydba.com/2020/07/how-to-make-datatype-change-to-run-as.html

user_136755710
Related