January 20, 2015 at 10:43 am
Thank you for that one.
January 20, 2015 at 12:33 pm
Thanks for the learning opportunity.
January 21, 2015 at 10:12 am
Nice question, thank you.
Some additional explanation from BOL (https://msdn.microsoft.com/en-us/library/ms175523%28v=sql.105%29.aspx):
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement.
Also, note that removing the GO before the DELETE in Batch Two does prevent the DELETE from executing. This version does not empty the table:
BEGIN TRAN
SELECT COUNT(*) AS TheBeginning FROM Tab
GO
CREATE TABLE Tab(i INT) -- raises error
--GO
DELETE FROM Tab
GO
WHILE @@TRANCOUNT > 0
ROLLBACK
SELECT COUNT(*) AS TheEnd FROM Tab
Rich
January 22, 2015 at 12:55 am
rmechaber (1/21/2015)
Nice question, thank you.Some additional explanation from BOL (https://msdn.microsoft.com/en-us/library/ms175523%28v=sql.105%29.aspx):
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement.
Also, note that removing the GO before the DELETE in Batch Two does prevent the DELETE from executing. This version does not empty the table:
BEGIN TRANSELECT COUNT(*) AS TheBeginning FROM Tab
GO
CREATE TABLE Tab(i INT) -- raises error
--GO
DELETE FROM Tab
GO
WHILE @@TRANCOUNT > 0
ROLLBACK
SELECT COUNT(*) AS TheEnd FROM Tab
Rich
This is a short script, just for example. Often, my scripts are over 50 lines, with create/alter views/functions/store procedures, as you well know they should be the first and only statement in the batch, so the batch separator is mandatory.
January 22, 2015 at 2:36 am
Shame - I am sure I selelcted the correct answers, but the site tells me I am wrong and that the correct answer is what I selected. (Or think I selected).
When I click back on my browser to verify what I ticked, I get a "document expired" message, so I'll just have to accept that I must have mis-clicked and that it's not a database error.
Not too thrilled with this question. Lots of work and thinking for a feature that should have been deprecated and removed twenty years ago. Or failing that, should be banned from use in any serious company.
January 22, 2015 at 2:46 am
Hugo Kornelis (1/22/2015)
Not too thrilled with this question. Lots of work and thinking for a feature that should have been deprecated and removed twenty years ago. Or failing that, should be banned from use in any serious company.
What and why do you think it should be deprecated?
January 22, 2015 at 7:20 am
Carlo Romagnano (1/22/2015)
Hugo Kornelis (1/22/2015)
Not too thrilled with this question. Lots of work and thinking for a feature that should have been deprecated and removed twenty years ago. Or failing that, should be banned from use in any serious company.What and why do you think it should be deprecated?
Implicit transaction. They are just too confusing. Too easy to make mistakes and accidentally leave a transaction open for a long time. Or roll back way more than you intended.
January 22, 2015 at 7:43 am
Hugo Kornelis (1/22/2015)
Carlo Romagnano (1/22/2015)
Hugo Kornelis (1/22/2015)
Not too thrilled with this question. Lots of work and thinking for a feature that should have been deprecated and removed twenty years ago. Or failing that, should be banned from use in any serious company.What and why do you think it should be deprecated?
Implicit transaction. They are just too confusing. Too easy to make mistakes and accidentally leave a transaction open for a long time. Or roll back way more than you intended.
If application/script/procedure have bugs, it's not because of IMPLICIT_TRANSACTIONS.
January 23, 2015 at 12:32 pm
Carlo Romagnano (1/22/2015)
Hugo Kornelis (1/22/2015)
Carlo Romagnano (1/22/2015)
Hugo Kornelis (1/22/2015)
Not too thrilled with this question. Lots of work and thinking for a feature that should have been deprecated and removed twenty years ago. Or failing that, should be banned from use in any serious company.What and why do you think it should be deprecated?
Implicit transaction. They are just too confusing. Too easy to make mistakes and accidentally leave a transaction open for a long time. Or roll back way more than you intended.
If application/script/procedure have bugs, it's not because of IMPLICIT_TRANSACTIONS.
Actually it often is; you find lots of code that switches implicit transactions on and then calls code that starts an explicit transaction "just in case" and commits it (correctly,effectively commiting nothing if it was called from within a transaction but decrementing trancount because that's what commit is supposed to do). This code which has been written to behave correctly whether it's called from within a transaction or from a not transaction context creates total disacter when called with implicit transactions enabled but not within a transaction. Sure, it's possible to write code to get around it but the rubbish that forces you to do that is such bizarre idiocy that my view is that it's the "implicit transactions" imprelentation that is responsable for the resulting problems. Writing code to look at trancount and decide what to do acording to its value anywhere other than in error handling routines is highly undesirable, but use of implicit transactions means such code is needed all over the place, and I've never wanted my people (other than those trusted to write the guts of error management code) to be stuck with such a bizarre task.
Tom
January 30, 2015 at 4:36 am
Very interesting question
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply