binki's questions

Estou usando um CTE recursivo em uma estrutura de árvore para listar todos os descendentes de um nó específico na árvore. Se eu escrever um valor de nó literal na minha WHEREcláusula, o SQL Server parece realmente aplicar o CTE apenas a esse valor, fornecendo um plano de consulta com baixas contagens de linhas reais, etc .:

No entanto, se eu passar o valor como parâmetro, ele parece realizar (spool) o CTE e depois filtrá-lo após o fato :

Eu poderia estar lendo os planos errado. Não notei um problema de desempenho, mas estou preocupado que a realização do CTE possa causar problemas com conjuntos de dados maiores, especialmente em um sistema mais ocupado. Além disso, normalmente componho essa travessia em si mesma: percorro os ancestrais e volto para os descendentes (para garantir que reuni todos os nós relacionados). Devido à forma como meus dados são, cada conjunto de nós “relacionados” é bastante pequeno, então a realização do CTE não faz sentido. E quando o SQL Server parece perceber o CTE, ele está me dando alguns números bastante grandes em suas contagens “reais”.

Existe uma maneira de fazer com que a versão parametrizada da consulta funcione como a versão literal? Eu quero colocar o CTE em uma visualização reutilizável.

Consulta com literal:

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;

Consulta com parâmetro:

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;

Código de configuração:

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;

Eu estava brincando com SET IMPLICIT_TRANSACTIONS ON. Encontrei uma situação em que estou convencido de que o banco de dados está criando e confirmando automaticamente uma transação sem deixá-la aberta. Se eu fizer isso IF EXISTS (SELECT * FROM dbo.t1)ou qualquer variação, tenho certeza de que uma transação está acontecendo em segundo plano e espero que permaneça aberta se estiver usando transações implícitas.

No entanto, depois de ler os documentos , ele diz algo como:

Isso significa que, se @@TRANCOUNT = 0, qualquer uma das instruções Transact-SQL a seguir iniciará uma nova transação.

e a lista que mostra não inclui IF. No entanto, espero que todas as instruções não encontradas na lista da documentação não saiam @@TRANCOUNTincrementadas. Então, procurei outra declaração não incluída nessa lista. eu encontrei DECLARE. No entanto, descobri que, se eu executar uma subconsulta com DECLARE, obterei o comportamento que esperaria de SET IMPLICIT_TRANSATIONS ON. Ou seja, se my DECLAREtiver uma subconsulta, ela será incrementada @@TRANCOUNTse for zero. Portanto, parece que a documentação é inconsistente consigo mesma

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

Por que o DECLAREincremento @@TRANCOUNTpara um incorporado SELECT, mas não IF? O SELECTincorporado no de IFalguma forma não está realmente usando uma transação?

Estou usando o SQL Server 13.0.4411.

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

A conversão de tipo na expressão (CONVERT_IMPLICIT(xml,[Expr1001],0)) pode afetar "CardinalityEstimate" na escolha do plano de consulta

Por que uma conversão está acontecendo? Achei que isso FOR XMLfazia com que o resultado já fosse do XMLtipo. Se este aviso pode/deve ser ignorado, como faço para impedir que o aviso apareça no plano de execução (para que, quando eu depurar uma consulta maior com um aviso semelhante, isso não me distraia dos problemas reais)?