On Mon, 17 Sep 2007 21:25:30 +0100, Danny Burton wrote:

>When returning all columns from a table is there any performance gain (or
>any other reason) for using SELECT col1, col2 etc... instead of just SELECT
>* ?

Hi Clive,

I have to contradict Erland here - there will be a small performance
hit, that might (in very rare cases) grow to be a annoying enough to be
noticeable.

For SELECT *, the first step in parsing is to find out which columns are
in the table. That requires a read on the syscolumns system table in SQL
Server 2000, or it's undocumented equivalent in SQL Server 2005. That in
itself means that you'll have a small bit of overhead, due to requesting
a lock, getting it, reading the data, and releasing the lock again.

If this happens frequently in a system that also frequently adds or
removes columns to of from tables, you might see blocking on the system
table, since the Sch-S (schema stability) lock taken for reading the
columns can't co-exist with the Sch-M (schema modification) lock taken
for changing the table structure.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis