Datetime fields return as integers in view

Question

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?

Answer 1

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:

  EXEC sys.sp_refreshview N'dbo.viewname';
  

• Permanent fix:

  • Stop using SELECT * in views (and maybe even consider always using WITH SCHEMABINDING for all views).

  • I blogged about the SELECT * symptom here.

  • I blogged about the benefits of SCHEMABINDING here, and they include being unable to alter a table when a view is schema-bound to it.