May 2009 - Posts

• Without ORDER BY, there is no default sort order.

  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... Posted Wednesday, May 20, 2009 9:31 AM by Alexander Kuznetsov | 19 Comments Filed under: SQL Server, Transact SQL, SQLL Myths

• Court in New Jersey ordered code review of software used for alcohol breath tests

  Court in New Jersey ordered code review of software used for alcohol breath tests. Sounds like an interesting precedent to me. Read More... Posted Saturday, May 16, 2009 7:15 PM by Alexander Kuznetsov | 1 Comments

• Are values generated by NewId() distributed evenly?

  I ran a quick check, and it looks like a reasonably even distribution to me. I genenerated 2M NewId values, as follows: SELECT NewID() AS RandomColumn INTO #t FROM Data.TableWith2Mrows I looked at the distribution into 256 buckets, considering the first Read More... Posted Thursday, May 14, 2009 1:56 PM by Alexander Kuznetsov | 3 Comments Filed under: Database Programming, Transact SQL, NewId

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

  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... Posted Wednesday, May 13, 2009 9:11 PM by Alexander Kuznetsov | 10 Comments Filed under: SQL Server, Database Programming, Transact SQL, Defensive programming

• Defensive database programming: fun with triggers.

  There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there are three all-too-common kinds of problems related to triggers: · Triggers can fail to work as expected · Triggers can be bypassed · Triggers can Read More... Posted Monday, May 11, 2009 9:08 PM by Alexander Kuznetsov | 4 Comments

• Two suggestions to enhance constraints in SQL Server

  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... Posted Monday, May 04, 2009 11:29 PM by Alexander Kuznetsov | 4 Comments Filed under: SQL Server, Transact SQL, Data Integrity, Business rules