Posted by Serge Rielau on 09/19/07 21:45

Erland Sommarskog wrote:
> Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
>> 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.
>
> But if you list all columns in the table, the optimizer still has to
> read all rows in sys.columns for the table verify that all columns
> exists, to find their data types etc. So the amount of locking would be
> the same.
Uhmm.. I don't think I'd want to confuse the official information schema
with what the DBMS uses for itself. You assume there is neither
compacting of the decomposed information into one object (like a LOB)
nor caching of the schema in memory.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Back to original message]