March 2009 - Posts

• We need an OLAP function for string concatenation

  String concatenation in SQL is frequently discussed on newsgroups and blogs. Adam Machanic recently posted a competition in string concatenation , which was followed by Paul Nielsen's post There were several suggestions on Connect to implement it, such Read More... Posted Sunday, March 29, 2009 1:27 PM by Alexander Kuznetsov | 0 Comments Filed under: Transact SQL, String concatenation

• Reproducing one more intermittent deadlock on only one table

  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... Posted Thursday, March 26, 2009 11:46 AM by Alexander Kuznetsov | 2 Comments Filed under: SQL Server, SQL Deadlocks, Deadlocks

• Connect items to vote for

  IMO Connect is difficult to search and navigate. So I think it would be nice to have a periodical digest of Connect items to vote for. Of course, Aaron Bertrand is the expert on Connect, and I do hope that Aaron will eventually take it over, but currently Read More... Posted Sunday, March 22, 2009 6:25 PM by Alexander Kuznetsov | 1 Comments

• Defensive database programming: fun with ROWCOUNT

  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... Posted Saturday, March 21, 2009 10:59 PM by Alexander Kuznetsov | 8 Comments Filed under: SQL Server, Database Programming, Transact SQL, Defensive programming

• Set based calculation of products of several numbers.

  You can use SUM to calculate the sum of several numbers, but you cannot directly use set-based logic to calculate a product. Yet there is a very simple trick – you can use EXP(SUM(LOG(…))) and get a product of several numbers without a loop. The trick Read More... Posted Tuesday, March 17, 2009 6:01 PM by Alexander Kuznetsov | 10 Comments Filed under: SQL, Transact SQL, Brainteasers, Set based logic

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

  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... Posted Wednesday, March 11, 2009 11:05 PM by Alexander Kuznetsov | 31 Comments Filed under: SQL Server, Transact SQL, Deadlock

• Summarizing previous posts about defensive database programming

  I have been posting examples of defensive database programming for some time now. I am by no means done with this topic, there is much more to it. Yet this time I would like to skip concrete examples and write up a long overdue introduction. Defensive Read More... Posted Sunday, March 08, 2009 9:33 PM by Alexander Kuznetsov | 5 Comments Filed under: SQL Server, Database Programming, Transact SQL, Defensive programming

• Storing intervals of time with no overlaps.

  I will demonstrate how use constraints to make sure that intervals of time have no overlaps. Also you may ensure that there are no gaps either, but if you choose to allow gaps, then the query to retrieve them is very easy. As discussed in my previous Read More... Posted Sunday, March 08, 2009 8:12 PM by Alexander Kuznetsov | 17 Comments Filed under: Database Programming, Transact SQL, Data Integrity