使用连词执行查询时的 SQL 行为

发布于 2024-11-07 18:08:35 字数 551 浏览 0 评论 0原文

如果我有一个对三个表执行存在检查的 SQL 查询：

IF EXISTS(SELECT [KEY] FROM [Table1] WHERE [KEY]='Key1')
    AND EXISTS(SELECT [KEY] FROM [Table2] WHERE [KEY]='Key2')
    AND EXISTS(SELECT [KEY] FROM [Table3] WHERE [KEY]='Key3')

SQL Server 是否支持条件语句的“提前退出”，这样如果针对 [Table1] 的初始存在检查返回 false，则不会执行其余两个存在检查？
假设 Microsoft SQL Server 作为后端，我希望在三个引用的表上看到什么锁定行为，再次假设针对 Table1 的初始存在检查将返回 false？

使用函数而不是实际查询进行的一些基本测试表明支持“提前退出”，但查询执行期间的锁分析还表明在所有三个表上都获取了锁，这与“提前退出”的结果相矛盾。

SQL Server 是否会获取查询中所有表的锁，以防以后需要它们？

原文

If I have a SQL query that is performing an existence check against three tables:

IF EXISTS(SELECT [KEY] FROM [Table1] WHERE [KEY]='Key1')
    AND EXISTS(SELECT [KEY] FROM [Table2] WHERE [KEY]='Key2')
    AND EXISTS(SELECT [KEY] FROM [Table3] WHERE [KEY]='Key3')

Does SQL Server support 'early exit' for conditional statements, such that if the initial exists check against [Table1] returns false, the remaining two exists checks are not executed?
Assuming Microsoft SQL Server as the backend, what locking behaviour would I expect to see on the three referenced tables, again assuming that the initial exists check against Table1 will return false?

Some basic testing using functions instead of actual queries would suggest that 'early exit' is supported, but lock analysis during query execution also suggests that a lock is acquired on all three tables which contradicts the 'early exit' findings.

Does SQL Server acquire a lock on all tables in a query, just in case it needs them later on?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

魂牵梦绕锁你心扉 2024-11-14 18:08:36

SQL Server 不会短路。执行查询时必须评估语句的所有部分并获取所有相关的锁。

回复收藏 0 原文

自在安然 2024-11-14 18:08:35

SQL Server确实会进行短路计算，但您无法控制它选择计算子句的顺序，除非您通过 CASE 语句来执行此操作。

回复收藏 0 原文

够运 2024-11-14 18:08:35

编辑：SQL Server 中显然存在一个错误，该错误有时会导致短路。请参阅 RedFilter 答案的评论。 ~~如果您想要短路，请使用嵌套的 IF。~~

一般来说，SQL 是声明性的，而不是过程性的，因此您永远不能假设任何表达式或查询将按照其编写的顺序进行计算。编辑：除了 CASE ...

IF EXISTS(SELECT [KEY] FROM [Table1] WHERE [KEY]='Key1')
BEGIN
    IF EXISTS(SELECT [KEY] FROM [Table2] WHERE [KEY]='Key2')
    BEGIN
        IF EXISTS(SELECT [KEY] FROM [Table3] WHERE [KEY]='Key3')
        BEGIN

这也会改变锁的应用方式：现在您将为每个查询拥有单独的锁，而不是在第一个 AND 表达式的持续时间内为所有三个表提供锁

Edit: there is a bug in SQL Server apparently that kiboshes short circuiting occasionally. See comments on RedFilter's answer. ~~If you want short circuiting, use nested IFs.~~

Generally SQL is declarative, not procedural, so you can never assume any expression or query will be evaluated in the order its written. Edit: except for CASE...

IF EXISTS(SELECT [KEY] FROM [Table1] WHERE [KEY]='Key1')
BEGIN
    IF EXISTS(SELECT [KEY] FROM [Table2] WHERE [KEY]='Key2')
    BEGIN
        IF EXISTS(SELECT [KEY] FROM [Table3] WHERE [KEY]='Key3')
        BEGIN

This would also change how locks are applied: now you'll have separate locks for each query instead of locks for all three tables for the duration of the first AND expression

回复收藏 0 原文

~没有更多了~