November 23, 2010 at 8:23 pm
Comments posted to this topic are about the item Transactions
November 23, 2010 at 8:29 pm
That one was easy for me wich proves that "Today's Question" works well for people like me because I applied things I learned from an earlier "Today's Question"... unless I got lucky :w00t:
Thank you!
November 24, 2010 at 12:32 am
Hello!
This was very similar to one of my own QotDs a few months back.
However, what I take from this one is that the DDL is not committed automatically because it is wrapped inside another transaction, which when rolled back, rolls back everything till the outermost BEGIN TRAN.
I also did the test by using IMPLICIT_TRANSACTIONS ON/OFF, and the result was the same.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
November 24, 2010 at 12:40 am
Should be worth 2 points.
- Cant copy/paste
- Multichoice question
- Judging by right/wrong ratio (53/47 at time)
/T
November 24, 2010 at 2:24 am
Nice question.
CREATE PROC [dbo].[QOD_TransTable]
What is the purpose of this procedure? 🙂
Insert into dbo.Items(ItemId,Item,column_b)SELECT 1,'first','New'
GO;
This part stumped me for a while. 🙂 Due to previous questions, I know that a GO with a semicolon doesn't mean "batch separator" (in this case, it is a column alias). Luckily, there's no trick in this part.
November 24, 2010 at 5:07 am
Nice easy question.
Appalled that half the answers to date have been wrong.
Tom
November 24, 2010 at 5:10 am
Good question. Many people (24% at this time) tend to think that DDL is somehow excluded from rollbacks, so it's good to help educate them.
I was unaware of the issue with a semicolon after GO. Thanks for alerting me to that, vk-kirov. Saves me some points in case someone ever makes a QotD out of that. (Though I'll probably overlook it anyway, just as I did now).
November 24, 2010 at 5:28 am
Indeed, thank-you, vk-kirov for alerting us on the use of GO as a column alias. I was unaware of it as well.
As Hugo rightly mentioned, even with this knowledge, chances are that I will miss this particular use of 'GO;' the next time I see it.
Have a great rest-of-the-day, everyone!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
November 24, 2010 at 6:08 am
Nice easy question? Unless I'm missing something, the CREATE TABLE, the first INSERT, the BEGIN TRAN and the ALTER TABLE statements are all inside a stored procedure which is not executed; the second INSERT, both SELECTs and the ROLLBACK TRAN are outside of the procedure. Where's the option for "won't compile"?
November 24, 2010 at 7:05 am
richardd (11/24/2010)
Nice easy question? Unless I'm missing something, the CREATE TABLE, the first INSERT, the BEGIN TRAN and the ALTER TABLE statements are all inside a stored procedure which is not executed; the second INSERT, both SELECTs and the ROLLBACK TRAN are outside of the procedure. Where's the option for "won't compile"?
Note the comments. The CREATE PROC is indeed confusing, but the comments clearly state that
(a) The specified table is in the database;
and
(b) The following SQL is executed.
If you look carefully, you'll even see that this is not one, but two seperate images (there is some white space in between the two).
While this is not as clear as it should be, I think that the comments, the white space, and the available answer options combined make it clear what the intention of the question is.
November 24, 2010 at 7:08 am
I agree with the "nice" part, but it's not an easy one if you don't regularly poke around altering tables, using transactions etc.. (judging by the figures: 50% wrong, it seems not an easy question to a lot of folks)
I had to check MSDN first to reassure the ALTER TABLE within a transaction would be rolled back (as Hugo pointed out, a good learning opportunity), then to see if a transaction can consist of multiple batches (it can, which of course makes perfect sense when you think of it).
Having found that out, I answered it correctly, but found the explanation quite meager.
Thanks for a nice question!
Peter Rijs
BI Consultant, The Netherlands
November 24, 2010 at 8:44 am
I got it wrong, as the last time I tried any DDL within a transaction was 15 years ago in my Oracle days, when such changes could not be rolled back (no idea if they can in current versions of Oracle).
I've never found this to be a problem though. Under what circumstances would you want to do DDL in a transaction?
November 24, 2010 at 9:07 am
Toreador (11/24/2010)
I got it wrong, as the last time I tried any DDL within a transaction was 15 years ago in my Oracle days, when such changes could not be rolled back (no idea if they can in current versions of Oracle).
Transaction handling has not been changed since those days.
Here is a quote from the Oracle documentation (http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/transact.htm):
A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.
November 24, 2010 at 9:18 am
Toreador (11/24/2010)
I've never found this to be a problem though. Under what circumstances would you want to do DDL in a transaction?
I do that all the time. I develop a code generator. My repository supports multiple versions of the model. The generator generates SQL scripts to bring the database from one version to another. Those script only work if the database is actually at the expected starting version. The scripts use logic to check this, and won't run if the database is in another version.
But if such a script runs into an error, I don't want to leave the database in an inconsistent "between versions" state. I know the possible causes of errors: either an error in the tiny bits of code that the user has to supply and that is embedded in the generated code is erroneous, or the constraints in the new version are more strict than in the old version, and the existing population cause the new constraints to be violated. Since I know where in the script these errors can occur, the script includes logic to test for these errors. And if one occurs, the entire script is rolled back, so that the database remains in its original version. After correcting the issue, the script can be re-executed, and will complete.
Without transaction affecting DDL, this would neven have been possible.
November 24, 2010 at 9:21 am
This is a very good question, thank you Ron. The only thing difficult to figure out is that you mention that database contains this table in the beginning but then there is a procedure definition, and unfortunately, it is not mentioned anywhere that in order for the script starting from the following T-SQL is executed to run, someone needs to execute the procedure first, not just create it. It goes without saying that such procedure will never exist in real life, because it must be executed, it must be executed only once in its lifetime, and it assumes that the actual table is not there before the procedure was created and once and only once executed.
I made an assumption that execute procedure is implied, so I just disregarded the top part of the script in question and it helped me to figure the answer correctly. The behaviour of the go; is inconsistent, and I am aware that the chances to have some query with go; either execute successfully or fail miserably are roughly equal and depend on whether it is possible for the parser to use it as an alias or not, i.e.
use AdventureWorks;
go
-- this works just fine (go is used as an alias)
select DepartmentID, [Name]
from HumanResources.Department
go;
-- this fails miserably
select DepartmentID, [Name]
from HumanResources.Department
order by DepartmentID
go;
I learned the details of the above behaviour the hard way some time in the past, so if someone will tell me that thou shalt not use the semicolon after the go batch terminator, he will be preaching to the converted.
Once again, thank you for the question, I really enjoyed it.
Oleg
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply