Input:
The customer claims that the application (.NET) when querying for some data returns data different from when the customer looks into the data table directly.
I understand there could be various reasons and in completely different places. My purpose is not to solve it here, but rather to ask experienced DBAs and DB developers if:
Is it possible for a VIEW to show data that does not match the underlying TABLE(s)?
- What are possible causes/reasons for this?
- Can an UPDATE statement on a view cause future SELECTs to return 'updated' data, when the table really does not?
Possible causes (please comment on those with question-marks):
- the reason is that there are two separate transactions, which would explain the customers' confusion.
- the underlying table was altered, but the view was not refreshed (using sp_refreshview)
- a different user is connecting and can see different data due to permissions ?
- programmer error: wrong tables/columns, wrong filters (all-in-one here)
- corruption occurs: DBCC CHECKDB should help
- can
SELECT ... FOR UPDATE
cause this ??? - ? __
What really happened (THE ANSWER):
Column positions were altered in some tables: Apparently the customer gave full database access to a consultant for database usage analysis. That great guy changed the order of the columns to see the few audit fields at the beginning of the table when using SELECT * ...
clauses.
Using dbGhost the database schema was compared to the schema of the backup taken few days before the problem appeared, and the column position differences were discovered.
What came next was nothing related to programming, but more an issue of politics.
Therefore the sp_refreshview
was the solution. I just took one step more to find who caused the problem. Thank you all.