Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Don't Use (select *), but List Columns
From Wiki
Don’t use * but list the columns
Do SELECT pub_name,city FROM dbo.publishers instead of SELECT * FROM dbo.publishers
If you would have a covering index on the columns pub_name and city then the table would not be accessed at all and all the data would be returned from the index. This would also reduce the logical reads. You can use STATISTICS IO to find out how many logical reads you would have
- SET STATISTICS IO ON
- GO
- SELECT pub_name,city FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
- SET STATISTICS IO ON
- GO
- SELECT * FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.
Contributed by: --SQLDenis 15:26, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks - 100+ List
Section Query Optimization
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.