The VIEW database does not reflect data in a table under the heading

Question

Input:

The client claims that the application (.NET) returns some data when it requests some data, other than when the client looks directly at the data table.

I understand that there can be different reasons in completely different places. My goal is not to solve this problem here, but to ask experienced database and database developers if:

Is it possible for VIEW to display data that does not match the underlying TABLES?

• What are the possible reasons / reasons for this?
• Can an UPDATE statement in a view force future SELECTs to return “updated” data when the table really does not work?

Possible reasons (comment on those who have question marks):

• the reason is that there are two separate transactions that explain customer confusion.
• base table has been changed but view has not been updated (using sp_refreshview)
• another user connects and can see different data due to permissions?
• programmer error: wrong tables / columns, wrong filters (all-in-one here)
• damage occurs: DBCC CHECKDB should help
• can SELECT ... FOR UPDATE call this
• ? __

What really happened (ANSWER):

The column positions have been changed in some tables: obviously, the client gave full access to the database for the consultant to analyze the use of the database. This great guy reordered the columns to see several audit fields at the beginning of the table when using SELECT * ... clauses.

Using dbGhost , the database schema was compared to the backup schema that was made a few days before the problem appeared, and differences in the column position were detected.

What happened next is not related to programming, but rather a matter of politics.

Therefore, the sp_refreshview solution was a solution. I just took one more step to find who caused the problem. Thanks to everyone.

Answer 1

Yes, sort of.

Possible reasons:

• Viewing needs to be updated or recompiled. It happens that the original column definitions change, and the view (or something on which it depends) uses "*", can be unpleasant. Call sp_RefreshView. It can also happen due to the views or functions (data sources) that it invokes too.

• The view is looking at something different from what they / you think. They are looking at the wrong table or view.

• The view transforms the data in an unexpected way. It works correctly, just not as expected.

• The view returns a different subset of the data than expected. Again, this works correctly, just not the way they think.

• They are looking at the wrong database or with a login / user id that forces the view to change what it shows.

Answer 2

it is possible if the base table was changed and sp_refreshview was not launched against the view, so the columns will not be present in the view if they were added to the table.

To find out what I mean, read how to make sure that the view will have changes to the base table using sp_refreshview

Answer 3

You can create views with lock hints, which means you can get a dirty read. Or, alternatively, when they directly access the table, they can use blocking hints that could give them a dirty read at this point.

Another possibility that users do not seem to understand is that the data is fluid. The data that you read at 3:00 in the view may not coincide with the data that you see at 3:30, looking directly at the table, because at the same time there were changes.

Answer 4

A few possibilities:

• Your .NET application may not point to where you or they think it points. For example, he pointed to a test server by mistake

• If the view has an index for a floating-point number or a numeric value, the value may differ from the base query due to rounding

• The ANSI_NULLS parameter is specific to the view when it was created. If it differs from the setting during selection (s) in the base tables, this may lead to discrepancies in certain types of queries

• Base table structures have changed and the view has not been updated (especially a problem if it uses "SELECT *")

I will edit this post if I think about others.

EDIT: Here is an example of how the ANSI_NULLS parameter can reset your results:

 SET ANSI_NULLS ON DECLARE @i INT, @j INT SET @i = NULL SET @j = 1 SELECT CASE WHEN @i <> @j THEN 'Not Equal' ELSE 'Equal' END SET ANSI_NULLS OFF SELECT CASE WHEN @i <> @j THEN 'Not Equal' ELSE 'Equal' END 

The results you should get:

 Equal Not Equal 

Answer 5

Assuming that the view does not actually convert the data, it is technically possible if damage occurs. A view retrieves data from one index, a "table" retrieves from another (that is, from a clustered one), and two of them are not synchronized. DBCC CHECKDB should identify the problem.

But human error is much more likely, i.e. they look at a different table than a view, or at different records.

Answer 6

Of course, there are other things:

 1) Derived attributes are pulling from wrong tables in the view 2) The view is using incorrect tables 3) incorrect or missing joins in the view 

to name a few