While retrieving data using select statement which way is faster either with * or with column names and why?

more ▼

asked Aug 31, 2010 at 12:28 AM in Default

avatar image

11 1 1 1

Plz let me know the answer

Aug 31, 2010 at 12:29 AM thebigboss
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you are only selecting all columns from a table, using select * and select [columnnames].... will be the same.

It is generally a better idea to select the column names explicitly. Should a table receive an extra column it would be loaded with a select * call, where the extra column is not needed.

This can have several implications:

  1. More network traffic

  2. More I/O (got to read more data from disk)

  3. Possibly even more I/O (a covering index cannot be used - a table scan is performed to get the data)

  4. Possibly even more CPU (a covering index cannot be used so data needs sorting)

P.S. Please don't pressure for an answer (especially only one minute after asking). People answer as an when they can on this volunteer website.

more ▼

answered Aug 31, 2010 at 12:43 AM

avatar image

26.2k 18 37 48

  • I would agree with all of these and add that explicitly naming the columns you need can give a benefit from the server using a cached plan if it recognises the query as having been run before. Using * does not guarantee the order that the columns are expanded so you can end up with different plans for effectively the same query.

Aug 31, 2010 at 01:56 AM Fatherjack ♦♦

@Fatherjack - did you +1 me on this, as i cannot see that being accounted to me anywhere? (hope voting is not broken)

Aug 31, 2010 at 02:26 AM WilliamD

G'ah!. Sorry. typed the words and then failed to follow up and hit the Thumbs-Up. Done now, voting is fine I am sure. ;)

Aug 31, 2010 at 02:40 AM Fatherjack ♦♦

Ok then. I wasn't sure, because there are still some bugs in OSQA and I recall a short discussion about votes going uncounted.

Aug 31, 2010 at 02:49 AM WilliamD

SSMS Tip to list all of the columns of a table without having to type them: Open up the Object Explorer payne and find the table, click + to see "Columns" and then drag the "Columns" to the Query pane. All of the column names will be listed for you, seperated by a comma. (This is easier done than said by the way.)

Sep 01, 2010 at 07:54 AM Mark
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 31, 2010 at 12:28 AM

Seen: 2380 times

Last Updated: Sep 01, 2010 at 08:26 AM

Copyright 2018 Redgate Software. Privacy Policy