为什么我可以用SCHEMABINDING修改视图引用的表? [英] Why can I modify a table that is referenced by a view with SCHEMABINDING?

问题描述

正在寻找关于SCHEMABINDING的一些说明.我在一个论坛中读到，如果您使用Schemabinding创建视图，则不能更改基表.

Looking for some clarification on SCHEMABINDING. I read in a forum that if you create a view with Schemabinding, the base table cannot be altered.

因此，我使用SCHEMABINDING创建了一个视图，并能够从基表中删除一列.

So I created a view with SCHEMABINDING and was able to drop a column from the base table.

因此，现在我对SCHEMABINDING的目的以及何时何地使用SCHEMABINDING感到困惑.

So, now I am confused on what is the purpose of SCHEMABINDING and when and where should this be used?

注意:由于SCHEMABINDING，我试图删除基表，但无法这样做.

NOTE: I tried to drop the base table and I was not able to do so because of SCHEMABINDING.

推荐答案

SCHEMABINDING不仅限于对象级绑定.在这种情况下，实际上可能只绑定到特定的列.举一个非常简单的例子:

SCHEMABINDING is not restricted to just object-level bindings. In this case, it is actually possible to only be bound to specific columns. Take this very trivial example:

CREATE TABLE dbo.foo(a INT, b INT);
GO

CREATE VIEW dbo.vFoo 
WITH SCHEMABINDING 
AS
  SELECT a FROM dbo.foo;
GO

由于该视图仅引用列a，因此我可以放下b没问题:

Since the view only references column a, I can drop b no problem:

ALTER TABLE dbo.foo DROP COLUMN b;
GO

但是，一旦我尝试触摸a，就会出现问题:

But as soon as I try to touch a, problem:

ALTER TABLE dbo.foo DROP COLUMN a;
GO

错误:

MSG 5074，第16级，状态1，第17行
对象'vFoo'依赖于列'a'.
Msg 4922，第16级，状态9，第17行
ALTER TABLE DROP COLUMN失败，因为一个或多个对象访问此列.

Msg 5074, Level 16, State 1, Line 17
The object 'vFoo' is dependent on column 'a'.
Msg 4922, Level 16, State 9, Line 17
ALTER TABLE DROP COLUMN a failed because one or more objects access this column.

因此，无论谁写了他们在该论坛上写的东西，他们都不是很明确或详尽. 文档会有所帮助，但是您仍然需要推断:

So, whoever wrote what they wrote in that forum, they weren't very explicit or elaborate. The documentation is a little more helpful, but you still need to extrapolate:

...不能以会影响视图定义的方式修改一个或多个基本表.

... the base table or tables cannot be modified in a way that would affect the view definition.

请注意，该句子在单词修饰"之后并未结束.删除视图中未引用的列不会以影响视图定义的方式修改表，因为它不知道未引用的列.

Notice the sentence does not end after the word "modified." Dropping a column that isn't referenced in the view is not modifying the table in a way that would affect the view definition, because it does not know about a column it doesn't reference.

这篇关于为什么我可以用SCHEMABINDING修改视图引用的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！