O'Reilly logo

Beginning Transact-SQL with SQL Server 2000 and 2005 by Dan Wood, Paul Turley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

10

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, while 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.

Depending on the query, the same results can be achieved using either technique. Graphical design tools, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required