Select * from table OR select id,field1, field2, field3 from table - best practice?

Question

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)

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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

http://stackoverflow.com/questions/208925/is-there-a-difference-betweeen-select-and-select-list-each-col

Answer 7

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).

Answer 8

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.