binki's questions

我在树结构上使用递归 CTE 来列出树中特定节点的所有后代。如果我在我的WHERE子句中写一个文字节点值，SQL Server 似乎实际上只将 CTE 应用于该值，从而给出一个实际行数较低的查询计划，等等：

但是，如果我将该值作为参数传递，它似乎会实现（假脱机）CTE，然后在事后对其进行过滤：

我可能读错了计划。我没有注意到性能问题，但我担心 CTE 的实现可能会导致更大的数据集出现问题，尤其是在更繁忙的系统中。此外，我通常在自身上复合这种遍历：我向上遍历祖先并返回到后代（以确保我收集所有相关节点）。由于我的数据是这样的，每组“相关”节点都相当小，因此实现 CTE 没有意义。当 SQL Server 似乎实现了 CTE 时，它的“实​​际”计数给了我一些相当大的数字。

有没有办法让查询的参数化版本像文字版本一样工作？我想将 CTE 放在可重用的视图中。

用文字查询：

CREATE PROCEDURE #c AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = 24
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c;

带参数查询：

CREATE PROCEDURE #c (@Id BIGINT) AS BEGIN;
    WITH descendants AS (SELECT
         t.ParentId Id
        ,t.Id DescendantId
    FROM #tree t
    WHERE t.ParentId IS NOT NULL
    UNION ALL SELECT
         d.Id
        ,t.Id DescendantId
    FROM descendants d
    JOIN #tree t ON d.DescendantId = t.ParentId)
    SELECT d.*
    FROM descendants d
    WHERE d.Id = @Id
    ORDER BY d.Id, d.DescendantId;
END;
GO
EXEC #c 24;

设置代码：

DECLARE @count BIGINT = 100000;
CREATE TABLE #tree (
     Id BIGINT NOT NULL PRIMARY KEY
    ,ParentId BIGINT
);
CREATE INDEX tree_23lk4j23lk4j ON #tree (ParentId);
WITH number AS (SELECT
         CAST(1 AS BIGINT) Value
    UNION ALL SELECT
         n.Value * 2 + 1
    FROM number n
    WHERE n.Value * 2 + 1 <= @count
    UNION ALL SELECT
         n.Value * 2
    FROM number n
    WHERE n.Value * 2 <= @count)
INSERT #tree (Id, ParentId)
SELECT n.Value, CASE WHEN n.Value % 3 = 0 THEN n.Value / 4 END
FROM number n;

我在玩SET IMPLICIT_TRANSACTIONS ON. 我发现了一种情况，我确信数据库会自动创建并提交一个事务，而不会让它保持打开状态。如果我这样做IF EXISTS (SELECT * FROM dbo.t1)或有任何变化，我确定交易正在后台发生，如果我使用隐式交易，我希望它保持开放状态。

但是，在阅读文档之后，它说的是这样的：

这意味着如果@@TRANCOUNT = 0，以下任何 Transact-SQL 语句都会开始一个新事务。

并且它显示的列表不包括IF. 但是，我希望在文档列表中找不到的所有语句都不会@@TRANCOUNT递增。因此，我寻找了另一个未包含在该列表中的声明。我发现DECLARE。但是，我发现如果我使用 执行子查询DECLARE，我会得到我期望的行为SET IMPLICIT_TRANSATIONS ON。也就是说，如果 my有一个子查询，如果它为零DECLARE，它就会递增。@@TRANCOUNT所以看起来文档与自身不一致

SET IMPLICIT_TRANSACTIONS ON
GO
IF OBJECT_ID('dbo.t1') IS NOT NULL
BEGIN
    DROP TABLE dbo.t1
    COMMIT
END
GO
CREATE TABLE dbo.t1 (x INT);
COMMIT;
GO

IF CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END = 1 PRINT 'unreachable';
PRINT 'after1 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after1 0

DECLARE @x INT = 2;
PRINT 'after2 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after2 0

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;
PRINT 'after3 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after3 1
GO

WHILE @@TRANCOUNT > 0 ROLLBACK
GO

为什么嵌入式而不是DECLARE增量？嵌入在某种程度上实际上没有使用事务吗？@@TRANCOUNTSELECTIFSELECTIF

我正在使用 SQL Server 13.0.4411。

SET STATISTICS XML ON;
DECLARE @x XML = (SELECT 'hi' "@id" FOR XML PATH ('this'), root ('xml'));

表达式中的类型转换 (CONVERT_IMPLICIT(xml,[Expr1001],0)) 可能会影响查询计划选择中的“CardinalityEstimate”

为什么会发生转换？我认为这FOR XML导致结果XML已经是类型。如果可以/应该忽略此警告，我如何阻止警告出现在执行计划中（这样当我调试带有类似警告的更大查询时，它不会分散我对实际问题的注意力）？