Mystery - Table design modification
-
Guys,
I have a table with 17 columns (a mixture of int, varchar, char, datetime) and 13 million records. It has no indexes and is not referenced by any other object (i.e. it was created for the testing purposes). One of its fields is CHAR(1). I want to change it to CHAR(10). When I make a change and save the table, it takes forever to finish. More specifically, I waited for over 3 hours already and it is still not done. I am currently trying to explore the possible reasons and need advise from you guys.
This table sits within a database which is located on a busy server.
A few questions arose in the exploration of this issue:
1. What happens internally when a data type is changed in a table definition? Is something dropped and recreated?
2. I am currently thinking that there could be IO issues involved. I remember reading somewhere how one can explore IO problems, but cannot locate this article. Could anyone suggest how I can explore if I have IO issues with my server?
3. I was considering to try to change CHAR(1) to VARCHAR(10) instead of CHAR(10). I am not sure if it would make a difference. My reasoning was that for CHAR(10), exactly 10 bytes would need to be allocated for each such record, forcing all records, for every row, to be shifted over. Is this true or it would not matter? And in general, from what I've learned, it looks like using VARCHAR is always more advantageous. Could anyone specify when using CHAR could be more advantageous?
Any suggestion is appreciated.
Thank you very much
-
It's definitely IO. Is this SQL 2000 or 7? 7 will be worse, but in either case, you can make the change in EM and then hit the "script button" (near the save button) and it will show you what it does. It basically makes a new table, copies the data, deletes the old table and renames the new. (Might have this out of order).
You can take the script, don't save changes, and then do this yourself. If you don't need this table done quickly, create the new one, and then insert the data in batches. You can clear the T-log/set to simple mode while this happens, since part of the issue is that your inserts are being logged.
When you're done, drop or truncate the old table, DON't DELETE. Too much logging.
Batching: http://www.sqlservercentral.com/columnists/sjones/batching.asp
-
1. Are you making this change in Enterprise Manager? If so, it takes a long time because EM will create a new table with the new column datatype, insert the data from the old table to the new one, drop the old table, and rename the new table. With a 13,000,000 row table, that'll take a while. It's generally faster to do this in T-SQL using ALTER TABLE ALTER COLUMN.
2. I don't have the links handy, but I remember reading some good stuff about I/O performance on http://www.sql-server-performance.com.
3. You can find an explantion of CHAR and VARCHAR in BooksOnLine but, basically, use CHAR if you have fixed length character data such as U.S. state abbreviation. It's always two characters, so no need to use varchar. If the will vary in size, use varchar.
Greg
Greg
-
If done using enterprise manager, the script generated will create a new table with the corrected design, then copy all rows to that table, then recreate drop the old table and rename the new one.
This is done instead of using a single alter table command which would definitly be much faster (backward compatibility issues). I don't know weather to suggest you to kill the job or let it run. I'd assume it to be done after 3 hours unless you have a dead lock
(run sp_who2 in Query analyser and search for a interger value in the blocked by column). If you see your spid in there just kill it (KILL < you spid here >
.Then I suggest you install a developpment station where you can screw around and not interfere with production work.
The correct alter command would look like this :
ALTER TABLE dbo.TableName
ALTER COLUMN ColName CHAR(10) [NOT] NULL (not is optional)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply