在仅使用文字值的 WHERE 子句中替换 ISNULL() 有哪些不同的方法？

答案部分

有多种方法可以使用不同的 T-SQL 结构来重写它。我们将在下面查看优缺点并进行总体比较。

首先：使用OR

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18
OR u.Age IS NULL;

UsingOR为我们提供了一个更有效的 Seek 计划，它读取我们需要的确切行数，但是它将技术世界调用的内容添加a whole mess of malarkey到查询计划中。

另请注意，此处执行了两次 Seek，这从图形运算符中应该更明显：

Table 'Users'. Scan count 2, logical reads 8233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 469 ms,  elapsed time = 473 ms.

其次：使用派生表和UNION ALL 我们的查询也可以这样重写

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

这产生了相同类型的计划，更少的恶意，以及更明显的关于索引被搜索（搜索？）多少次的诚实程度。

它执行与OR查询相同数量的读取 (8233)，但减少了大约 100 毫秒的 CPU 时间。

CPU time = 313 ms,  elapsed time = 315 ms.

但是，您必须非常小心，因为如果此计划尝试并行，则两个单独的COUNT操作将被序列化，因为它们都被视为全局标量聚合。如果我们使用跟踪标志 8649 强制执行并行计划，问题就会变得很明显。

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

这可以通过稍微改变我们的查询来避免。

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

现在执行 Seek 的两个节点都是完全并行的，直到我们点击连接运算符。

对于它的价值，完全并行的版本有一些好处。以大约 100 次读取和大约 90 毫秒的额外 CPU 时间为代价，经过的时间缩减到 93 毫秒。

Table 'Users'. Scan count 12, logical reads 8317, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 500 ms,  elapsed time = 93 ms.

交叉申请呢？ 没有魔法的答案是不完整的CROSS APPLY！

不幸的是，我们遇到了更多的问题COUNT。

SELECT SUM(Records)
FROM dbo.Users AS u 
CROSS APPLY 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

这个计划太可怕了。当你最后一次出现在圣帕特里克节时，这就是你最终的计划。虽然很好地并行，但出于某种原因，它正在扫描 PK/CX。嗯。该计划的成本为 2198 美元。

Table 'Users'. Scan count 7, logical reads 31676233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 29532 ms,  elapsed time = 5828 ms.

这是一个奇怪的选择，因为如果我们强制它使用非聚集索引，成本会显着下降到 1798 美元。

SELECT SUM(Records)
FROM dbo.Users AS u 
CROSS APPLY 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

嘿，寻找！在那边检查你。还要注意，有了 的魔力CROSS APPLY，我们不需要做任何愚蠢的事情来拥有一个几乎完全平行的计划。

Table 'Users'. Scan count 5277838, logical reads 31685303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 27625 ms,  elapsed time = 4909 ms.

COUNT如果没有这些东西，Cross apply 最终会表现得更好。

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY 
(
    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

该计划看起来不错，但读取和 CPU 并没有改进。

Table 'Users'. Scan count 20, logical reads 17564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4844 ms,  elapsed time = 863 ms.

将交叉应用重写为派生连接会导致完全相同的所有内容。我不会重新发布查询计划和统计信息——它们真的没有改变。

SELECT COUNT(u.Id)
FROM dbo.Users AS u
JOIN 
(
    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x ON x.Id = u.Id;

关系代数：为了彻底，为了不让 Joe Celko 困扰我的梦想，我们至少需要尝试一些奇怪的关系代数。这里什么都没有！

一次尝试INTERSECT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   INTERSECT
                   SELECT u.Age WHERE u.Age IS NOT NULL );

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,  elapsed time = 1090 ms.

这是一个尝试EXCEPT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   EXCEPT
                   SELECT u.Age WHERE u.Age IS NULL);

Table 'Users'. Scan count 7, logical reads 9247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2126 ms,  elapsed time = 376 ms.

可能还有其他方法可以编写这些，但我将把它留给那些可能比我更经常使用EXCEPT的人。INTERSECT

如果您真的只需要 我在查询中使用的计数COUNT作为速记（阅读：有时我懒得想出更多涉及的场景）。如果你只需要一个计数，你可以使用一个CASE表达式来做几乎同样的事情。

