添加字段以某种方式影响视图结果 [英] adding a field somehow effects a views results

问题描述

有一个非常奇怪的情况.由于各种原因，我们有一堆表格是这样做的:

Have a very odd situation. For various reasons we have a bunch of tables where this was done:

TableA 被重命名为 TableASource 并创建了一个名为 TableA 的视图TableB 被重命名为 TableBSource 并创建了一个名为 TableB 的视图

TableA was renamed to TableASource and a view was created called TableA TableB was renamed to TableBSource and a view was created called TableB

这一切都很好，并且已经有一段时间了.昨天我向 TableASource、TableBSource(以及其他)添加了一个新字段.这是一个名为createDate"的字段，一个 smallDateTime，没有空值，默认值为 getDate().你会认为这应该对任何事情产生零影响.今天，用户说的第一件事是他们可以看到这些日期而不是预期的数据.EG Lest 说我们有一个页面从名为 TableA 的视图中提取 10 个字段，而不是显示这 10 个字段，它现在正确显示 9 个字段并随机显示添加到 TableASource 的新 createDate 字段中的值(如果重要，则将其放置在页面的第二个字段中)

This all works fine and has done for a fair while. Yesterday I added a new field to TableASource, TableBSource (and the others). This was a field called 'createDate' a smallDateTime, no nulls with a default value of getDate(). You would think this should have zero impact on anything. Today first thing users are saying they can see these dates inplace of the intended data. E.G Lest says we had a page extracting 10 fields out of the view called TableA, instead of showing these 10 fields its now showing 9 correctly and randomly showing the value in the new createDate field that was added to TableASource (If it matters it placing it in the second field on the page)

我很快删除了所有这些新的 createDate 字段，问题就消失了.这怎么可能发生?向 TableASource 添加字段如何影响 TableA 视图的结果?

I quickly dropped all these new createDate fields and the issue went away. How can this possibly happen? How can adding a field to TableASource effect the results of the TableA view?

使用 SQLServer2008 r2

Using SQLServer2008 r2

回答下面的额外问题.这是名为bayTrainCourses"的视图，上面已将其描述为 TableA:

in response the the extra questions below. Here is the view called 'bayTrainCourses' which has been described above as TableA :

SELECT  btc.course_id, btc.course_name, btc.course_status, btc.course_expiry, btc.course_startdate, btc.course_enddate, btc.course_type, btc.site_id, btc.channelid, btc.exam_type, btc.enroll_type, btc.last_updated, btc.compid, btc.TOC, btc.rdone, btc.autoqualify, btc.courseIntroId, btc.coursePurposeId, btc.courseBackgroundId,btc.courseObjectivesId, intro.    [content] AS course_intro, purpose.[content] AS course_purpose, background.[content] AS course_background, 
objectives.[content] AS course_objectives
FROM dbo.bayTrainCoursesSource AS btc 
LEFT OUTER JOIN dbo.RTE AS intro ON btc.courseIntroId = intro.pk 
LEFT OUTER JOIN dbo.RTE AS purpose ON btc.coursePurposeId = purpose.pk 
LEFT OUTER JOIN dbo.RTE AS background ON btc.courseBackgroundId = background.pk 
LEFT OUTER JOIN dbo.RTE AS objectives ON btc.courseObjectivesId = objectives.pk

选择是:

select * from baytraincourses....

编辑 - 由于一些试验错误.事实证明，这次审判实际上是下面约翰斯的建议.重新创建视图.... 我将视图重命名为 baytraincourses_broken.然后右键单击> 脚本视图为> 创建到> 然后我将"baytraincourses_broken"更改为"baytraincourses"以使用原始名称重新创建另一个版本.现在页面可以工作了.

EDITING - due to some trial an error. It turns out that the trial was actually the advice from JohnS below. Recreate the view.... I renamed the view to baytraincourses_broken. Then right click > script view as > create to > then I changed 'baytraincourses_broken' to 'baytraincourses' to recreate another version with the original name. Now the page works.

我不是观点的忠实粉丝.说实话，我创建它们是非常罕见的，我正在使用我最初没有编写的代码.每次添加新列时，我真的必须重新创建视图吗?肯定不是，我该如何处理?

I am not a big fan of views. Its very rare I create them to be honest, I am working with code that I did not originally write. Do I really have to re-create views every time I add a new column? Surely not, how can I deal with this?

推荐答案

将新字段添加到 TableASource 后，您需要重新创建视图.(即 DROP VIEW TableA ... 然后 CREATE VIEW TableA ...).

After adding the new field to TableASource you need to recreate the view. (i.e. DROP VIEW TableA ... then CREATE VIEW TableA ...).

这篇关于添加字段以某种方式影响视图结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！