Alexander Kuznetsov : Transact SQL

Yet another use of OUTER APPLY in defensive programming

Alexander Kuznetsov — Wed, 06 Jun 2012 21:58:00 GMT

When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem. Prerequisites All we need is the following mock...(read more)

Catching multiple exceptions on the client is robust and easy

Alexander Kuznetsov — Wed, 06 Jun 2012 17:30:00 GMT

Maria Zakourdaev has just demonstrated that if our T-SQL throws multiple exceptions, ERROR_MESSAGE() in TRY..CATCH block will only expose one. When we handle errors in C#, we have a very easy access to all errors. The following procedure throws two exceptions:...(read more)

Avoiding nested transactions might not improve performance.

Alexander Kuznetsov — Wed, 08 Feb 2012 18:09:00 GMT

Beginning a transaction only when @@TRANCOUNT=0 might not improve performance at all. At least, I did not notice any difference whatsoever. No matter if I use this pattern: BEGIN TRAN ; -- (snip) COMMIT ; or a more complex one: DECLARE @trancount INT...(read more)

Using XACT_ABORT ON may be faster than using TRY...CATCH

Alexander Kuznetsov — Wed, 01 Feb 2012 22:25:00 GMT

To ensure atomicity of transactions, we can use XACT_ABORT ON or wrap the transaction in TRY block and rollback in CATCH block. In some cases, the XACT_ABORT ON approach uses noticeably less CPU. I am posting repro scripts. Please run them, tweak them,...(read more)

Wrapping related changes in a transaction may use less CPU.

Alexander Kuznetsov — Wed, 01 Feb 2012 22:02:00 GMT

Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself. Environment...(read more)

Yet another gotcha: variables' scopes do not end where they should.

Alexander Kuznetsov — Wed, 25 Jan 2012 22:09:00 GMT

Be careful: unlike most other languages, T-SQL does not limit variables' scope to the block where the variable has been defined. For example, the following snippet compiles and runs: -- @to is not in scope yet -- the line below would not compile --SET...(read more)

Running OLTPish system without deadlocks, and loving it.

Alexander Kuznetsov — Wed, 04 Jan 2012 22:31:00 GMT

Our OLTPish mixed load system has not had a single deadlock since last April, and we just love it. I would not make any blanket statements, but I think in our case being deadlock-free just makes a lot of practical sense. Of course, in other cases in might...(read more)

"Trusted" Foreign Keys Allow Orphans, Reject Valid Child Rows

Alexander Kuznetsov — Mon, 17 Oct 2011 17:15:00 GMT

In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save. False Negatives The following tables implement a very common type/subtype...(read more)

Preventing Bob from sales from selecting other people's messages

Alexander Kuznetsov — Thu, 18 Aug 2011 19:22:00 GMT

Suppose that Bob can retrieve all messages, as follows, and we are not happy with it: EXEC Exchange.ShowAllMessages ; Suppose that we don't want him to read anything related to messages, so our knee-jerk reaction is this: REVOKE EXECUTE ON Exchange.ShowAllMessages...(read more)

Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent.

Alexander Kuznetsov — Wed, 03 Aug 2011 01:33:00 GMT

When a select uses scalar or multi-statement UDFs under READ_COMMITTED_SNAPSHOT, we might not get consistent results as of the time our select began - I will provide simple repro scripts. At the time of this writing MSDN clearly states the following:...(read more)

Math with Months Is Not Commutative

Alexander Kuznetsov — Mon, 29 Nov 2010 22:58:00 GMT

In other words, if we add a month, then subtract a month, we might not get back to the date we started from. For example: SELECT DATEADD ( MONTH , 1 , DATEADD ( MONTH , - 1 , '20100330' )) , DATEADD ( MONTH , - 1 , DATEADD ( MONTH , 1 , '20100330' ))...(read more)

Benefit from Unit Testing T-SQL: Ensure quick turnaround

Alexander Kuznetsov — Thu, 04 Nov 2010 01:50:00 GMT

The biggest advantage of unit testing is the ability to make changes quickly, and with confidence that we have not broken anything with our change. Whether we need to speed up a query real quick, or to fix a bug, automated testing saves us a lot of time,...(read more)

Myth Busting: COUNT(*) under REPEATABLE READ may return wrong results

Alexander Kuznetsov — Thu, 21 Oct 2010 16:04:00 GMT

This came up on sqlservercentral.com, where I read the following claim (in a discussion thread, not in an article): " COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT ". I have a repro script which...(read more)

Uncertainty erodes confidence, so add that ORDER BY clause.

Alexander Kuznetsov — Mon, 18 Oct 2010 18:18:00 GMT

I was reading Eric Lippert's blog post , and came across the following quote: "uncertainty erodes confidence in our users that we have a quality product that was designed with deep thought, implemented with care, and behaves predictably and sensibly"....(read more)

My book is complete, many thanks to Hugo, Tony, and many others!

Alexander Kuznetsov — Fri, 18 Jun 2010 19:22:00 GMT

My book is finished. A couple years ago I googled up "Defensive Database Programming", and came up with nothing. Naturally, I started filling up the void. Although the book is complete, I am planning to continue my research; I would appreciate any other...(read more)