带NULL参数的SARGable查询：如何保留全量查询且避免聚集索引扫描？

嘿，这个问题我在日常工作里碰到好多次了——带NULL参数的动态过滤查询很容易让SQL Server的查询优化器“犯难”，导致明明有合适的索引却还是走全表扫描。下面给你几个实用的解决办法，既能保留“不传参数返回全量”的需求，又能让传参数时用上高效的索引查找：

方案1：使用OPTION (RECOMPILE)查询提示 #

这是最简单的解决方案之一。当你添加这个提示后，SQL Server会在每次执行存储过程时，根据当前传入的参数值重新生成执行计划。这样一来，当@CompletedBy有具体值时，优化器会选择索引查找；当参数为NULL时，会生成适合全量查询的执行计划。

示例代码：

CREATE PROCEDURE GetRecords
    @CompletedBy INT = NULL -- 根据实际参数类型调整
AS
BEGIN
    SET NOCOUNT ON;
    -- 建议替换*为具体字段，提升性能和可读性
    SELECT Id, CompletedBy, OtherField
    FROM YourTargetTable
    WHERE CompletedBy = @CompletedBy
       OR @CompletedBy IS NULL
    OPTION (RECOMPILE);
END

注意：每次重新编译会带来一点点额外开销，但如果你的存储过程执行频率不是极高，这点开销完全可以忽略，对比索引扫描的性能提升来说非常值得。

方案2：用IF ELSE分支拆分查询 #

直接根据参数是否为NULL拆分逻辑，让两种场景完全独立。优化器会为每个分支生成最优的执行计划，性能最稳定，可读性也很强。

示例代码：

CREATE PROCEDURE GetRecords
    @CompletedBy INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF @CompletedBy IS NOT NULL
    BEGIN
        SELECT Id, CompletedBy, OtherField
        FROM YourTargetTable
        WHERE CompletedBy = @CompletedBy;
    END
    ELSE
    BEGIN
        SELECT Id, CompletedBy, OtherField
        FROM YourTargetTable;
    END
END

优点：完全避免了OR条件带来的执行计划问题，两种场景的性能都能达到最优；缺点是会有少量代码重复，适合逻辑简单的查询。

方案3：动态SQL拼接 #

如果你的查询涉及多个可选参数，动态SQL会更灵活。通过拼接不同的查询语句，让每个参数组合都对应最优的执行计划，同时用sp_executesql确保参数化，避免SQL注入风险。

示例代码：

CREATE PROCEDURE GetRecords
    @CompletedBy INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @Params NVARCHAR(MAX) = N'@CompletedBy INT';

    SET @SQL = N'SELECT Id, CompletedBy, OtherField FROM YourTargetTable WHERE 1=1';
    IF @CompletedBy IS NOT NULL
    BEGIN
        SET @SQL += N' AND CompletedBy = @CompletedBy';
    END

    EXEC sp_executesql @SQL, @Params, @CompletedBy;
END

优点：适合多参数的复杂场景，能灵活生成对应条件的查询；注意一定要用sp_executesql而不是直接EXEC，这样才能重用执行计划并防止注入。

内容的提问来源于stack exchange，提问作者dualcoredba