CODEWITHSUNDEEP
sqlsql-server-2005
Raj More
Raj More

Reputation: 48048

Changing INT to BigInt

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?

Upvotes: 40

Views: 118176

Answers (7)

Vamsy chiranjeevi
Vamsy chiranjeevi

Reputation: 21

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

Upvotes: 1

Contango
Contango

Reputation: 80378

This technique worked really nicely for me.

I executed:

use [Mytable]
ALTER TABLE [dbo].[USER] ALTER COLUMN USER_ID bigint NOT NULL

This resulted in this error because there was a constraint on the key:

Msg 5074, Level 16, State 1, Line 2
The object 'PK_USER_USER_ID' is dependent on column 'USER_ID'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN USER_ID failed because one or more objects access this column.

Not to be deterred, in SQL Server Management Studio I right clicked on the constraint PK_USER_USER_ID, then selected "Script key as >> Drop and Create To >> New Query Editor Window":

enter image description here

This generated this script:

USE [Database]
GO
/****** Object:  Index [PK_USER_USER_ID]    Script Date: 18/03/2014 13:05:38 ******/
ALTER TABLE [dbo].[USER] DROP CONSTRAINT [PK_USER_USER_ID]
GO

/****** Object:  Index [PK_USER_USER_ID]    Script Date: 18/03/2014 13:05:38 ******/
ALTER TABLE [dbo].[USER] ADD  CONSTRAINT [PK_USER_USER_ID] PRIMARY KEY CLUSTERED 
(
[USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,        ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I then executed the first half of this script, to drop the constraint:

ALTER TABLE [dbo].[USER] DROP CONSTRAINT [PK_USER_USER_ID]
GO

Now that the constraint was gone, the original change worked nicely:

use [Mytable]
ALTER TABLE [dbo].[USER] ALTER COLUMN USER_ID bigint NOT NULL

I then executed the second half of the script, to add the constraint back in:

ALTER TABLE [dbo].[USER] ADD  CONSTRAINT [PK_USER_USER_ID] PRIMARY KEY CLUSTERED 
(
[USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,        ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Upvotes: 17

MartW
MartW

Reputation: 12538

If one or more of those columns have no constraints against them (like a foreign key, index, default, rule, etc), you should be able to change each one quickly by doing

ALTER TABLE monster ALTER COLUMN MyIntCol1 bigint

Management Studio's change SQL is rarely the most efficient and tends to favour temporary tables for anything modifying an existing column.

Upvotes: 67

Hafthor
Hafthor

Reputation: 16916

If the source data never goes over INT limit, just make a VIEW that upcasts that column to BIGINT and query against that.

Upvotes: 2

Chris Coneybeer
Chris Coneybeer

Reputation: 91

I think the main error you maybe running into is that the GUI is what is timing out. When you apply a big change using the Modify selection from SSMS it will timeout. If you take the same command by generating the change script in SSMS and then run as a straight SQL query it will run until completed.

Upvotes: 7

KM.
KM.

Reputation: 103697

to expand on OrbMan's answer:

  • add the new columns at the bottom of the column list (this will speed it up a lot)
  • you can do your updates in batches of 10,000 rows or so if necessary
  • make sure you are in single user mode, or the application if "OFF" so no one else changes data in that table

Also, to see all the work that Management studio does when you change a table, click on the toolbar icon that looks like scroll with a diskette on it. This will show the actual SQL commands used to alter your table.

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

Not sure if this will help, but try this:

1 - create a new bigint column in the table
2 - update that new column with the values from the int column
3 - delete the int column
4 - rename the bigint column

Upvotes: 34

Related Questions