CODEWITHSUNDEEP
sqlsql-serverschema
van
van

Reputation: 77032

Database VIEW does not reflect the data in the underying TABLE

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)?

Possible causes (please comment on those with question-marks):

  1. the reason is that there are two separate transactions, which would explain the customers' confusion.
  2. the underlying table was altered, but the view was not refreshed (using sp_refreshview)
  3. a different user is connecting and can see different data due to permissions ?
  4. programmer error: wrong tables/columns, wrong filters (all-in-one here)
  5. corruption occurs: DBCC CHECKDB should help
  6. can SELECT ... FOR UPDATE cause this ???
  7. ? __

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.

Upvotes: 15

Views: 44323

Answers (7)

Faheem
Faheem

Reputation: 13

Was having same issue at our Production Environment, a guy changed few tables' structures, and the views were built as SELECT * FROM the base Tables. All the reports were impacted using those views as views were not returning any results.

executing sp_refreshview for those views fixed the issue.

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56755

Yes, sort of.

Possible Causes:

  1. The View needs to be refreshed or recompiled. Happens when source column definitions change and the View (or something it depends on) is using "*", can be nasty. Call sp_RefreshView. Can also happen because of views or functions (data sources) that it calls as well.

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

  3. The View is transforming the data in an unexpected way. It works right, just not like they expected.

  4. The View is returning a different subset of the data than expected. Again, it works right, just not like they think.

  5. They are looking at the wrong database/server or with a Logon/user identity that causes the View to alter what it shows. Particularly nefarious because unlike Management Studio, most client programs do not tell you what database/server they are pointed at.

Upvotes: 27

Tom H
Tom H

Reputation: 47392

A few possibilities:

  • Your .NET application may not be pointing to where you or they think it is pointing. For example, it's pointed to a test server by mistake

  • If the view has an index on a float or numeric value, the value may appear different from the underlying query due to rounding

  • The ANSI_NULLS setting is specific to the view when it was created. If it's different from the setting during the select(s) on the underlying tables it could cause discrepancies for certain kinds of queries

  • The underlying table structures have changed and the view hasn't been refreshed (especially a problem if it uses "SELECT *")

I'll edit this post if I think of any others.

EDIT: Here's an example of how the ANSI_NULLS setting can throw off 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 which you should receive are:

Equal

Not Equal

Upvotes: 3

HLGEM
HLGEM

Reputation: 96600

You can create views with locking hints which would mean you might be getting a dirty read. Or alternatively when they access the table directly, they might be using locking hints which could be giving them a dirty read at that point.

Another possibility that users don't seem to understand is that the data is fluid. The data you read at 3:00 in a view may not be the same data that you see at 3:30 looking directly at the table becasue there have been changes in the meantime.

Upvotes: 4

Remus Rusanu
Remus Rusanu

Reputation: 294407

Assuming the view does not actually transform the data, technically it is possible if a corruption occurs. View retrieves data from one index, 'table' retrieves from another (ie. from clustered) and the two are out of sync. A DBCC CHECKDB should reveal the problem.

But human error is much more likely, ie. they are looking at different table than the view, or at different records.

Upvotes: 1

northpole
northpole

Reputation: 10346

For sure 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

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135121

it is possible if the underlying table has been changed and sp_refreshview has not been ran against the view, so the view will have missing columns if those were added to the table.

To see what I mean read how to make sure that the view will have the underlying table changes by using sp_refreshview

Upvotes: 12

Related Questions