question

Matt Whitfield avatar image
Matt Whitfield asked

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-sqlseeder-questionsparse-columns
3 comments
10 |1200

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

(pet peeve warning) You deserve your problems because you used the evil "SELECT *" in a production system! ;)
0 Likes 0 ·
And, yes, I realize it's just a seeder question, but I couldn't resist the urge to harp on the asterisk. :)
0 Likes 0 ·
I'm generally of the opinion that the accepted answer would *have* to include a moan about select * :)
0 Likes 0 ·

1 Answer

·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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