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

Question

I'm using MS SQL Server 2005. Is there a difference, to the SQL engine, between

SELECT * FROM MyTable;

and

SELECT ColA, ColB, ColC FROM MyTable;

When ColA, ColB, and ColC represent every column in the table?

If they are the same, is there a reason why you should use the 2nd one anyway? I have a project that's heavy on LINQ, and I'm not sure if the standard SELECT * it generates is a bad practice, or if I should always be a .Select() on it to specify which cols I want.

EDIT: Changed "When ColA, ColB, and ColC are all the columns to the table?" to "When ColA, ColB, and ColC represent every column in the table?" for clarity.

Answer 1

Generally, it's better to be explicit, so Select col1, col2 from Table is better. The reason being that at some point, an extra column may be added to that table, and would cause unneeded data to be brought back from the query.

This isn't a hard and fast rule though.

Answer 2

1) The second one is more explicit about which columns are returned. The value of the 2nd one then is how much you value explicitly knowing which columns come back.

2) This involves potentially less data being returned when there are more columns than the ones explicitly used as well.

3) If you change the table by adding a new column, the first query changes and the second does not. If you have code like "for all columns returned do ..." then the results change if you use the first, but not the 2nd.

Answer 3

I'm going to get a lot of people upset with me, but especially if I'm adding columns later on, I usually like to use the SELECT * FROM table. I've been called lazy for this reason, because if I make any modifications to my tables, I'd like not to track down all the stored procs that use that table, and just change it in the data access layer classes in my application. There are cases in which I will specify the columns, but in the case where I'm trying to get a complete "object" from the database, I'd rather just use the "*". And, yes, I know people will be hating me for this, but it has allowed me to be quicker and less bug free while adding fields to my applications.

Answer 4

The two sides of the issue are this: Explicit column specification gives better performance as new columns are added, but * specification requires no maintenance as new columns are added.

Which to use depends on what kind of columns you expect to add to the table, and what the point of the query is.

If you are using your table as a backing store for an object (which seems likely in the LINQ-to-SQL case), you probably want any new columns added to this table to be included in your object, and vice-versa. You're maintaining them in parallel. For this reason, for this case, * specification in the SELECT clause is right. Explicit specification would give you an extra bit of maintenance every time something changed, and a bug if you didn't update the field list correctly.

If the query is going to return a lot of records, you are probably better off with explicit specification for performance reasons.

If both things are true, consider having two different queries.