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
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
- SELECT pub_name,city FROM dbo.publishers
Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'Products'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.
Contributed by: --SQLDenis 15:26, 31 May 2008 (GMT)
Section Query Optimization