Would that plan be cached? and be reusable?
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 (
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
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
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass