Professional SQL Server® 2005 Performance Tuning

9.7. Tuning T-SQL Common Patterns or Idioms

If you've ever felt like you've seen that performance problem before, you've probably tapped into your mental storage of the pattern and the resulting solution. Some T-SQL performance issues are recurring issues, and identifying them quickly can greatly reduce your troubleshooting work load. This section examines performance issues with some common patterns that are used in T-SQL to solve development problems.

9.7.1. Singleton SELECT Statements

This pattern involves selecting one and only one row from a table. The term singleton refers to a set with only one element—in the database world this is one row. Typically, there are two approaches to generating a singleton-type result. One option is to use the TOP statement to lift the top one (1) row based on a predicate and sort order. A second option is use a derived table to find the one clustered index value matching the applied predicate using an aggregate function. To tune these types of queries, look at the results you need from the operation. If you are returning a set of columns that can't be covered by indexes, then the column values must be looked up from the base pages. It is more efficient to look up only those rows that meet one condition rather than look up all the rows to determine the top row. Conversely, if all you need is one row with the column that is in an index, either way will perform about the same.

The TOP approach is most commonly used, because it is the most idiomatic ...