Sql-server – SQL Server table and view show different values

sql servert-sqlview

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.

Best Answer

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: