Geezer's questions

Meu entendimento aqui é que quando uma View é criada, a definição é armazenada em metadados sys.tables. ou seja sys.views.

Além disso, se você usá SELECT * FROM-lo, armazenará os nomes exatos das colunas, por exemplo SELECT a, b FROM.

Mesmo se você usar a "CHECK OPTION" - ela ainda não será validada nas tabelas subjacentes.

"SCHEMABINDING", por outro lado, será validado nas tabelas subjacentes.

Meu problema é quando uma coluna em uma tabela é droppedsubstituída por outra com o mesmo nome, mas computedalgo estranho acontece quando você consulta uma visualização com base nessa tabela.

Aqui está um exemplo.

DROP TABLE IF EXISTS dbo.Test1;
DROP TABLE IF EXISTS dbo.Test2;
GO
CREATE TABLE dbo.Test1
(
    Id          INT IDENTITY(1,1) PRIMARY KEY ,
    Test1Col1   VARCHAR(80) NULL ,
    Test1Col2   VARCHAR(80) NULL ,
    Test1Col3   VARCHAR(80) NULL 
);
CREATE TABLE dbo.Test2
(
    Id          INT IDENTITY(1,1) PRIMARY KEY ,
    Test2Col1   VARCHAR(80) NULL ,
    Test2Col2   VARCHAR(80) NULL ,
    Test2Col3   VARCHAR(80) NULL ,
    Test1Id     INT 
);
GO
INSERT INTO dbo.Test1
    (Test1Col1, Test1Col2, Test1Col3)
VALUES 
    ('Test1Col1Data1', 'Test1Col2Data1', 'Test1Col3Data1') ,
    ('Test1Col1Data2', 'Test1Col2Data2', 'Test1Col3Data2') ,
    ('Test1Col1Data3', 'Test1Col2Data3', 'Test1Col3Data3') ;
GO
INSERT INTO dbo.Test2
    (Test2Col1, Test2Col2, Test2Col3, Test1Id)
VALUES 
    ('Test2Col1Data1', 'Test2Col2Data1', 'Test2Col3Data1', 1) ,
    ('Test2Col1Data2', 'Test2Col2Data2', 'Test2Col3Data2', 2) ,
    ('Test2Col1Data3', 'Test2Col2Data3', 'Test2Col3Data3', 3) ;
GO

Crie uma visualização baseada em tabelas.

CREATE OR ALTER 
    VIEW    dbo.View1 
AS

    SELECT T1.*, T2.*
    FROM    (
            SELECT  TestId = T.Id
            FROM    dbo.Test1   T
        )   T1
    INNER JOIN  dbo.Test2   T2  ON  T2.Test1Id = T1.TestId ;
GO
SELECT  * FROM dbo.View1 ;
GO

Você obtém este conjunto de resultados,

Agora eu ALTERmesa dbo.Test2.

DROPcoluna Test2Col3 e substituiu-a por uma computedcoluna com o mesmo nome.

ALTER TABLE dbo.Test2
    DROP COLUMN Test2Col3 ;
ALTER TABLE dbo.Test2
    ADD Test2Col3 AS Test2Col1;
GO

Agora, quando consulto a visualização, obtenho o seguinte conjunto de resultados.

SELECT  * FROM dbo.View1 ;
GO

Os dados em colunas Test2Col3pareciam Test1Idter deslocado 1 para a esquerda.

O que eu esperaria ver Test2Col3está sendo mostrado Test1Ide vice-versa.

Por que é isso?
Eu sei que uma atualização ou alteração da visualização corrigirá isso, mas os nomes das colunas não foram alterados. Eu repliquei isso no banco de dados SQL-2022 e Azure SQL.

Em um banco de dados SQL do Azure (compatibilidade com SQL2019), tenho um processo ETL que preenche tabelas HISTORY em um padrão DeltaTrack.

No Proc, há um UPDATE na tabela HISTORY que o mecanismo de consulta está usando um SORT, mas tenho um índice que deve cobri-lo.

O caso de uso para este UPDATE é para linhas existentes onde adicionamos colunas adicionais à ingestão desde que a linha foi adicionada pela primeira vez à tabela HISTORY.

Este SORT está fazendo com que os Procs em que as atualizações estão em nossas tabelas maiores/mais largas sejam dolorosamente lentos.

Como ajusto o índice ou consulta para remover o SORT na consulta 3 ?

Aqui está o plano de execução atualizado conforme solicitado por JD

Aqui está o DDL.

