Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008

Chapter 9. Advanced Queries and Scripting

Writing queries is like driving to work. There are probably 18 different ways to get there, and most likely, some routes are clearly better than others. Under different conditions, different choices may be preferable. This might depend on variable traffic conditions, the time of day, or whether you need to stop by the grocery store to pick up milk and flowers. In my case, the route I choose depends largely on the ferry schedule. If I can't make the ferry, I save time by driving a much greater distance rather than waiting for the next boat. Some opt for consistency, choosing to take the same route regardless of changing conditions, whereas others weave through traffic to find the fastest lane, and shave off a few seconds here and there.

Similarly, many queries can be written more than one way. Traditional SQL statements, written for other database products, often use subqueries, whereas SQL Server leans toward ANSI-standard join expressions. Most SQL Server professionals will tell you that if you have the option to choose between using a subquery and a join, the joins will execute faster. Generally speaking, I think this is true, but it depends on the expression and other conditions. Using joins gives the database engine more leeway to implement the best type of operations for a query, whereas subqueries may not afford SQL Server as many options.

An even more flexible option was introduced in SQL Server 2005 with common table expressions (CTEs), ...