| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2006-12-28 : 10:39:29
|
| 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 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-28 : 10:46:20
|
| 1. Take a look at this:[url]http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html[/url]2. There is going to be IO overhead. Just check your data file and log file size. I am sure you will see them increasing drastically during the operation.3. You are right. Since data type is Char(10), it will try to allocate exactly 10 bytes for each row (by padding column values with spaces). That is the reason for it being slow. Using Varchar definitely will make it fast.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-12-28 : 10:52:25
|
| 1. yes. new table created, old data moved over to new table. old table deleted. new table renamed. shortcut (for you)....unload data to 'text file', create new table, use BCP or similar tool to re-load data....avoiding logging!....and continue as above.2. investigate io issues....start with the "profiler" tool.3. "shifted over" is a presentation issue...sql data is not necessarily stored contiguously. varchar has a cost....extra bytes reserved to manage actual length of the varchar feld....disadvantage comes when varchar contents expand....and also when varchar is more full than empty....ie varchar(10) has no advantages over char(10) when filled with 10 chars...other disadvantage is "preserving trailing spaces"...if required....users/dba/programmers will in general will drop(cause to be dropped) these..... |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-28 : 11:48:30
|
| it looks like using VARCHAR is always more advantageousThat depends. If every value in that column is always exactly 10 characters in length then char(10) is better. If some values might be between 0 and 10 characters in length then varchar(10) would be better. Either way, make the decision based on your data, not on which one will make this one time change more quickly.One more thing, did you try to make the change in Enterprise Manager or did you use an ALTER TABLE statement? Enterprise Manager copies the entire table to the new structure, then drops the old table and renames the copy. It may be faster, and it will use less disk space if you run an ALTER TABLE, which doesn't copy the table like EM does. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2006-12-28 : 12:29:58
|
| For SQL Server 2005 these posts are informative:http://blogs.msdn.com/sqlcat/archive/2006/03/01/541550.aspxhttp://blogs.msdn.com/sqlcat/archive/2006/03/31/566046.aspx===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2006-12-28 : 17:11:12
|
| Guys,Thanks for all the help. I am using SQL Server 2000 and did make the change through the Enterprise Manager. Looking at the script I do see that a temp table is created, data is inserted into it and the old table is dropped (not to mention recreation of all the constraints).Question: Are you saying that if I do it through the ALTER TABLE command the same procedure will not be followed internally (i.e. the table will not be dropped and recreated)?And if so, then why doesn't Enterprise Manager follow this, more efficient approach, internally?Thank you |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-28 : 17:27:39
|
| No, an ALTER TABLE doesn't copy everything like the EM script does. You know exactly what you want to do, EM is trying to allow for lots of possibilities as safely as possible so it always does things that way - if you made a lot of changes then it may well even be better. But, you know you just want that one change, so you can do it directly. |
|
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2006-12-29 : 09:22:41
|
quote:
Originally posted by sql_er
Guys,Thanks for all the help. I am using SQL Server 2000 and did make the change through the Enterprise Manager. Looking at the script I do see that a temp table is created, data is inserted into it and the old table is dropped (not to mention recreation of all the constraints).Question: Are you saying that if I do it through the ALTER TABLE command the same procedure will not be followed internally (i.e. the table will not be dropped and recreated)?And if so, then why doesn't Enterprise Manager follow this, more efficient approach, internally?Thank you
Enterprise manager is evil  I have had endless arguments with our developers about using it, and it wasn't until one of them did this (in our dev environment) that they started to appreciate just what it was I was trying to say. You might fine the following [url]http://vyaskn.tripod.com/sql_enterprise_manager_or_t-sql.htm[/url] to be a useful discussion. I tend to agree with the article there are some things that are better done with the GUI (Creating and scheduling jobs) but NOT Many. In fact I think the only one that can't be done (In SQL2K) any other way is designing DTS Packages, (For this I hate the guys at Microsoft, although when we move to SQL2K5 I'll love them  ) although I stand to be corrected.What I can't figure is developers will invest time learning a whole new language, and yet not bother to learn the relativly simple syntax of SQL-- RegardsTony The DBA |
|
|
|
|
Mystery - Table design modification