August 17, 2011 at 1:46 pm
I need to clear this up in my head, after reading what I could find with a search.
I know that the "GO" statement is an OSQL command that separates batches being sent to the server. I have also read that a "GO" may exist inside an explicit transaction.
Does that effectively create separate implied transactions on the server if not within an explicit transaction?
For instance, in a table with 100,000 rows:
Update Table1
Set Field6 = Null
Where IsDate(Field6) = 0 --this is for rows that have a "0" in them.
Go
Alter Table1
Alter Field6 Date Null
Go
Update Table1
Set Field9 = Null
Where IsDate(Field9) = 0
Would the above, being executed from SSMS all at once, effectively create 3 implicit transactions for each of the batches, or would the whole thing execute as one execution plan?
Do I need to write my script like this to get transactions where I want them?
Begin Tran T1
Update Table1
Set Field6 = Null
Where IsDate(Field6) = 0 --this is for rows that have a "0" in them.
Commit Tran T1
Begin Tran T2
Alter Table Table1
Alter Column Field6 Date Null
Commit Tran T2
Begin Tran T3
Update Table1
Set Field9 = Null
Where IsDate(Field9) = 0
Commit Tran T3
Again, does the "GO" key word have the affect of creating a separate transaction for each batch since it makes SSMS separate the "batches" of statements sent to the server?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
August 17, 2011 at 2:09 pm
Stamey (8/17/2011)
I need to clear this up in my head, after reading what I could find with a search.I know that the "GO" statement is an OSQL command that separates batches being sent to the server. I have also read that a "GO" may exist inside an explicit transaction.
Does that effectively create separate implied transactions on the server if not within an explicit transaction?
For instance, in a table with 100,000 rows:
Update Table1
Set Field6 = Null
Where IsDate(Field6) = 0 --this is for rows that have a "0" in them.
Go
Alter Table1
Alter Field6 Date Null
Go
Update Table1
Set Field9 = Null
Where IsDate(Field9) = 0
Would the above, being executed from SSMS all at once, effectively create 3 implicit transactions for each of the batches, or would the whole thing execute as one execution plan?
Do I need to write my script like this to get transactions where I want them?
Begin Tran T1
Update Table1
Set Field6 = Null
Where IsDate(Field6) = 0 --this is for rows that have a "0" in them.
Commit Tran T1
Begin Tran T2
Alter Table Table1
Alter Column Field6 Date Null
Commit Tran T2
Begin Tran T3
Update Table1
Set Field9 = Null
Where IsDate(Field9) = 0
Commit Tran T3
Again, does the "GO" key word have the affect of creating a separate transaction for each batch since it makes SSMS separate the "batches" of statements sent to the server?
Forget about the "GO".
When working on auto commit transaction mode - SQL Server default mode, each single Transact-SQL statement is committed or rolled back when it completes.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 17, 2011 at 2:17 pm
"GO" is a feature of OSQL/SQLCMD/SSMS. Feel free to change it to "FOOBAR" in menu -> tools -> options -> query execution -> sql server -> general -> "batch separator" 😀
By default SQL Server executes every single statement in an own transaction, if no explicit transaction available. Even for DDL.
Try this, you will see, the "Foo" column will still be created:
CREATE TABLE #t (Id INT);
GO
ALTER TABLE #t ADD Foo INT;
ALTER TABLE #t ADD Id INT;
GO
SELECT * FROM #t^
Greets
Flo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply