sql-server-2008 – 编写SQL查询的最佳方法,该查询检查列是否为

我有一个SP,其参数具有NULL作为默认值,然后我想进行如下查询：

SELECT ...
FROM ...
WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND a.VersionId IS NULL));

上面的WHERE检查@VersionId的非NULL值和NULL值.

在性能方面是否更好地使用IF语句并将查询复制到一个搜索非NULL而另一个搜索NULL的问题呢？ ：

IF @VersionId IS NULL BEGIN
    SELECT ...
    FROM ...
    WHERE a.Blah = @Blah AND a.VersionId IS NULL;
ELSE BEGIN
    SELECT ...
    FROM ...
    WHERE a.Blah = @Blah AND a.VersionId = @VersionId;
END

或者查询优化器使它基本相同？

更新：

(注意：我使用的是SQL Server)

(据我所知,对于这两种情况使用a.VersionId = @VersionId都行不通,是吗？)

解决方法

column = @argument OR (@argument IS NULL AND column IS NULL)

可以替换为

EXISTS (SELECT column INTERSECT SELECT @argument)

这将允许您将NULL与NULL匹配,并允许引擎有效地使用列上的索引.有关此技术的深入分析,请参阅Paul White的博客文章：

> Undocumented Query Plans: Equality Comparisons

由于在您的特定情况下有两个参数,您可以使用与@Blah相同的匹配技术 – 这样您就可以或多或少地简洁地重写整个WHERE子句：

WHERE
  EXISTS (SELECT a.Blah,a.VersionId INTERSECT SELECT @Blah,@VersionId)

这将使用索引(a.Blah,a.VersionId)快速工作.

Or the query optimizer makes it essentially the same?

在这种情况下,是的.在从SQL Server 2005开始的所有版本(至少)中,优化器可以识别模式col = @var OR(@var IS NULL和col IS NULL)并将其替换为正确的IS比较.这确实依赖于内部重写匹配,因此可能存在更复杂的情况,这并不总是可靠的.

在SQL Server from 2008 SP1 CU5 inclusive的版本中,您还可以选择通过OPTION(RECOMPILE)使用Parameter Embedding Optimization,其中任何参数或变量的运行时值在编译之前作为文字嵌入查询中.

因此,至少在很大程度上,在这种情况下,选择是一种风格问题,尽管INTERSECT结构无疑是紧凑和优雅的.

以下示例显示了每个变体的“相同”执行计划(文字与变量引用除外)：

DECLARE @T AS table
(
    c1 integer NULL,c2 integer NULL,c3 integer NULL

    UNIQUE CLUSTERED (c1,c2)
);

-- Some data
INSERT @T
    (c1,c2,c3)
SELECT 1,1,1 UNION ALL
SELECT 2,2,2 UNION ALL
SELECT NULL,NULL,NULL UNION ALL
SELECT 3,3,3;

-- Filtering conditions
DECLARE 
    @c1 integer,@c2 integer;

SELECT
    @c1 = NULL,@c2 = NULL;

-- Writing the NULL-handling out explicitly
SELECT * 
FROM @T AS T
WHERE 
(
    T.c1 = @c1
    OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND 
(
    T.c2 = @c2
    OR (@c2 IS NULL AND T.c2 IS NULL)
);

-- Using INTERSECT
SELECT * 
FROM @T AS T
WHERE EXISTS 
(
    SELECT T.c1,T.c2 
    INTERSECT 
    SELECT @c1,@c2
);

-- Using separate queries
IF @c1 IS NULL AND @c2 IS NULL
    SELECT * 
    FROM @T AS T
    WHERE T.c1 IS NULL
    AND T.c2 IS NULL
ELSE IF @c1 IS NULL
    SELECT * 
    FROM @T AS T
    WHERE T.c1 IS NULL
    AND T.c2 = @c2
ELSE IF @c2 IS NULL
    SELECT * 
    FROM @T AS T
    WHERE T.c1 = @c1
    AND T.c2 IS NULL
ELSE
    SELECT * 
    FROM @T AS T
    WHERE T.c1 = @c1
    AND T.c2 = @c2;

-- Using OPTION (RECOMPILE)
-- Requires 2008 SP1 CU5 or later
SELECT * 
FROM @T AS T
WHERE 
(
    T.c1 = @c1
    OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND 
(
    T.c2 = @c2
    OR (@c2 IS NULL AND T.c2 IS NULL)
)
OPTION (RECOMPILE);

（编辑：南京站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!