Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Don't Use (select *), but List Columns

From Wiki

Jump to: navigation, search

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


  1. SET STATISTICS IO ON
  2. GO
  3. SELECT pub_name,city FROM dbo.publishers

Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.


  1. SET STATISTICS IO ON
  2. GO
  3. 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

113 Rating: 3.3/5 (39 votes cast)