May 2008 - Posts

• Unit Testing Tips: Reproduce a Lock Timeout

  Well, I have just finished a series of articles about unit testing on simple-talk.com, and some techniques were not included, just to keep the articles short. Still problems such as reproducing a lock timeout and unit testing the retry after it (lock Read More... Posted Friday, May 30, 2008 6:24 PM by Alexander Kuznetsov | 0 Comments Filed under: SQL Server, Database Unit Testing, Database Testing

• Reuse Your Code with Table-Valued UDFs

  Suppose you are using SQL Server 2005, and you want to truncate your DATETIME value, leaving only date part of it, as follows: DATEADD ( day , DATEDIFF ( day , '19010101' , LastModifiedDate ), '19010101' ) Suppose you want to reuse this code and wrap Read More... Posted Friday, May 23, 2008 1:26 PM by Alexander Kuznetsov | 8 Comments

• A Great Advice on Coding Predicates in Outer Joins

  Yet another very common error is when someone puts a wrong condition in a WHERE clause and that essentially renders an outer join into an inner join. A great advice on coding predicates in outer joins comes from a DB2 expert Terry Purcell. "Meet the experts: Read More... Posted Thursday, May 15, 2008 1:19 PM by Alexander Kuznetsov | 0 Comments

• Be Aware of These Loopholes in Your Referential Integrity

  I am going to discuss several potential problems with referential integrity, because they keep coming up in the newsgroups. Parent and Child Tables Are in Different Databases. Although you cannot use a foreign key in this situation, there are workarounds Read More... Posted Tuesday, May 13, 2008 6:17 PM by Alexander Kuznetsov | 1 Comments

• When Index Covering Prevents Deadlocks

  Suppose that a select has just navigated all the way down a non-clustered index and is trying to lookup a row in the clustered index. Suppose that at the same time an update has just modified that row and tries to modify the corresponding entry in the Read More... Posted Saturday, May 03, 2008 11:08 PM by Alexander Kuznetsov | 4 Comments