DROP TABLE IF EXISTS dbo.STAGE;
GO
CREATE TABLE dbo.STAGE
(
    Id varchar(18) NULL,
    CreatedDate varchar(4000) NULL,
    LastModifiedDate varchar(4000) NULL,
    LastReferencedDate varchar(4000) NULL,
    [Name] varchar(4000) NULL,
    OwnerId varchar(4000) NULL,
    SystemTimestamp datetime2(7) NULL
)
GO

DROP TABLE IF EXISTS dbo.HISTORY;
GO
CREATE TABLE dbo.HISTORY
(
    HistoryRecordId int IDENTITY(1,1) NOT NULL,
    [Hash] binary(64) NOT NULL,
    [IsActive]  BIT NOT NULL ,
    ActiveFromDateTime datetime2(7) NOT NULL,
    ActiveToDateTime datetime2(7) NOT NULL,
    Id varchar(18) NOT NULL,
    CreatedDate datetime2(7) NULL,
    LastModifiedDate datetime2(7) NULL,
    LastReferencedDate datetime2(7) NULL,
    [Name] varchar(80) NULL,
    OwnerId varchar(18) NULL,
    SystemTimestamp datetime2(7) NULL
) 
GO
CREATE UNIQUE CLUSTERED INDEX [CL__HISTORY] ON dbo.HISTORY
(
    Id , 
    [ActiveToDateTime] ASC,
    [IsActive] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX__HISTORY_IsActive] ON dbo.HISTORY
(
    [Id] ASC
)
INCLUDE([IsActive],[ActiveToDateTime]) 
GO

DROP TABLE IF EXISTS #updates;
GO


