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

前端之家收集整理的这篇文章主要介绍了sql-server-2008 – 编写SQL查询的最佳方法,该查询检查列是否为非NULL值或NULL前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个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);
原文链接:https://www.f2er.com/mssql/80178.html

猜你在找的MsSQL相关文章