Yes and yes. It's a simple plan and there;'s no reason for it not to be cached and reused. You can check by looking in the plan cache

SELECT creation_time, last_execution_time, execution_count, dbid, objectid, text

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

The point with SELECT * is not that it's always less efficient, it's that it could result in more data been returned than necessary. It may be less efficient, depending what you're doing and what's in the table

Consider a table with 6 columns.

CREATE TABLE Test (

a int,

b varchar(10),

c datetime,

d datetime,

e varchar(500),

f smallint)

If you only want columns b and c and your use select *, you're returning, across a network, 4 columns that you don't need (between 14 and 514 bytes per row)

If there's an index on B include a and c, and you write a query

SELECT a,b,c from Test where B='Rob'. The index is covering for that query and the query can be satisfied with an index seek. No lookup to the cluster/heap is required.

Now consider SELECT * from Test where B='Rob'

An index seek can still be done, but now a lookup is required to the cluster/heap to retrieve columns d,e and f. More IOs, slower query.

Select * makes creating covering indexes near impossible to create (as all the columns in the table are required by the query)

Now, consider a modification is made to that table and column g is added, a varbinary(max) that holds an image averagng 700kb per row.

Now any query that does a SELECT * will start returning that image, even if it's not required. Since it's a LOB and stored out of row that means more IOs (lots more IOs), less efficient use of memory and a lot more data going over the network.

Worse, any query that has assumed that there are 6 columns in the table

( Like

insert into Table2 (Col1, col2, col3, Col4, Col5, Col6)

Select * From Test

)

will break when run.

Does that help?

jsheldon: The reason your second select ran faster than your first had nothing to do with the structure of the query. The first had to fetch the data from disk into cache, the second read from cache. If you're doing time tests like that, either ensure that the cache has the data before you start timing, or run DBCC DropCleanBuffers between runs to ensure an empty data cache