SELECT * not returning all columns

Question

question

Matt Whitfield asked Mar 18, '10

SELECT * not returning all columns

In my production system, a query that used to work is now failing after some recent upgrades to the schema. The statement:

SELECT * FROM [production].[item] WHERE [ItemID] = 921

is not returning all the columns. There are several sparse columns defined in the table, but they are not returned from the query, and instead a lump of XML is returned.

What could be causing this?

(seeder)

t-sql seeder-question sparse-columns

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab ♦ commented · Mar 18, 2010 at 01:34 PM

(pet peeve warning) You deserve your problems because you used the evil "SELECT *" in a production system! ;)

0 ·

Tom Staab ♦ commented · Mar 18, 2010 at 01:35 PM

And, yes, I realize it's just a seeder question, but I couldn't resist the urge to harp on the asterisk. :)

0 ·

Matt Whitfield ♦♦ commented · Mar 18, 2010 at 04:37 PM

I'm generally of the opinion that the accepted answer would *have* to include a moan about select * :)

0 ·

Answer 1 · 2010-03-18T17:34:15Z

CirqueDeSQLeil answered Mar 18, '10

Umm... laziness in not specifying the set of columns you wished to be returned. >:)

EDIT

Now adding the official answer:

When you use SELECT * to retrieve data from a table configured with a column set, only the column set and non-sparse columns are returned. However, if you specify the name of the sparse columns in your SELECT clause, the columns will be returned.

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1357672_mem1,00.html

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question