November 19, 2003 at 7:49 pm
Kinda dumb question --- sorry
What are sql internals while altering tables - adding, updating, or delting columns?
Does sql have to drop the table and recreate a new one each time..???
Thx..
November 20, 2003 at 2:35 am
Not a dumb question. Through straight T-SQL, SQL will only let you do a limited set of ALTERations to a table (add/delete columns, etc) that will keep the existing table intact. ie. NOT drop/recreate the table.
On the surface it appears that Enterprise Manager is much more sophisticated, allowing changes like adding a new column between existing columns, rather than tacking the column on the end.
If you use Profiler to track what Enterprise Manager is doing, you'll see that it's achieving this by copying existing data to a "work" area, recreating the table and indexes, and then copying the data back in.
Whilst this is a boon in a development area, it can be a disaster for the unsuspecting and soon-to-be-unemployed DBA in a production environment - when one does what looks like a simple change via EM to a BIG table, and everything grinds to a halt.
I adopt a policy of only implementing reviewed T-SQL scripted changes in production, NOT a set of Enterprise Manager instructions.
Cheers,
- Mark
Cheers,
- Mark
November 20, 2003 at 3:27 am
infact,
MS EM allows you to see and copy and save the script that EM will execute in order to do the change.
just click on 3rd icon on toolbar of table designer. and see the TSQL script in action.
November 20, 2003 at 4:09 am
The key problem with EM is when people are trying to add a column in the middle of existing columns. For the table to be altred so it has to be recreated and thus a very large table will take a long time and space could potentially be an issue.
I suggest making sure column adds be done to the end of the table, don't worry doing column name chnages, alias in views if need a reference but at least document the table strucuture anyway. And make sure any change is preceeded by a full backup for safety.
November 20, 2003 at 10:45 am
Gracias, guys... Is there any way of specifying location of new column via sql analyzer (I meant T-sql)..? Via EM, you can just go insert it anywhere you want (of course at the expense of re-bilding the table), but is there anything similar we can do using T-Sql.. As usual, all your help is appreciated..
Thx.
November 20, 2003 at 11:59 pm
This might be interesting for you http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17899
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply