December 29, 2015 at 10:06 pm
Comments posted to this topic are about the item Explicit Transactions
December 30, 2015 at 4:29 am
great article. it's nice to revisit some basic topics from time to time. I never used marked transactions before, so I liked that example.
cya
December 30, 2015 at 6:31 am
Great article. I learned a lot today. Thanks.
December 30, 2015 at 6:55 am
Please keep in mind, or disproove, that an explicit rollback also reverts any SQL module compilations done in the course of the transaction (in the same session).
December 30, 2015 at 7:30 am
Great article! I know now what I didn't understand until now. Thanks
December 30, 2015 at 9:38 am
Excellent article.
A couple of points where it could maybe be improved:-
In the Identity Columns section, while the statement "any IDENTITY columns do not have the values reverted" is true it's pretty meaningless, since values in IDENTITY columns can't be changed, and potentially misleading as it may lead people to think that they can be changed, and in the rest of that sentence your reference to the bext available identity value should be to the next automatically generated identity value. If you were intending to suggest that the IDENT_CURRENT table property is part of the value of the column your statement makes sense of a sort but if so you should state it because people generally don't think that way.
It would be better to include a SQL Variables section between Identity Columns and Table Variables, to say that SQL Variables are not affected by rollback, using something like
DECLARE @TestValue int to produce an example. Then the Table variables section could say "As with other SQL variables, Table Variables are not reverted by a ROLLBACK" instead of referring to IDENTITY.
Tom
December 31, 2015 at 3:24 am
Great article - thanks.
Maybe an error in Listing 1 - should not the SP RestoreTestTable include the drop/create of the test table ?
December 31, 2015 at 3:33 am
Thank you Steve for this article, very good job indeed.
😎
January 4, 2016 at 4:17 am
Interesting stuff and a few things I wasn't aware of.
Would have liked to see errors and xact_abort covered also as this has caught me out in the past.
January 22, 2016 at 9:50 am
Nice article Steve on what can be a complex subject matter.
qh
July 13, 2018 at 9:45 am
sTTu - Monday, January 4, 2016 4:17 AMInteresting stuff and a few things I wasn't aware of.Would have liked to see errors and xact_abort covered also as this has caught me out in the past.
Hi there. I posted an answer to DBA.StackExchange a few years ago on this topic and did include error handling, XACT_ABORT, XACT_STATE(), etc:
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 13, 2018 at 10:41 am
Great explanation of transactions--an important topic with intricacies that every developer should understand. I do think the article pertains equally well to implicit transactions, though, and could use a paragraph or two explaining the benefits of managing transaction scope explicitly.
July 13, 2018 at 12:23 pm
The name can be in excess of 32 characters, but only the first 32 characters are actually used.
Hi Steve. Regarding the statement from the article quoted above (pertaining to transaction name length), please note that the silent truncation you are referring to only applies to using a variable for the transaction name. If you are using a literal / constant for the transaction name, then you will get an error if you attempt to use more than 32 characters.
Please also note that transaction names are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.
For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 13, 2018 at 12:57 pm
Solomon Rutzky - Friday, July 13, 2018 12:23 PMThe name can be in excess of 32 characters, but only the first 32 characters are actually used.
Hi Steve. Regarding the statement from the article quoted above (pertaining to transaction name length), please note that the silent truncation you are referring to only applies to using a variable for the transaction name. If you are using a literal / constant for the transaction name, then you will get an error if you attempt to use more than 32 characters.
Please also note that transaction names are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.
For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers
Take care,
Solomon...
I thought that binary collations were case-sensitive? Decided to check for myself and was surprised.
July 13, 2018 at 1:09 pm
Lynn Pettis - Friday, July 13, 2018 12:57 PMSolomon Rutzky - Friday, July 13, 2018 12:23 PMPlease also note that transaction names are treated as having a binary Collation (not case-sensitive as the documentation currently states), regardless of the Instance-level or Database-level Collations.For details, please see the Transaction Names section of the following post: What’s in a Name?: Inside the Wacky World of T-SQL Identifiers
Take care,
Solomon...I thought that binary collations were case-sensitive? Decided to check for myself and was surprised.
It is a very common misconception that binary comparisons are case-sensitive (or anything sensitive, such as accent-sensitive, or width sensitive, etc), which is why I was clarifying there. Regarding the "checking", were you referring to this post:
No, Binary Collations are not Case-Sensitive
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply