2

I have a view that selects data from a table that is showing different data.

The table is dbo.user, the view is dbo.users (don't judge, I inherited this system). The view is simply select * from table.

If I run the following statements:

SELECT Field1, Field2 FROM dbo.[User] WITH (NOLOCK) ORDER BY Field1;

SELECT Field1, Field2 FROM dbo.Users WITH (NOLOCK) ORDER BY Field1;

I get different values for one of the fields. See the attached pic:

enter image description here

Any ideas how this happens? The table is clustered, has a pk and unique index.

Improve this question
  • 6
    Alter your view to select the explicit column names instead of * and let us know if that fixes the problem. Then never SELECT * again haha – Avarkx Apr 15 '15 at 20:13
  • Yes, altering the view fixes it, even if i don't change the definition and leave the select * in there. My point is that this shouldn't happen, and this isn't the only view in the world like this. – sqlpadawan Apr 15 '15 at 20:18
  • 1
    Is your view created WITH SCHEMABINDING ? A sp_refreshview viewName will help as well. – Kin Shah Apr 15 '15 at 20:27
  • There is no schemabinding on the view, and thanks, i know a sp_refreshview will also work, but I'm curious how the data in the view can be different from the data in the table. That would lead me to believe that sql is caching the view results somewhere. But I've never heard of views acting that way. – sqlpadawan Apr 15 '15 at 20:30
  • 3
    SQL isn't caching the data from the view, but it is caching the metadata about the underlying table. This is why your views should never use SELECT *. See sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/… – Aaron Bertrand Apr 15 '15 at 21:01
7

When a view is created it is bound to the metadata it needs in order to execute.

Note: https://msdn.microsoft.com/en-us/library/ms187821.aspx

That says that sp_refreshview: "Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends."

The outdated metadata binding causes the surprising "wrong" results. Using schema-bound views is one way of avoiding the problem.

Read the link that Aaron Bertrand posted:

Improve this answer
  • you have a minor typo in sy_refreshview, but the change is too small to be an edit :) – Tom V Apr 16 '15 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.