SELECT SUM(CASE WHEN u.Age < 18 THEN 1
                WHEN u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

SELECT SUM(CASE WHEN u.Age < 18 OR u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

它们都获得相同的计划并具有相同的 CPU 和读取特性。

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 719 ms.

获胜者，冠军？ 在我的测试中，在派生表上使用 SUM 的强制并行计划表现最好。是的，可以通过添加几个过滤索引来解释这两个谓词来帮助这些查询中的许多查询，但我想将一些实验留给其他人。

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

谢谢！

I wasn't game to restore a 110 GB database for just one table so I created my own data. The age distributions should match what's on Stack Overflow but obviously the table itself won't match. I don't think that it's too much of an issue because the queries are going to hit indexes anyway. I'm testing on a 4 CPU computer with SQL Server 2016 SP1. One thing to note is that for queries that finish this quickly it's important not to include the actual execution plan. That can slow things down quite a bit.

I started by going through some of the solutions in Erik's excellent answer. For this one:

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

I got the following results from sys.dm_exec_sessions over 10 trials (the query naturally went parallel for me):

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     3532 ║                975 ║         60830 ║
╚══════════╩════════════════════╩═══════════════╝

The query that worked better for Erik actually performed worse on my machine:

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

Results from 10 trials:

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     5704 ║               1636 ║         60850 ║
╚══════════╩════════════════════╩═══════════════╝

I'm not immediately able to explain why it's that bad, but it's not clear why we want to force nearly every operator in the query plan to go parallel. In the original plan we have a serial zone that finds all rows with AGE < 18. There are only a few thousand rows. On my machine I get 9 logical reads for that part of the query and 9 ms of reported CPU time and elapsed time. There's also a serial zone for the global aggregate for the rows with AGE IS NULL but that only processes one row per DOP. On my machine this is just four rows.

My takeaway is that it's most important to optimize the part of the query that finds rows with a NULL for Age because there are millions of those rows. I wasn't able to create an index with less pages that covered the data than a simple page-compressed one on the column. I assume that there's a minimum index size per row or that a lot of the index space cannot be avoided with the tricks that I tried. So if we're stuck with about the same number of logical reads to get the data then the only way to make it faster is to make the query more parallel, but this needs to be done in a different way than Erik's query that used TF 8649. In the query above we have a ratio of 3.62 for CPU time to elapsed time which is pretty good. The ideal would be a ratio of 4.0 on my machine.

One possible area of improvement is to divide the work more evenly among threads. In the screenshot below we can see that one of my CPUs decided to take a little break:

Index scan is one of the few operators that can be implemented in parallel and we can't do anything about how the rows are distributed to threads. There's an element of chance to it as well but pretty consistently I saw one underworked thread. One way to work around this is to do parallelism the hard way: on the inner part of a nested loop join. Anything on the inner part of a nested loop will be implemented in a serial way but many serial threads can run concurrently. As long as we get a favorable parallel distribution method (such as round robin), we can control exactly how many rows are sent to each thread.

I'm running queries with DOP 4 so I need to evenly divide the NULL rows in the table into four buckets. One way to do this is to create a bunch of indexes on computed columns:

ALTER TABLE dbo.Users
ADD Compute_bucket_0 AS (CASE WHEN Age IS NULL AND Id % 4 = 0 THEN 1 ELSE NULL END),
Compute_bucket_1 AS (CASE WHEN Age IS NULL AND Id % 4 = 1 THEN 1 ELSE NULL END),
Compute_bucket_2 AS (CASE WHEN Age IS NULL AND Id % 4 = 2 THEN 1 ELSE NULL END),
Compute_bucket_3 AS (CASE WHEN Age IS NULL AND Id % 4 = 3 THEN 1 ELSE NULL END);

CREATE INDEX IX_Compute_bucket_0 ON dbo.Users (Compute_bucket_0) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_1 ON dbo.Users (Compute_bucket_1) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_2 ON dbo.Users (Compute_bucket_2) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_3 ON dbo.Users (Compute_bucket_3) WITH (DATA_COMPRESSION = PAGE);

I'm not quite sure why four separate indexes is a little faster than one index but that's one what I found in my testing.

To get a parallel nested loop plan I'm going to use the undocumented trace flag 8649. I'm also going to write the code a little strangely to encourage the optimizer not to process more rows than necessary. Below is one implementation which appears to work well:

SELECT SUM(t.cnt) + (SELECT COUNT(*) FROM dbo.Users AS u WHERE u.Age < 18)
FROM 
(VALUES (0), (1), (2), (3)) v(x)
CROSS APPLY 
(
    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_0 = CASE WHEN v.x = 0 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_1 = CASE WHEN v.x = 1 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_2 = CASE WHEN v.x = 2 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_3 = CASE WHEN v.x = 3 THEN 1 ELSE NULL END
) t
OPTION (QUERYTRACEON 8649);

The results from ten trials:

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     3093 ║                803 ║         62008 ║
╚══════════╩════════════════════╩═══════════════╝

通过该查询，我们的 CPU 与经过时间的比率为 3.85！我们从运行时缩短了 17 毫秒，并且只需要 4 个计算列和索引就可以完成！每个线程处理的总行数非常接近，因为每个索引的行数非常接近，并且每个线程只扫描一个索引：

最后一点，我们还可以点击简单按钮并将非集群 CCI 添加到Age列中：

CREATE NONCLUSTERED COLUMNSTORE INDEX X_NCCI ON dbo.Users (Age);

以下查询在我的机器上在 3 毫秒内完成：

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18 OR u.Age IS NULL;

这将很难被击败。

虽然我没有 Stack Overflow 数据库的本地副本，但我可以尝试几个查询。我的想法是从系统目录视图中获取用户数（而不是直接从基础表中获取行数）。然后计算符合（或可能不符合）Erik 标准的行数，并做一些简单的数学运算。

I used the Stack Exchange Data Explorer (Along with SET STATISTICS TIME ON; and SET STATISTICS IO ON;) to test the queries. For a point of reference, here are some queries and the CPU/IO statistics:

QUERY 1

--Erik's query From initial question.
SELECT COUNT(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 17) < 18;

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) returned)

