July 2009 - Posts

• When you add an index and your query blows up...

  You cannot assume that the conditions in your WHERE clause will evaluate in the left-to-write order - making such assumptions leads to unsafe queries. For example, the following query is not safe: SELECT * FROM dbo.Messages WHERE ISDATE ( VarcharColumn Read More... Posted Thursday, July 16, 2009 5:39 PM by Alexander Kuznetsov | 3 Comments

• Saving the whole team of players or nothing

  Suppose that you need to store teams of players, and you must enforce the following business rule: each team must consist of exactly two players. I will demonstrate how you can use constraints to implement this rule. I haven't used this approach in production Read More... Posted Wednesday, July 15, 2009 2:28 PM by Alexander Kuznetsov | 0 Comments Filed under: Business rules, constraints, Transact SQL

• Optimizing yet another query that involves highly correlated columns

  In some cases some of the columns involved in a query are highly correlated. If you manage to communicate to the optimizer that valuable information, it may come up with a more efficient plan. For example, consider the following table (the script that Read More... Posted Monday, July 13, 2009 6:15 PM by Alexander Kuznetsov | 2 Comments Filed under: query performance, Transact SQL

• Using CROSS APPLY to optimize joins on BETWEEN conditions

  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... Posted Tuesday, July 07, 2009 8:47 PM by Alexander Kuznetsov | 19 Comments Filed under: query performance, SQL Server

• When CHECK constraints using UDFs fail for multirow updates

  I have made up a simple example when a CHECK constraint that uses a UDF should succeed but always fails. Suppose you need to move some stuff in your car, and you don't want to load more than 100 pounds into your small car at a time. Here is the DDL: CREATE Read More... Posted Wednesday, July 01, 2009 10:08 AM by Alexander Kuznetsov | 1 Comments