June 30, 2008 at 1:39 pm
In one our or Datawarehosue tables (20 million + rows) I was trying to change the type of a column from numeric(8,0) to numeric(8,4). I attempted to make the change by modifying the column schema ("Design mode" under Sql 2005 Management Studio). When I saved the changes, after about 30 seconds, the process timed out.
For my next try I used the schema of the original table to create a new empty table. Then I updated the new empty table column to numeric(8,4) and used SSIS to do a straight-across data pull.
That worked fine but I'm sure there are smarter ways of updating the schema of tables with large amounts of data. What can you suggest?
TIA,
Barkingdog
June 30, 2008 at 1:54 pm
It really depends on the type of change. You should go back into design mode in a test envionment and try to make your change in Management Studio again. Rather than saving the change, click the "Script Change" button and look at what it does.
You may be surprised to see that it creates a copy of the table with the new schema and copies all of the data into the new copy of the table. It then drops the old table and renames the new one to the original table name. This is one of the reasons Management Studio doesn't let you modify schema through the designer on replicated articles.
It gets done this way for a number of reasons (one of which is the inability to specify the ordinal position of columns in an ALTER TABLE script). In your case, you could do an ALTER to change a data type:
[font="Courier New"]CREATE TABLE doc_exy ( column_a INT ) ;
GO
INSERT INTO doc_exy (column_a)
VALUES (10) ;
GO
ALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE doc_exy ;
GO[/font]
This operation is still likely to take quite some time on a large table since the database engine has a lot of moving around of information to complete the task.
June 30, 2008 at 3:41 pm
Michael,
You wrote
>>
...Rather than saving the change, click the "Script Change" button and look at what it does.
>>
That's a good idea but I couldn't locate the "Script Change" button in Management Studio. Please advise.
TIA,
barkingdog
June 30, 2008 at 4:24 pm
You will have to take the downtime. I don't think there is any other way of doing it.
The one available is the same what is done in background by sql server:
1. Create new table with updated design.
2. Export data from old to new table.
3. Drop Old table.
4. Rename new table with old name.
Is there any clustered index on this table? If yes, then is it on same field that you are trying to modify?
Manu
July 1, 2008 at 5:07 am
The generate script button is the first button on the table designer toolbar by default.
Also, when in the table designer, there is a Table Designer menu and it will be the last menu item.
July 2, 2008 at 5:23 am
I think a faster approach (typically) is:
1. add a new column with the new specification
2. run an update query to update the new column from the old
3 rename the old and new columns
4. delete the old column
If you reference your columns with an ordinal number, or rely on select * (and the column order matters), you may want to move the new column to the position of the old.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply