SQL Server 中“WITH SCHEMABINDING”的缺点？

Downsides to "WITH SCHEMABINDING" in SQL Server?

提问by SqlRyan

I have a database with hundreds of awkwardly named tables in it (CG001T, GH066L, etc), and I have views on every one with its "friendly" name (the view "CUSTOMERS" is "SELECT * FROM GG120T", for example). I want to add "WITH SCHEMABINDING" to my views so that I can have some of the advantages associated with it, like being able to index the view, since a handful of views have computed columns that are expensive to compute on the fly.

我有一个数据库，其中包含数百个笨拙命名的表（CG001T、GH066L 等），并且我对每个表都有“友好”名称的视图（例如，视图“CUSTOMERS”是“SELECT * FROM GG120T”） . 我想在我的视图中添加“WITH SCHEMABINDING”，这样我就可以拥有一些与之相关的优势，比如能够索引视图，因为少数视图具有计算列，这些列在运行时计算成本很高。

Are there downsides to SCHEMABINDING these views?I've found some articles that vaguely allude to the downsides, but never go into them in detail. I know that once a view is schemabound, you can't alter anything that would impact the view (for example, a column datatype or collation) without first dropping the view, so that's one, but aside from that? It seems that the ability to index the view itself would far outweigh the downside of planning your schema modifications more carefully.

SCHEMABINDING 这些视图有缺点吗？我发现一些文章含糊地暗示了缺点，但从未详细介绍过。我知道，一旦视图绑定到架构，您就不能在不首先删除视图的情况下更改任何会影响视图的内容（例如，列数据类型或排序规则），所以这是一个，但除此之外？似乎索引视图本身的能力远远超过了更仔细地规划架构修改的缺点。

采纳答案by gbn

None at all. It's safer. we use it everywhere.

一个都没有。这样更安全。我们到处使用它。

回答by Dan S

You wont be able to alter/drop the table, unless you drop the view first.

您将无法更改/删除表，除非您先删除视图。

回答by user2246674

Oh, there are DEFINITELY DOWNSIDESto using SCHEMABINDING - these come from fact the SCHEMABINDING, especially when coupled with COMPUTED columns "LOCKS" THE RELATIONSHIPSand makes some "trivial changes" darn near impossible.

哦，使用 SCHEMABINDING肯定有缺点- 这些来自于 SCHEMABINDING 的事实，特别是当与计算列“锁定”关系并使一些“微不足道的变化”几乎不可能时。

1. Create a table.
2. Create a SCHEMABOUND UDF.
3. Create a COMPUTED PERSISTED column that references the UDF.
4. Add an INDEX over said column.
5. Try to update the UDF.

1. 创建一个表。
2. 创建一个 SCHEMABOUND UDF。
3. 创建一个引用 UDF 的 COMPUTED PERSISTED 列。
4. 在所述列上添加一个 INDEX。
5. 尝试更新 UDF。

Good luck with that one!

祝一切顺利！

1. The UDF can't be dropped or altered because it is SCHEMABOUND.
2. The COLUMN can't be dropped because it is used in an INDEX.
3. The COLUMN can't be altered because it is COMPUTED.

1. 不能删除或更改 UDF，因为它是 SCHEMABOUND。
2. 不能删除 COLUMN，因为它用于 INDEX。
3. COLUMN 不能更改，因为它是 COMPUTED。

Well, frak. Really..!?! My day just became a PITA. (Now, tools like ApexSQL Diff can handle this when provided with a modified schema, but the issue is here that I can't even modify the schema to begin with!)

好吧，弗拉克。真的..！？！我的一天刚刚变成了一个 PITA。（现在，像 ApexSQL Diff 这样的工具可以在提供修改后的架构时处理这个问题，但问题是我什至无法开始修改架构！）

I'm not against SCHEMABINDING, mind (and it's needed for a UDF in this case), but I'm against there not being a way (that I can find) to "temporarily disable" the SCHEMABINDING.

我不反对 SCHEMABINDING ，请注意（在这种情况下，UDF 需要它），但我反对没有一种方法（我可以找到）“暂时禁用” SCHEMABINDING。

回答by JeffO

