We ran into a curious case today where a view that's doing "select * from table" had a couple of datetime fields come back as integers. This was working previously up until very recently. These is nothing in the view itself that is forcing these columns to return as integers.
Another curious thing is that rewriting the view to explicitly select all of the columns makes the data come back as datetimes. I'm thinking maybe it's some sort of bad cache plan, but I'm unsure.
What could the culprit be of the data pulling back incorrectly?
Best Answer
This has happened because the definition of the table has changed since the view was last modified. Maybe someone altered one or more columns, or the table was dropped and re-created with columns in a different order, different types, etc.
Temporary fix:
Permanent fix:
Stop using
SELECT *
in views (and maybe even consider always usingWITH SCHEMABINDING
for all views).I blogged about the
SELECT *
symptom here.SCHEMABINDING
here, and they include being unable to alter a table when a view is schema-bound to it.