Mistake: using SELECT * as the default projection in released, maintainable code. It is useful for ad hoc inspection when you need to discover a table's columns or answer a quick data question, but production queries should name the columns they require. section 5.3
The sales-order analysis query
An application needs SalesOrderDate, SalesOrderDeliveryDueDate, and SalesOrderDeliveryActualDate. Selecting only those columns sends 9 bytes per row; SELECT * can send up to 61 bytes. Across 2.5 million orders, the unnecessary transfer can reach 121 MB. Wide NVARCHAR(MAX) columns and concurrent users can make the cost much worse. section 5.3
Avoid SELECT * in maintained code because it:
- Transfers columns the application does not need, increasing network and application-tier work. section 5.3
- Can prevent a focused covering index from matching the required projection efficiently. section 5.3
- Makes dependencies less readable and can create maintenance problems when table definitions change. section 5.9
Listing 5.6 Creating a covering index
CREATE NONCLUSTERED INDEX [OrderDate-Including-DueDate-ActualDate] ON dbo.SalesOrderHeaders (SalesOrderDate) INCLUDE(SalesOrderDeliveryDueDate,SalesOrderDeliveryActualDate) ;
A covering index for the sales-order analysis query places SalesOrderDate in the key and includes the two delivery-date columns. section 5.3
