February 10, 2006 at 11:25 pm
Hi Everybody,
I have small doubt could you please solve this.
1) select * from tablename
2) select col1,col2,col3 from tablename
Considering that i need to use all the columns from my table and without any
indexing on my table or any condition to filter the records ,which of these two queries are faster.
I am assuming that select * is much faster then selecting individual columns
Kindly let me know on this
Regards
Suresh
February 11, 2006 at 4:58 am
In terms of performance I don't think there will be any difference.
I prefer to specify the columns because if the underlying table changes its structure my query is still bringing back the columns I specified in the order I specified them.
If you have a clustered index on your table you may get a boost in SQL2000 by adding a WHERE clause to search between the minimum and maximum possible value that the index can contain
SELECT *
FROM dbo.YourTable
WHERE ID BETWEEN 0 AND 2147483647
This give any benefits on SQL2005.
February 13, 2006 at 5:11 pm
David, did you intend for your last statement to be "This doesn't give any benefits on SQL2005"? If so, why not?
Also, selecting col1, col2, and col3 from the table could provide a benefit if the three columns are a subset of all columns in the table. This is especially true if there is a covering index on col1,2, and 3.
Well - my statement above is based on SQL 2000. I'm just started 2005.
Ryan
February 14, 2006 at 10:27 am
Yes I did mean "This doesn't".
In both SQL 2000 and SQL 2005 the statement forces an index seek however in SQL2005 a "Filter" step is added into the execution plan which increases the cost.
SQL2005 costs, from my experiments are about 1/2 those of SQL2000 so only technique only benefits SQL2000.
If you have a covering index on Col1...3 and all you want to select is Col1...3 then this should eliminate a costly Bookmark Lookup as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply