December 21, 2009 at 10:31 am
My production environment has a table with indenity column with INT datatype; The column size is maxed out, The table size is 60GB. What is the best way to change the datatype to BIGINT to support more inserts. The table is heavly used by the users.
December 21, 2009 at 11:02 am
you'll want to use the GUI in SSMS;and change it to bigINT; the GUI will create a new table witht he proper structure, migrate the data, migrate all the foreign keys, constraints, etc, and drop teh old, then rename the new table to the proper name;
it is much easier and faster to use the GUI than to do that by hand, especially on a big busy table.
i'm fairly sure that it also changes the child tables that point to it with foreign keys, since they need to be changed to BIGINT also. one simple operation like changing the data type might affect dozens or hundreds of tables.
everyone will be locked out of the table while you are changing it, but they cannot insert right now, i would assume, so you are probably going to have to do this right away.
Lowell
December 21, 2009 at 11:27 am
In addition, I recommend you make a backup of the table as a precaution.
I would go to the extent of scripting out the table, including indexes and Key relationships.
Then a simple select * into tablebackup12212009 from table would suffice for the backup.
Doing this provides a bit of protection should something happen during the conversion. This has saved me from catastrophy due to a transaction deadlock on the table rebuild that killed the temp table and the data in it. If not for the backup, I would have been hosed.
Other than that, do as Lowell recommends.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 11:45 am
Thank you guys for your helps.
December 21, 2009 at 11:56 am
NP
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 12:14 pm
CirquedeSQLeil (12/21/2009)
Then a simple select * into tablebackup12212009 from table would suffice for the backup.
I seem to recall that you can get blocking when creating a table this way if the source table is big because system locks are held until the insert is complete. You could create the empty table tablebackup12212009 first , then insert the data.
December 21, 2009 at 12:25 pm
Good Point Homebrew - It is a better practice in most cases to create the table first.
If all is broken currently because nothing can be inserted into the table, then it may be fine in this scenario to use the select into.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 12:32 pm
This is quick and dirty way to create an empty table ... Not one to file under best practice though 😎
select * into tablebackup12212009 from table where 2=1
December 21, 2009 at 12:33 pm
The following procedure is OK?
1. Create Table [backup12212009] with BIGINT Indentity
2. using SSIS export data from old table to [backup12212009]
3. Delete old table
4. Rename backup12212009
Can do as above?
December 21, 2009 at 12:37 pm
Joe-420121 (12/21/2009)
The following procedure is OK?1. Create Table [backup12212009] with BIGINT Indentity
2. using SSIS export data from old table to [backup12212009]
3. Delete old table
4. Rename backup12212009
Can do as above?
Why not just use INSERT INTO? Just be sure to enable INDENTITY insert either way, and if using T-SQL, to turn it off when done.
December 21, 2009 at 1:01 pm
Joe-420121 (12/21/2009)
The following procedure is OK?1. Create Table [backup12212009] with BIGINT Indentity
2. using SSIS export data from old table to [backup12212009]
3. Delete old table
4. Rename backup12212009
Can do as above?
if there are any dependent foreign keys on the table, or if the table has any constraints or defaults then no, that is NOT the way to do it.
doing a full backup and additionally backing up the table as Jason suggested, and then using the SSMS GUI is the way to do it.
Lowell
December 21, 2009 at 1:03 pm
Lynn Pettis (12/21/2009)
Joe-420121 (12/21/2009)
The following procedure is OK?1. Create Table [backup12212009] with BIGINT Indentity
2. using SSIS export data from old table to [backup12212009]
3. Delete old table
4. Rename backup12212009
Can do as above?
Why not just use INSERT INTO? Just be sure to enable INDENTITY insert either way, and if using T-SQL, to turn it off when done.
As Lynn said, I would use the INSERT INTO. It will be faster in most cases.
Also, it is very important that when you create the table that it is created to mirror the original table with the exception of the BIGINT.
I, would also not Drop the old table for several weeks. I like to keep it around as a failsafe. It is much faster to recover from that table than from backup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 1:05 pm
If you're going to use INSERT INTO, you'll need to name all the columns. One easy way to get the list of columns (if the table has many) is "select name + ',' from syscolumns where object_name(id) = 'mytablename' order by colorder (Replace mytablename with the name of your table) This will give you the columns and the commas in-between.
Carlos.
Carlos
December 21, 2009 at 1:09 pm
Any reason why you're not using alter table alter column?
as in alter table mytable alter column my column bigint?
Carlos.
Carlos
December 21, 2009 at 1:15 pm
Carlos Pereyra (12/21/2009)
Any reason why you're not using alter table alter column?as in alter table mytable alter column my column bigint?
Carlos.
The best answer is - as a safeguard - but the OP will have to respond.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply