Using * in a query and the query optimiser | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Using * in a query and the query optimiser

I’ve been working on some queries and have found that when queries have the shamefull * operator in them to retrieve all columns instead of the favoured column list, the estimated execution plan that the query optimiser puts out is different. eg :
Q1 : select * from myTable and Q2 : select col1, col2, col3 from myTable Now, there are 219 rows in the table but the query optimiser shows an estimated rowcount of 1 for the first query and the correct estimated rowcount of 219 for the second query, the cpu and io cost also favour the first query but this must be because it’s assuming it returns 1 row. I updated statistics and tried this again but to no avail.
I’m running winXP professional, SqlServer 2k Standard edition (on my developer machine). There may be some explanation on the site but I haven’t come accross it, any suggestions or pearls of wisdom would be appreciated. Cheers
Shaun World Domination Through Superior Software
On the T-Sql (tips) page this refers
In your queries, don’t return column data you don’t need. For example, you should not use SELECT * to return all the columns from a table if you don’t need all the data from each column. In addition, using SELECT * prevents the use of covered indexes, further potentially hurting query performance. [6.5, 7.0, 2000] Updated 2-8-2002
…
 _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
My tests showed same rowcount for the different queries. Any chance you set rowcount 1 on the connection from where you run select * and forgot? try set rowcount 0 and execute query again… Bambola.
Thanks Satya, but I still don’t understand why the rowcount is different as there is only 1 clustered index on the table and both queries result in a clustered index seek so although the query can’t benefit from covered indexes it wouldn’t need to in this case anyway. Bambola, I tried setting rowcount to 0 but still got the same result with the one query only displaying a rowcount of 1 in the query optimiser page. I’m a bit stumped, I reckon I’ll try it on some other machines against other databases just to make sure my machine and database aren’t messed up! Thanks
Shaun World Domination Through Superior Software
]]>