If these tables are from a third-party app (they're notorious for trying hide their tables), you cause and upgrade to fail if it attempts to alter any of these tables.

如果这些表来自第三方应用程序（他们因试图隐藏他们的表而臭名昭著），如果它尝试更改这些表中的任何一个，则会导致升级失败。

You just have to alter the views without the schemabinding before the update/upgrade and then put them back. Like others have mentioned. Just takes some planning, discipline, etc.

您只需要在更新/升级之前更改没有架构绑定的视图，然后将它们放回去。就像其他人提到的那样。只需要一些计划，纪律等。

回答by Triynko

One downside is that if you schemabind a view, it can only reference other schemabound views.

一个缺点是，如果您架构绑定一个视图，它只能引用其他架构绑定视图。

I know this because I tried to schemabind a view and was met with an error message telling me it could not be schemabound because one of the other views it references is not also schemabound.

我知道这是因为我试图对视图进行架构绑定，但遇到了一条错误消息，告诉我它不能被架构绑定，因为它引用的其他视图之一也不是架构绑定的。

The only consequence of this is that if you suddenly want to update a schemabound view to reference some new or existing view, you might have to schemabind that new or existing view as well. In that case, you won't be able to update the view, and you better hope your database developers know how to work with schemabound views.

这样做的唯一后果是，如果您突然想要更新架构绑定视图以引用某个新视图或现有视图，您可能还必须架构绑定该新视图或现有视图。在这种情况下，您将无法更新视图，您最好希望您的数据库开发人员知道如何使用模式绑定视图。

回答by JonnyRaa

Another downside is that you need to use schema qualified names for everything: You'll get a load of error messages like this:

另一个缺点是您需要对所有内容使用模式限定名称：您将收到大量错误消息，如下所示：

Cannot schema bind view 'view' because name 'table' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

无法模式绑定视图“视图”，因为名称“表”对于模式绑定无效。名称必须采用两部分格式，并且对象不能引用自身。

Also to 'switch off' schemabinding you do alter view which requires you to redefine the view's select statement. I think the only thing you dont have to redefine is any grants. This puts me off a lot as overwriting the view seems like an inherently unsafe operation.

同样要“关闭”架构绑定，您需要更改视图，这需要您重新定义视图的选择语句。我认为您唯一不必重新定义的是任何赠款。这让我很失望，因为覆盖视图似乎是一种本质上不安全的操作。

Its a bit like the way adding not null constraints forces you to overwrite the column's data type - nasty!

它有点像添加非空约束迫使您覆盖列的数据类型的方式 - 讨厌！

You'll also have to redefine any other views or procedures that depend on the schema bound object you want to change... this means you may have to redefine (and possibly break) a large cascade of functions and views just to add (eg) a not null constraint to one column.

您还必须重新定义依赖于您想要更改的模式绑定对象的任何其他视图或过程...... ) 对一列的非空约束。

Personally I think this doesnt really represent a solution and its better to have a decent process whereby any database changes are applied automatically so it isnt a nightmare to change the database. That way you can have all your views + functions dropped and recreated from scratch (they get checked on creation anyway) as part of the process when you apply changes to tables.

我个人认为这并不是真正的解决方案，最好有一个体面的过程，即自动应用任何数据库更改，因此更改数据库不是一场噩梦。这样，当您将更改应用于表时，您可以将所有视图 + 函数从头开始删除并重新创建（无论如何它们都会在创建时进行检查）作为过程的一部分。

回答by ZagNut

this seems like a downside to me (#'s are mine):

这对我来说似乎是一个缺点（# 是我的）：

Cannot create index on view "###.dbo.###" because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.

I kinda need my LEFT joins. This SO questionis relevant.

我有点需要我的左连接。 这个SO问题是相关的。

回答by Marcelo Barbieri

When using tSQLt Unit Test Framework you will come across issues and will need workarounds when using FakeTable method, which won't allow you to fake a table that is linked to a view with schemabinding.

使用 tSQLt 单元测试框架时，您会遇到问题，并且在使用 FakeTable 方法时需要解决方法，该方法不允许您伪造链接到具有模式绑定的视图的表。

回答by thetempest

The negatives mentioned hardly outweigh this best practice since SQL Svr 2005. It avoids the dreaded table spooling. A major negative for me is that schema bound sprocs, funcs, views, can't include "foreign" databases such as the master db, so you can throw all the great realtime system stuff in the trash unless, for example, your production core database sits inside master. For me, I can't deal with life without the sys stuff. Of course not all processing requires spool-free performance and fast and slow results can be combined simultaneously in higher data class layers.

自 SQL Svr 2005 以来，提到的负面影响几乎没有超过这个最佳实践。它避免了可怕的表假脱机。对我来说一个主要的负面影响是模式绑定的 sprocs、funcs、视图不能包含“外部”数据库，例如主数据库，因此您可以将所有伟大的实时系统内容扔进垃圾桶，除非，例如，您的生产核心数据库位于 master 内部。对我来说，没有系统的东西我就无法应付生活。当然，并非所有处理都需要无假脱机的性能，并且可以在更高的数据类层中同时组合快速和慢速结果。

回答by Adriaan Davel

If your tool (ssms etc.) does not handle schema change failures on the base object well / elegantly you could cause yourself some real chaos. That's what I'm sitting with now, and I do realise that this is a fringe case

如果您的工具（ssms 等）不能很好地/优雅地处理基础对象上的架构更改失败，您可能会给自己造成一些真正的混乱。这就是我现在的情况，我确实意识到这是一个边缘案例