WITH src AS (
  SELECT 
    CONVERT(VARCHAR(18), t.[Id]) AS [Id]
  , CONVERT(DATETIME2, t.[CreatedDate]) AS [CreatedDate]
  , CONVERT(DATETIME2, t.[LastModifiedDate]) AS [LastModifiedDate]
  , CONVERT(DATETIME2, t.[LastReferencedDate]) AS [LastReferencedDate]
  , CONVERT(VARCHAR(80), t.[Name]) AS [Name]
  , CONVERT(VARCHAR(18), t.[OwnerId]) AS [OwnerId]
  , CONVERT(DATETIME2, t.SystemTimestamp) AS SystemTimestamp
  , dgst.[Hash]
  , CONVERT(DATETIME2, SystemTimestamp) AS [ActiveFromDateTime]
  , RN = ROW_NUMBER() OVER ( 
            PARTITION BY 
                t.[Id] 
                ORDER BY CONVERT(DATETIME2, SystemTimestamp) DESC
        ) 
  FROM dbo.STAGE t
    OUTER APPLY (
      SELECT 
        CAST(HASHBYTES('SHA2_256',
          COALESCE(CAST([CreatedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([LastModifiedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([LastReferencedDate] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([Name] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST([OwnerId] AS NVARCHAR(4000)), N'')
            + N'||' + COALESCE(CAST(SystemTimestamp AS NVARCHAR(4000)), N'')
        ) AS BINARY(64)) AS [Hash]
      ) dgst
), tgt AS (
  SELECT *
  FROM dbo.HISTORY t
  WHERE t.[ActiveToDateTime] > GETUTCDATE()
  AND 1 = 1  
)
SELECT 
  tgt.HistoryRecordId
, src.*
INTO #updates
FROM src
  LEFT JOIN tgt 
    ON tgt.[Id] = src.[Id] WHERE src.RN = 1;  
GO

--Create index on temp table (#updates) 
CREATE NONCLUSTERED INDEX NCCI_#updates__Kimble_HISTORY_ForecastStatus 
    ON #updates ( [Id] , ActiveFromDateTime, [Hash] );
GO  


    UPDATE  tgt 
    SET
      tgt.[Hash]        = src.[Hash] 
    , tgt.IsActive      = 1
    , tgt.[CreatedDate] = src.[CreatedDate]
    , tgt.[LastModifiedDate]    = src.[LastModifiedDate]
    , tgt.[LastReferencedDate]  = src.[LastReferencedDate]
    , tgt.[Name]            = src.[Name]
    , tgt.[OwnerId]         = src.[OwnerId]
    , tgt.SystemTimestamp   = src.SystemTimestamp
    FROM dbo.HISTORY tgt
      INNER JOIN #updates src   
            ON tgt.[Id] = src.[Id]
            AND src.[ActiveFromDateTime] = tgt.[ActiveFromDateTime] 
            AND tgt.[Hash]  <> src.[Hash] ; 
GO

Quando uma linha é atualizada, a coluna SYS_CHANGE_OPERATION ainda mostra I (Inserir) e não U (Atualizar) ao consultar CHANGETABLE .

Aqui está o que estou fazendo.

USE master
GO
CREATE DATABASE TestCT ; 
GO
SELECT compatibility_level , @@VERSION 
FROM sys.databases WHERE name = 'TestCT'; 

compatibilidade_level = 160
@@Version = Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64 bits) no Windows 10 Pro 10.0 (Build 19045: ) (Hipervisor)

ALTER DATABASE TestCT
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) ; 

USE TestCT;
GO
DROP TABLE IF EXISTS dbo.TestCT
CREATE TABLE dbo.TestCT(
    Id      INT IDENTITY (1,1) CONSTRAINT PK__TestCT PRIMARY KEY ,
    Col1    VARCHAR(100)
) ; 
GO

ALTER TABLE dbo.TestCT
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON) ; 
GO

INSERT INTO dbo.TestCT  (Col1)
            VALUES      ('INSERT1') ; 
GO
SELECT * 
FROM CHANGETABLE (CHANGES dbo.TestCT,0) as  CT 
LEFT JOIN dbo.TestCT         EM ON CT.Id = EM.Id
ORDER BY SYS_CHANGE_VERSION ; 
GO

SYS_CHANGE_OPERATIONaqui está I (Inserir) como esperado.

Agora, se eu atualizar a linha

UPDATE  T 
SET     Col1 = 'UPDATE'
FROM    dbo.TestCT   T 
WHERE   Col1 = 'INSERT1';
GO

SYS_CHANGE_OPERATIONaqui ainda é I (Inserir) e não U (Atualizar). Como você pode ver, SYS_CHANGE_VERSIONaumentou em 1 como esperado.

O que estou fazendo de errado aqui?

Banco de Dados SQL do Azure.

Eu tenho uma tabela da qual preciso obter as primeiras e mais recentes linhas para Col1e Col2com base em CreateDate.

CREATE TABLE dbo.table1 (
    Id            INT    IDENTITY(1,1) PRIMARY KEY ,
    Col1        VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    Col2        VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    CreateDate    DATETIME NOT NULL
) ;

Eu tenho um índice assim:

CREATE INDEX IX__table1_ASC
ON dbo.table1 (Col1, Col2, CreateDate );

Minha consulta para obter a primeira linha é (planeje aqui ):

--Get the first row
SELECT    TOP (1) WITH TIES
        *
FROM    table1
ORDER BY ROW_NUMBER()
        OVER (PARTITION BY Col1, Col2
              ORDER BY CreateDate );

A verificação de índice está usando o índice ( IX__table1_ASC) que criei, mas por que estou recebendo uma classificação?

Minha consulta para obter a linha mais recente (planeje aqui ):

--get latest row
SELECT    TOP (1) WITH TIES
        *
FROM    table1
ORDER BY ROW_NUMBER()
        OVER (PARTITION BY Col1, Col2
              ORDER BY CreateDate DESC); --desc here

Novamente, a varredura de índice está usando o índice ( IX__table1_ASC), mas desta vez estou obtendo duas classificações. O primeiro logo após a varredura do índice. O otimizador não é inteligente o suficiente para ler o índice na ordem inversa? Novamente, para que serve o segundo tipo?

A tabela real é bastante grande, então você pode imaginar que os tipos são caros. Como posso otimizar melhor aqui?

Tenho uma tabela larga, relativamente grande, 14.264.775 linhas, rodando no banco de dados Azure SQL.

A consulta a seguir precisa de algum TLC.

IF EXISTS (
    SELECT  1/0 
    FROM dbo.table1 src
      INNER JOIN dbo.table1 tgt 
        ON tgt.Col1 = src.Col1
    WHERE tgt.ValidFrom <= src.ValidTo
    AND tgt.ValidTo >= src.ValidFrom
    AND tgt.RecordId <> src.RecordId  
)
 BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
 END ;

Eu tenho esse índice.

CREATE NONCLUSTERED INDEX [IX__table1] ON dbo.table1
        ( Col1 ) 
INCLUDE (ValidFrom, ValidTo, RecordId)
GO

Este é o io stats da consulta. As leituras lógicas são através do telhado.

Aqui está o XML do plano . Eu tentei PasteThePlan, mas não analisaria o XML do plano. (talvez não goste do plano de banco de dados sql do Axure xml).

Como você pode ver, há uma varredura de índice em [src] ; lendo 14.264.775 linhas (o mesmo número de todas as linhas da tabela) . E uma busca de índice em [tgt] ; lendo 194.405.307 linhas.

O que preciso mudar para melhorar o desempenho da consulta?

Existem 2,1 milhões de valores Col1 exclusivos entre as 14 milhões de linhas totais.

Estou trabalhando em um data warehouse de esquema em estrela criado no banco de dados SQL do Azure onde o último desenvolvedor incluído WITH RECOMPILEem todos os Procs.

Isso eu acredito que foi porque o ETL só executa esses Procs duas vezes por dia, então a sobrecarga de recompilações é mínima.

No entanto, não existe o risco de o cache ficar inchado com todos esses planos e, em caso afirmativo, qual é a melhor maneira de manter o cache do plano para mantê-lo em bom estado e o mais eficiente possível?

Estou escrevendo um TVF para consultar uma tabela grande (dezenas de milhões de linhas) onde uma string (Código Postal) nessa tabela corresponde a uma string (um código postal parcial (parte de código de saída/setor)) de outra tabela.

Estou atingindo um caso extremo que não consigo resolver.

Para quem não conhece os códigos postais do Reino Unido

Aqui estão alguns dados de exemplo.

DECLARE @tab1 TABLE (Sk INT, Postcode VARCHAR(8))
DECLARE @tab2 TABLE (Sk INT, Coverage VARCHAR(8)) 

INSERT INTO @tab1 (Sk, Postcode) VALUES (1, 'E12 5HH'), (6, 'SW1X 6AA')
INSERT INTO @tab2 (Sk, Coverage) VALUES (1, 'E12'), (1, 'E12 5'), 
                                        (2, 'E1'), (2, 'E11'), (2, 'E13'), 
                                        (3, 'E12 6'),
                                        (4, 'E12 5') ,
                                        (5, 'E12') ,
                                        (7, 'SW1') ,
                                        (8, 'SW1X') 

E minha consulta atual

SELECT  S.Sk, 
        S.Postcode, 
        CoverageSk = X.Sk, 
        X.Coverage 
FROM        @tab1   S 
OUTER APPLY (
    SELECT  Sk , 
            Coverage , 
            [Length] = LEN(Coverage) 
    FROM    @tab2
)   X 
WHERE   S.Sk <> X.Sk 
AND     LEFT(S.Postcode,X.[Length] ) = X.Coverage 

Esses são meus resultados.

Os dados nas linhas 1 e 4 não devem estar nos resultados. Linha 1, A Cobertura ou o código de saída "E1" não é o mesmo que a parte do código de saída ("E12") do CEP "E12 5HH".

O mesmo para a Linha 4, a Cobertura ou o código de saída "SW1" não é o mesmo que a parte do código de saída ("SW1X") do Código Postal "SW1X 6AA".

No SSMS, ao se conectar a qualquer um dos nossos Bancos de Dados SQL do Azure usando contas de serviço e autenticação de servidor SQL, a conexão em si pode levar de 10 a 15 segundos para ser estabelecida.

Uma vez conectado, clique com o botão direito do mouse em qualquer tabela e clique em "selecionar as 1000 principais linhas" isso também leva entre 10 e 15 segundos para responder e apresentar as linhas no SSMS.

Nenhum desses atrasos/atrasos ocorre ao usar a autenticação "Active Directory - Universal com MFA Supprt" - no entanto, isso não está usando as mesmas contas de serviço.

Como o atraso de autenticação do SQL Server pode ser resolvido/investigado?

Alguém sabe quando o suporte para Active Directory - Universal com MFA será incluído no Reporting Services?

Está disponível em SSMS.

Propriedades da fonte de dados do Visual Studio 2017

Propriedades da fonte de dados do servidor de relatórios

Se o SQL Agent não estiver disponível para o Banco de Dados SQL do Azure, é possível manter (índices, estatísticas etc.) os bancos de dados com os scripts de manutenção de Ola Hallengren, por exemplo?

Banco de Dados SQL do Azure - Standard Edition (camada de serviço S3)

Por que o modo de lote só entra em vigor ao usar funções agregadas?

DROP TABLE IF EXISTS dbo.TransCS

CREATE TABLE dbo.TransCS (
     Col1 INT 
    ,Col2 AS Col1*2
)
CREATE CLUSTERED COLUMNSTORE INDEX CS_TransCS on dbo.TransCS;

WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 
    AS n FROM L5)
INSERT INTO dbo.TransCS (Col1)
SELECT TOP (1000000) n FROM Nums ORDER BY n; 

Inserir 1 milhão de linhas

Processamento de linha

SELECT * FROM dbo.TransCS

Plano de execução

Processamento em lote

SELECT Col1, Col2, SUM(Col2)OVER ()
FROM dbo.TransCS

Plano de execução

Existe uma maneira de utilizar os benefícios de desempenho sem usar as funções Agg? A documentação sobre isso é escassa.