x

SELECT * v SELECT Columns

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

thebigboss gravatar image

thebigboss
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

2 answers: 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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+1 - 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x252
x110
x61

asked: Aug 31, 2010 at 12:28 AM

Seen: 1940 times

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