I saw an article recently about implicit transactions and coincidentally, I had a friend get caught by this. A quick post to show the impact of this setting.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Scenario
You run this code:
Everything looks good. I ran an insert and I see the data in the table. I’m busy, so I click “close” on the tab and see this.
I’ve gotten so used to these messages, and annoyed by them in SSMS, I click “No” to get rid of it and close the window.
The Problem
A short while later I open a query window to do something related and check my data. I don’t see it.
What happened? I had implicit transactions set. This might happen if you mis-click this dialog. Ths option is close to the ANSI_NULL_DFLT_ON option.
You could also, or someone could in your terminal (as a poor joke) run this:
SET IMPLICIT_TRANSACTIONS ON
In either case, this means that instead of that insert running as expected, it really behaves like this:
BEGIN TRANSACTION
INSERT dbo.CityName
(
CityName
)
VALUES
(‘Parker’)
If I don’t explicit commit (or click “Yes”) then this isn’t committed.
Be wary of implicit transactions. It’s a setting that goes against the way many of us work and can cause lots of unexpected problems. This is a code smell I would never want in my codebase.
SQL New Blogger
When I ran into this twice in a week, I decided to spend 10 minutes writing this post. It’s a chance to explain something and give a recommendation. Something every employer wants.