Table 'Users'. Scan count 17, logical reads 201567, physical reads 0, read-ahead reads 2740, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1829 ms, elapsed time = 296 ms.

QUERY 2

--Erik's "OR" query.
SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18
OR u.Age IS NULL;

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) returned)

Table 'Users'. Scan count 17, logical reads 201567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 2500 ms, elapsed time = 147 ms.

QUERY 3

--Erik's derived tables/UNION ALL query.
SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) returned)

Table 'Users'. Scan count 34, logical reads 403134, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 3156 ms, elapsed time = 215 ms.

1st Attempt

This was slower than all of Erik's queries I listed here...at least in terms of elapsed time.

SELECT SUM(p.Rows)  -
  (
    SELECT COUNT(*)
    FROM dbo.Users AS u
    WHERE u.Age >= 18
  ) 
FROM sys.objects o
JOIN sys.partitions p
    ON p.object_id = o.object_id
WHERE p.index_id < 2
AND o.name = 'Users'
AND SCHEMA_NAME(o.schema_id) = 'dbo'
GROUP BY o.schema_id, o.name

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) returned)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysrowsets'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Users'. Scan count 1, logical reads 201567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 593 ms, elapsed time = 598 ms.

2nd Attempt

Here I opted for a variable to store the total number of users (instead of a sub-query). The scan count increased from 1 to 17 compared to the 1st attempt. Logical reads stayed the same. However, elapsed time dropped considerably.

DECLARE @Total INT;

SELECT @Total = SUM(p.Rows)
FROM sys.objects o
JOIN sys.partitions p
    ON p.object_id = o.object_id
WHERE p.index_id < 2
AND o.name = 'Users'
AND SCHEMA_NAME(o.schema_id) = 'dbo'
GROUP BY o.schema_id, o.name

SELECT @Total - COUNT(*)
FROM dbo.Users AS u
WHERE u.Age >= 18

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysrowsets'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (1 row(s) returned)

Table 'Users'. Scan count 17, logical reads 201567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1471 ms, elapsed time = 98 ms.

Other Notes: DBCC TRACEON is not permitted on Stack Exchange Data Explorer, as noted below:

User 'STACKEXCHANGE\svc_sede' does not have permission to run DBCC TRACEON.

使用变量？

declare @int1 int = ( select count(*) from table_1 where bb <= 1 )
declare @int2 int = ( select count(*) from table_1 where bb is null )
select @int1 + @int2;

根据评论可以跳过变量

SELECT (select count(*) from table_1 where bb <= 1) 
     + (select count(*) from table_1 where bb is null);

一个简单的解决方案是计算 count(*) - count (age >= 18)：

SELECT
    (SELECT COUNT(*) FROM Users) -
    (SELECT COUNT(*) FROM Users WHERE Age >= 18);

或者：

SELECT COUNT(*)
     - COUNT(CASE WHEN Age >= 18)
FROM Users;

结果在这里

善用SET ANSI_NULLS OFF;

SET ANSI_NULLS OFF; 
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE age=NULL or age<18

Table 'Users'. Scan count 17, logical reads 201567

 SQL Server Execution Times:
 CPU time = 2344 ms,  elapsed time = 166 ms.

这是我突然想到的。刚刚在https://data.stackexchange.com中执行了这个

但效率不如@blitz_erik