SQL 数据库 VIEW 不反映底层 TABLE 中的数据

Database VIEW does not reflect the data in the underying TABLE

提问by van

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.

客户声称应用程序 (.NET) 在查询某些数据时返回的数据与客户直接查看数据表时返回的数据不同。

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:

我知道可能有各种原因并且在完全不同的地方。我的目的不是在这里解决它，而是询问有经验的 DBA 和 DB 开发人员是否：

Is it possible for a VIEW to show data that does not match the underlying TABLE(s)?

VIEW 是否可以显示与基础 TABLE 不匹配的数据？

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

• 可能的原因/原因是什么？
• 当表真的没有时，视图上的 UPDATE 语句是否会导致未来的 SELECT 返回“更新”的数据？

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 UPDATEcause this ???
7. ? __

1. 原因是有两个独立的交易，这可以解释客户的困惑。
2. 基础表已更改，但视图未刷新（使用 sp_refreshview）
3. 不同的用户正在连接并且由于权限可以看到不同的数据？
4. 程序员错误：错误的表/列，错误的过滤器（此处多合一）
5. 发生损坏：DBCC CHECKDB 应该有帮助
6. 会SELECT ... FOR UPDATE导致这个？？？
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 auditfields at the beginning of the table when using SELECT * ...clauses.

某些表中的列位置发生了变化：显然，客户向顾问提供了完整的数据库访问权限，以进行数据库使用情况分析。那个大佬改变了列的顺序，在使用子句时看到了表开头的几个审计字段SELECT * ...。

Using dbGhostthe database schema was compared to the schema of the backup taken few days before the problem appeared, and the column position differences were discovered.

使用dbGhost将数据库架构与问题出现前几天的备份架构进行比较，并发现列位置差异。

What came next was nothing related to programming, but more an issue of politics.

接下来的事情与编程无关，更多的是问题。

Therefore the sp_refreshviewwas the solution. I just took one step more to find who caused the problem. Thank you all.

因此，这sp_refreshview是解决方案。我只是多走了一步，找到了问题的根源。谢谢你们。

回答by RBarryYoung

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 becuase of views or functions (data sources) that it calls too.

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 or with a Logon/user identity that causes the View to alter what it shows.

1. 视图需要刷新或重新编译。当源列定义更改并且视图（或它所依赖的东西）使用“*”时会发生这种情况，这可能会令人讨厌。调用 sp_RefreshView。也可能因为它调用的视图或函数（数据源）而发生。

2. 视图正在查看与他们/您的想法不同的东西。他们正在查看错误的表或视图。

3. 视图正在以一种意想不到的方式转换数据。它工作正常，只是不像他们预期的那样。

4. 视图返回的数据子集与预期不同。同样，它工作正常，只是不像他们想的那样。

5. 他们正在查看错误的数据库或登录/用户身份导致视图更改其显示的内容。

回答by SQLMenace

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.

如果基础表已更改并且 sp_refreshview 尚未针对该视图运行，则该视图可能会丢失列，如果这些列已添加到表中。

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

要了解我的意思，请阅读如何使用 sp_refreshview 确保视图更改基础表

回答by HLGEM

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.

用户似乎不理解的另一种可能性是数据是流动的。您在视图中 3:00 读取的数据可能与您在 3:30 直接查看表时看到的数据不同，因为在此期间发生了更改。

回答by Tom H

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 *")

• 您的 .NET 应用程序可能没有指向您或他们认为它指向的位置。例如，它被错误地指向了一个测试服务器

• 如果视图在浮点数或数值上有索引，由于四舍五入，该值可能与底层查询不同

• ANSI_NULLS 设置特定于创建时的视图。如果它与基础表上的选择期间的设置不同，则可能会导致某些类型的查询出现差异

• 底层表结构已更改，视图未刷新（尤其是使用“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:

编辑：这是 ANSI_NULLS 设置如何摆脱您的结果的示例：

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

回答by Remus Rusanu

Assuming the view does not actually transformthe 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.

假设视图实际上没有转换数据，从技术上讲，如果发生损坏是可能的。视图从一个索引中检索数据，“表”从另一个索引（即从集群中）检索数据，两者不同步。DBCC CHECKDB 应该揭示问题。

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

但人为错误的可能性更大，即。他们正在查看与视图不同的表，或者不同的记录。

回答by northpole

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

仅举几例