Don’t Use SELECT *
One of the most common performance and scalability problems are queries that return too many columns or too many rows. I’ve seen many developers actually using and abusing SELECT * FROM queries. SELECT * query not only returns unnecessary data, but it also can force clustered index scans for query plans because columns in the SELECT clause are also considered by the optimizer when it identifies indexes for execution plans.
The following two queries show this difference; note the difference in query plans as well as the relative cost to the batch. The query cost relative to the batch is 67% for the SELECT * compared to just 33% for the SELECT column query.
WHERE OrderDate< ‘01/01/1978‘ SELECT *
WHERE OrderDate< ‘01/01/1978‘
The first query uses a Clustered Index Scan to resolve the query because it has to retrieve all the data from the clustered index, even though there is an index on the OrderDate column.
The second query uses the OrderDate index to perform an Index Seek operation. Because the query returns only the SalesOrderID column, and because the column is the clustering key, the query is resolved using only that index.