在使用视图的查询中使用WITH NOLOCK表提示-它是否在视图中传播? [英] Using WITH NOLOCK Table Hint in Query Using View - Does it Propagate Within the View?
问题描述
如果在SQL Server中的视图上使用了"WITH NOLOCK"查询提示，即使没有将NOLOCK用于视图定义中的原始表，它是否也会将该提示传播到视图定义本身?之所以需要这样做，是因为有时支持人员想要执行大量耗时的查询，而宁愿不使用应用程序内部的视图对所有查询强制使用此锁定.
If a "WITH NOLOCK" query hint is used on a View in SQL Server, does it propagate that hint to the view definition itself, even if NOLOCK is NOT used for the raw tables in the View definition? The reason to need this is that sometimes the support staff wants to do huge time-consuming queries but would rather not force this lock on all queries using the view within the application itself.
推荐答案
是的，NOLOCK将传播到视图定义所使用的表中(至少在SQL Server 2005中如此).
Yes, NOLOCK will propagate to the tables used by the view definition (at least in SQL Server 2005).
请参见MSDN中的表提示:
在SQL Server 2005中，所有锁定提示都传播到视图中引用的所有表和视图.另外，SQL Server执行相应的锁一致性检查.
In SQL Server 2005, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks.
但是，
如果表包含计算列，并且计算列由访问其他表中列的表达式或函数计算，则这些表上不使用表提示.这意味着表提示不会传播.例如，在查询的表上指定了NOLOCK表提示.该表具有计算的列，这些列是通过访问另一个表中的列的表达式和函数的组合来计算的.表达式和函数引用的表在访问时不使用NOLOCK表提示.
If a table contains computed columns and the computed columns are computed by expressions or functions accessing columns in other tables, the table hints are not used on those tables. This means the table hints are not propagated. For example, a NOLOCK table hint is specified on a table in the query. This table has computed columns that are computed by a combination of expressions and functions that access columns in another table. The tables referenced by the expressions and functions do not use the NOLOCK table hint when accessed.
如果您正在使用索引视图，则可能还需要阅读更多内容，因为那里也有一些特殊情况.
If you're using indexed views you might want to read a bit more as there are some special cases there too.
另请参见查看分辨率以了解更多信息
Also see View Resolution for more info.
这篇关于在使用视图的查询中使用WITH NOLOCK表提示-它是否在视图中传播?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
