Alexander Kuznetsov : SQL Server

Five Years of Writing about SQL Server

Alexander Kuznetsov — Tue, 19 Oct 2010 19:30:00 GMT

Five years ago I published my first article about SQL Server, "Index Covering Boosts SQL Server Query Performance" It is still quite popular - I just googled up "index covering", and it comes right on top. Bing does the same thing. It was just mentioned...(read more)

Book Review: Oracle Database Administration for Microsoft SQL Server DBAs

Alexander Kuznetsov — Mon, 27 Sep 2010 18:26:00 GMT

Working in mixed database environments is a very interesting challenge, both for DBAs and developers. If we already have experience with SQL Server, and need to work with Oracle, our SQL Server experience may be an advantage - we already have a good handle...(read more)

Additional links for my presentation on defensive database programming.

Alexander Kuznetsov — Sun, 19 Sep 2010 18:29:00 GMT

Yesterday I delivered a session named "Develop T-SQL defensively" at East Iowa SQL Saturday. Thanks to those who attended, and many thanks to Michelle Ufford, Chris Leonard, Ed Leighton-Dick, and other volunteers, who organized the event. Here are the...(read more)

Repro scripts and more info for my presentation on concurrency

Alexander Kuznetsov — Sun, 19 Sep 2010 18:10:00 GMT

Yesterday I delivered a session named "Developing T-SQL to survive concurrency" at East Iowa SQL Saturday. Thanks to everyone who attended, and thanks to Michelle Ufford, Chris Leonard, Ed Leighton-Dick, and other volunteers, who organized the event....(read more)

Using CROSS APPLY to optimize joins on BETWEEN conditions

Alexander Kuznetsov — Wed, 08 Jul 2009 00:47:00 GMT

Recently I encountered a case when I knew much more about the data than the optimizer. Originally the performance was horrible, this is why I had to have a look at the query in the first place. When I was able to share my knowledge with the optimizer,...(read more)

Without ORDER BY, there is no default sort order.

Alexander Kuznetsov — Wed, 20 May 2009 13:31:00 GMT

Sounds trivial? Right, but different flavors of this myth still persist. Yesterday I noticed a thread on stackoverflow discussing SELECT TOP without a where clause, where it was suggested that "The order will be defined based on the clustered key in that...(read more)

Your TRY block may fail, and your CATCH block may be bypassed.

Alexander Kuznetsov — Thu, 14 May 2009 01:11:00 GMT

Some T-SQL code is written under the assumption that either a TRY block successfully completes or a CATCH block is invoked. Most likely, this is the case. However, there is a third, although rare, possibility – the TRY block may fail, and the CATCH one...(read more)

Two suggestions to enhance constraints in SQL Server

Alexander Kuznetsov — Tue, 05 May 2009 03:29:00 GMT

We can tuck any logical expressions in CHECK constraints, but all foreign keys are currently capable of right now is to verify that one or more column values are equal. In many cases it would be very useful to have foreign keys use more complex logical...(read more)

Using ROWVERSION to enforce business rules

Alexander Kuznetsov — Wed, 29 Apr 2009 02:46:00 GMT

Suppose that you need to enforce the following business rule: contracts cannot be changed after you have started working on them (let us assume that that particular business operates in the perfect world). You can use a ROWVERSION column, a persisted...(read more)

When you process your rows one by one, avoid infinite loops

Alexander Kuznetsov — Fri, 24 Apr 2009 02:57:00 GMT

When you process your rows one by one in a loop, you need to make sure that your loop never runs infinitely. I will describe three scenarios with possible infinite loops. There have been many articles discussing feasibility of loops vs. set-based solutions...(read more)

Avoiding infinite loops. Part One.

Alexander Kuznetsov — Tue, 21 Apr 2009 03:11:00 GMT

Although there are many discussions about which kind of cursor or loop performs the best, there is no doubt which loops perform the worst – the infinite ones of course. Whenever you write a loop, you need to make sure that it never runs infinitely. I...(read more)

Selects under READ COMMITTED and REPEATABLE READ may return incorrect results.

Alexander Kuznetsov — Sat, 11 Apr 2009 00:34:00 GMT

Selects under READ COMMITTED may return incorrect results if the data they select is being modified at the same time. I will provide a repro script which on my laptop returns incorrect results in more than 90% cases. Also I will provide a similar repro...(read more)

Reproducing one more intermittent deadlock on only one table

Alexander Kuznetsov — Thu, 26 Mar 2009 15:46:00 GMT

I have already described several deadlock scenarios that involve only one table in another post. This time I will describe an intermittent deadlock which occurs in high concurrency environments. Before providing a complex repro script for it, I will provide...(read more)

Defensive database programming: fun with ROWCOUNT

Alexander Kuznetsov — Sun, 22 Mar 2009 02:59:00 GMT

I have written up two examples when a SET ROWCOUNT command breaks a seemingly working stored procedure or trigger. Note that currently the best practice is to use TOP clause instead of SET ROWCOUNT, which is deprecated in SQL Server 2008. However, even...(read more)

Some heap tables may be more prone to deadlocks than identical tables with clustered indexes

Alexander Kuznetsov — Thu, 12 Mar 2009 03:05:00 GMT

Apparently for high isolation levels some heap tables may be more prone to deadlocks than identical tables with clustered indexes. I have a simple repro script which successfully completes if a table has a clustered index but embraces in a deadlock if...(read more)