January 17, 2011 at 10:54 pm
Hello
Can someone please shed some light on Alter Table <> add column_name..
Very frequently developers write query like
Alter table mytable add mycol1 int
Alter table mytable add mycol2 int...
Here, for same table 2 times alter table is executed while this can be achieved with single alter table command
I have to always tell them to make one statement.
I plan to publish one notification, which would need , may be some reference or high resource consumption usage.
January 18, 2011 at 2:42 am
Two statements will results in two transactions, one statement will result in one transaction.
Is there any other reason to make a distinction?
The performance/resource consumption isn't that high, unless you have gigantic tables to alter.
In that case, I would even prefer seperate statements, otherwise you'll be stuck with one gigantic transaction.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 18, 2011 at 3:55 am
will it be treated as 2 differenet transaction?
not much..I think the SQL governor will govern them with the same set of transaction unless there is explicit BEGIN TRAN defined for each line sets in 'Alter table' statement.
January 18, 2011 at 4:20 am
khushbu (1/18/2011)
will it be treated as 2 differenet transaction?not much..I think the SQL governor will govern them with the same set of transaction unless there is explicit BEGIN TRAN defined for each line sets in 'Alter table' statement.
In Autocommit mode (which is the default transaction mode for SQL Server), two statements in a batch should be treated as two different transactions, unless there is a compile error, which causes the entire batch to fail or if there is an explicit transaction defined (which you already mentioned).
http://msdn.microsoft.com/en-us/library/ms187878.aspx
Unless I overlooked something of course 🙂
But to be absolutely certain that two different statements are two different transactions, you can always seperate them with the GO batch separator.
In my opinion, if you do not look at transactions, there is no real benefit in using 1 or 2 statements to add a column.
(Maybe of there is a lot of concurrency going on and you want to release locks as soon as possible?)
But if you like shorter code or if you want a uniform way of writing code, then you can set conventions on how to write alter statements.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 18, 2011 at 4:44 am
Khusbu,
You should educate the developers with best practices on writing the script. As koen has already explined about two different transactions, I would i only one comment "check the column existenace before adding it use - IF EXISTS".
Abhijit - http://abhijitmore.wordpress.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply