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.

10
  • 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, 2015 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, 2015 at 20:18
  • 1
    Is your view created WITH SCHEMABINDING ? A sp_refreshview viewName will help as well.
    – Kin Shah
    Apr 15, 2015 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, 2015 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.org/2009/10/10/… Apr 15, 2015 at 21:01

1 Answer 1

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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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