Another reason not to use \'SELECT * FROM…..\'

Another reason not to use \’SELECT * FROM…..\’

OK, OK, this one has been done to death, but for me one of the great things about SQL Server is that you keep finding out new stuff for yourself.

Actually, I\’m not sure that it isn\’t a case that I\’ve forgotten that I knew this. I\’m certain that I don\’t remember remembering it. Seems I\’m having one of those \’Inception\’ days. Again.

Basically, SELECT * FROM combines the results from all tables that are used in a JOIN , not just the ones from the left table.

I\’ll show you….

Consider the following innocuous query:

SELECT * FROM sys.indexes a
        INNER JOIN sys.data_spaces b
                ON a.data_space_id = b.data_space_id
        WHERE b.type = \’PS\’

You\’d think that you\’d get everything from sys.indexes that fulfils the JOIN ,but you don’t. (BTW, the PS in WHERE b.type = \’PS\’ stands for partition scheme).

sys.indexes has 18 columns. I won\’t list them here, but you could by doing this:

SELECT name FROM sys.all_columns
        WHERE object_id = (OBJECT_ID(\’sys.indexes\’)) 

sys.data_spaces has 5 columns. I won\’t list them here…. Do I really have to do this again ?  🙂

The first query has 23 columns, being the first 18 from sys.indexes and an additional 5 from sys.data_spaces .

So, in a nutshell, SELECT * FROM returns data you probably didn\’t actually want, yet you went and consumed all that precious I/O anyway. Just a thought, but if you\’ve got several wide tables in a JOIN and you do a SELECT * FROM …… ? I don\’t want to be around when THAT goes south !!

Back Soon….