August 6, 2016 at 10:56 pm
Comments posted to this topic are about the item Lots of inserts? Part II
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 6, 2016 at 10:57 pm
Good question thanks.
...
August 8, 2016 at 12:25 am
This was removed by the editor as SPAM
August 8, 2016 at 1:57 am
Great question!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2016 at 5:27 am
Nice question to get the grey matter moving on a Monday morning. Simple and straightforward. Thanks.
August 8, 2016 at 8:15 am
It's a nice question, but two things surprise me:
(a) that something this fundamental about ROLLBACK and/or GO is considered advanced/hard/obscure enought to be worth 2 points.
(b) that 55% of the first 338 answers were wrong.
Maybe (b) indicates that whoever decided it was worth 2 points has a much clearer picture of how much people don't know about SQL Server's data engine than I have and I'm wrong to be surprised by either (a) or (b).
Tom
August 8, 2016 at 9:08 am
I decided it was worth 2 points because it's tricky and this could be confusing to people that conflate batches with transactions, or misunderstand how "GO n" works. It's tricky enough to confuse people.
August 8, 2016 at 9:10 am
TomThomson (8/8/2016)
. . . Maybe (b) indicates that whoever decided it was worth 2 points has a much clearer picture of how much people don't know about SQL Server's data engine than I have and I'm wrong to be surprised . . .
Sure, Tom. I think you do not realize how much you know. A year ago I mentored a group of aspiring DBAs and it gave me a good insight into how much one has to know to be in that profession.
And thanks to Kenneth for the question.
August 8, 2016 at 10:36 am
Nice question and a very good detailed explanation, thanks Kenneth. 🙂
The key to the correct answer is the ROLLBACK.
August 9, 2016 at 8:05 am
No discussion about nested transactions would be complete without a reminder that they are really a myth and do not do what it seems like a real nested transaction should. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2016 at 9:19 am
Sean Lange (8/9/2016)
No discussion about nested transactions would be complete without a reminder that they are really a myth and do not do what it seems like a real nested transaction should. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]
The idea that SQL Server doesn't support nested transactions is the myth. When it does allow them the nest can have only one transaction at each level; only the outermost transaction uses "begin transaction" and "commit transaction" commands (unfortunately SQL Server doesn't enforce this, it allows meaningless begin transaction and commit transaction commands at levels other than the outermost, and this cause pointless confusion); inner transactions use "save transaction" with a mandatory name parameter to begin a transaction, and "rollback transaction <name>" to roll back the nest to the point immediately before the beginning of the named transaction; only the outermost transaction can be committed. To rollback the whole nest the rollback transaction command can either specify the name of the outermost transaction (specified in the begin transaction statement) or not provide a name, it doesn't matter which it uses as both mean the same.
The idea of commiting an inner transaction is just plain bizarre, so perhaps people should apply a little common sense and realise that anything that uses "commit" for inner transactions is not a transaction nest, it's just a counter (and it's this same bizarreness of inner commit that precludes more than one subtransaction at the same inner level as the T-SQL language doesn't express any parellism).
Perhaps things would have looked much cleaner if begin transaction had not been allowed in an in-transaction execution context, commit transaction had not allowed a (meaningless) name parameter, and the command for beginng an inner transaction had been named something like "begin subtransaction" instead of save transaction.
So there are two myths here:
Myth 1: using "begin transaction" inside a transaction creates a nested transaction
Myth 2: SQL Server doesn't support nested transactions
The big problem is that people keep on claiming that Myth 1 being a Myth means that Myth 2 is not a myth, and that's just plain silly.
Tom
August 9, 2016 at 9:55 am
Sean Lange (8/9/2016)
No discussion about nested transactions would be complete without a reminder that they are really a myth and do not do what it seems like a real nested transaction should. http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/[/url]
Absolutely! And I should have included that in the answer. Thanks for mentioning it.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 18, 2016 at 8:21 pm
Steve Jones - SSC Editor (8/8/2016)
I decided it was worth 2 points because it's tricky and this could be confusing to people that conflate batches with transactions, or misunderstand how "GO n" works. It's tricky enough to confuse people.
I continue to see people that have never seen GO n, so I can see the scoring logic there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply