Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 277

SELECT H.SalesOrderId, D.SalesOrderDetailId
FROM Sales.SalesOrderHeader H
INNER JOIN Sales.SalesOrderDetail D ON H.SalesOrderId = D.SalesOrderId
WHERE h.SalesOrderId = 43659

SELECT *
FROM Sales.SalesOrderHeader H
INNER JOIN Sales.SalesOrderDetail D
ON H.SalesOrderId = D.SalesOrderId
WHERE h.SalesOrderId = 43659

The first query selects the most efficient columns that can be made for these two tables. The output of this
select is not very useful, but provides a comparison for the best case, least amount of columns you can
select. The second statement grabs all the columns out of both tables and represents the most data you
can pull from these two tables. Table 9-12 compares the two select statements.

Table 9-12: Comparison of Specific and All-Column Selects

Method % Cost CPU Reads Duration

With WHERE Clause...

Specific SELECT 50 0 28 198ms

SELECT * 50 80 30 345ms

Without WHERE Clause

Specific SELECT 50 271 295 3960ms

SELECT * 50 2263 2510 19582ms

It is easy to see that the number of reads and the Merge Join Operation that occurs in the SELECT *
method creates the most expensive plan. In fact, the expense increases for each additional column, as can
be seen in Figure 9-9.

Figure 9-9