2

Could someone chime in as to whats a better practice? for select queries should I return all or the IDs that I require?

Efficiency? Scalability? etc.

thanks

Env: SQL Server 2008, VS2008 (VB)

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Duplicate of http://stackoverflow.com/questions/65512/which-is-fasterbest-select-or-select-column1-colum2-column3-etc – Nathan Koop Sep 16 '09 at 16:18
  • @Jay: Just because you can, doesn't mean you should... – OMG Ponies Sep 16 '09 at 16:20
  • there is a great answer in the dup that nathan posted, which gives reason for the 'better performance' reason cited often in answers below. – akf Sep 16 '09 at 16:23

9 Answers9

9

Always explicitly enumerate your columns. Never have select * in any production code.

Even situations where it may seemingly make sense can have unintended consequences. For example, you may think to do select * when you have a view that is supposed to mirror the layout of a table, but strange things can happen if you modify the underlying table without regenerating the view.

Stay away from select * unless you're typing the query and executing it then and there.

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343
3

Could someone chime in as to whats a better practice? for select queries should I return all or the IDs that I require?

Name your columns.

This is not only a best practice, but can gain more performance.

Imagine two queries:

SELECT  *
FROM    mytable
WHERE   column1 = @somevalue

and

SELECT  id, column1
FROM    mytable
WHERE   column1 = @somevalue

id is a clustered primary key and there is an index on column1.

I'm assuming that your client code processes the variable number of columns correctly, i. e. the table layout change does not break the code. This is a very strong assumption but let's make it.

Now, if mytable consists only of id and column1, the queries are the same.

What happens if you add a column2 to mytable?

The second query (with named columns) still uses the index (since in contains everything the query needs), but the first one needs to select column2 too (SQL Server does not know you are going to ignore it).

This will add a Clustered Table Seek into a plan and your query performance gets worse.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
3

Use select col1, col2, col3 from table instead of select * from table1. This has numerous advantages, as mentioned here and here.

Also see: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/26/60271.aspx

Is there a difference between Select * and Select [list each col]

Community
  • 1
  • 1
Bhaskar
  • 10,537
  • 6
  • 53
  • 64
2

Never listen to anyone telling you to always do something in SQL -- alternatively, always be wary of anyone telling you to never do something in SQL :)

In the following examples, SELECT * can do no harm and arguably has benefit as regards readability and code maintenance (DRY and all that):


Example 1

When the commalist of attributes has already been specified in an 'inner' scope:

SELECT * 
  FROM (
        SELECT col1, col2, col3, col4, col5
          FROM T1 
       ) AS DT1;

Example 2

When using a table value constructor in a CTE and one is compelled (e.g. in SQL Server!) to wrap the VALUES clause in a table expression (SELECT..FROM) e.g.

WITH T1
     AS
     (
      SELECT * 
        FROM (
              VALUES (1, 1, 1, 2, 1), 
                     (1, 1, 2, 1, 1), 
                     (1, 2, 1, 1, 1)
             ) AS T (col1, col2, col3, col4, col5)
     )
SELECT ...

OK so this last one is a bit of a strawman but consider that using commalists at every opportunity has caused an error and makes it difficult to debug:

WITH T2 (author_name, book_title, ISBN) 
     AS
     (
      SELECT book_title, ISBN, author_name
        FROM (
              VALUES ('9780321189561', 'C. J. Date', 'An Introduction to Database Systems')
             ) AS T (ISBN, author_name, book_title)
     )
SELECT *
  FROM T2;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
1

Always use named columns!

A good example of why it's bad: "select * from table" vs "select colA,colB,etc from table" interesting behaviour in SqlServer2005

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
1

You should specify the columns in most cases, this works best for future changes and maintenance. It also pulls less data, enhancing performance.

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
1

The reasons to prefer explicitly naming columns over SELECT * FROM our_table are

  1. Explicitly naming columns in the project expresses our intent more clearly and so contributes to self-documenting code.
  2. In the future somebody will add one or more columns to the table. If we use SELECT * these columns will be dragged in automatically, which may break our code or cause it to perform badly (especially if a LOB is involved).
APC
  • 144,005
  • 19
  • 170
  • 281
0

Unless you never refer to any of the column names in the client code, you should use named columns.

Steven Huwig
  • 20,015
  • 9
  • 55
  • 79
  • Still has performance and maintainability implications. And what if "column 3" changes because of a schema change? – gbn Sep 16 '09 at 16:18
  • The scenario I envision is iterating over all of the columns actually returned and dumping them into e.g. an HTML table with no further processing. I.e. if you need all the columns anyway, and your code won't break if one goes missing or changes data types, SELECT * is probably the way to go. – Steven Huwig Sep 16 '09 at 16:26
  • yeah, that's what I thought as well, I think this has to be a case by case practice with the lean on named fields. –  Sep 16 '09 at 16:38
  • 1
    @Jay_GISDev: are you *really* just writing a blind data dumper? Just because I came up with one edge case where it'd be OK doesn't mean it's OK anywhere else. :) – Steven Huwig Sep 16 '09 at 16:40
0

I'm not going to tell you to "never ever" or "always" use one or the other. Advices that start with that are not to be taken literal.

If you're working with small sets of data, please don't insist on using silly "optimizations" like replacing * with a list of fields, especially if you're going to specify all of the fields.

Having readable and easy to maintain SQL code is often worth more than a few saved CPU cycles or a couple of kilobytes less memory usage or network traffic.

Wouter van Nifterick
  • 23,603
  • 7
  • 78
  • 122