数据库 VIEW 不反映底层 TABLE 中的数据 [英] Database VIEW does not reflect the data in the underying TABLE
问题描述
客户声称应用程序 (.NET) 在查询某些数据时返回的数据与客户直接查看数据表时返回的数据不同.
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.
我知道可能有各种原因，并且发生在完全不同的地方.我的目的不是在这里解决它，而是询问有经验的 DBA 和 DB 开发人员是否:
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:
- 造成这种情况的可能原因/原因是什么?
- 视图上的 UPDATE 语句能否导致未来的 SELECT 返回更新后的"数据，而表确实没有?
- 原因是有两个独立的交易，这可以解释客户的困惑.
- 基础表已更改，但视图未刷新(使用 sp_refreshview)
- 另一个用户正在连接并且由于权限而可以看到不同的数据?
- 程序员错误:错误的表/列、错误的过滤器(此处多合一)
- 发生损坏:DBCC CHECKDB 应该有所帮助
- 可以
SELECT ... FOR UPDATE导致这个??? - ?__
到底发生了什么(答案):
某些表中的列位置发生了变化:显然，客户向顾问提供了完整的数据库访问权限，以进行数据库使用情况分析.当使用 SELECT * ... 子句时，那个伟大的家伙改变了列的顺序以查看表开头的几个 audit 字段.
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.
使用 dbGhost 将数据库架构与所采取的备份架构进行比较问题出现前几天，发现了列位置差异.
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.
因此 sp_refreshview 是解决方案.我只是多走了一步，找到了问题的根源.谢谢大家.
Therefore the sp_refreshview was the solution. I just took one step more to find who caused the problem. Thank you all.
推荐答案
是的，有点.
可能的原因:
需要刷新或重新编译视图.当源列定义发生变化并且视图(或它所依赖的东西)使用*"时会发生这种情况，这可能很糟糕.调用 sp_RefreshView.也可能因为它调用的视图或函数(数据源)而发生.
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.
视图正在查看与他们/您的想法不同的东西.他们看错了表格或视图.
The View is looking at something different from what they/you think. They are looking at the wrong table or view.
视图正在以一种意想不到的方式转换数据.它工作正常，只是不像他们预期的那样.
The View is transforming the data in an unexpected way. It works right, just not like they expected.
视图返回的数据子集与预期不同.同样，它工作正常，只是不像他们想的那样.
The View is returning a different subset of the data than expected. Again, it works right, just not like they think.
他们正在查看错误的数据库/服务器或登录/用户身份导致视图更改其显示的内容.特别邪恶，因为与 Management Studio 不同，大多数客户端程序不会告诉您它们指向的是哪个数据库/服务器.
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.
这篇关于数据库 VIEW 不反映底层 TABLE 中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
