Antyan提出的问题 -dba

Antyan

Asked: 2022-06-16 06:38:44 +0800 CST

Estratégia de indexação ideal para atualizações de Datawarehouse e Data Lake

Temos um banco de dados sql server que usamos como data lake e datawarehouse. Cada tabela no banco de dados tem alguma definição padronizada, já que temos cerca de 600 tabelas agora, portanto, a manutenção precisa ser um pouco automatizada.

O processo geral para carregar cada tabela é primeiro carregar uma cópia da tabela em uma tabela hash no esquema changeLog (às vezes apenas com os registros alterados, se pudermos determinar quais são os registros alterados) e, em seguida, comparar a tabela changeLog com o destino tabela. A tabela de destino é usada para relatórios, portanto, essa abordagem changeLog nos permite persistir na tabela de destino e aplicar apenas operações UPDATE/INSERT mínimas.

Cada tabela de destino tem uma Chave Exclusiva/Chave Comercial que é identificável por meio de uma tabela de configuração E possui colunas de auditoria padronizadas com o mesmo nome em todas as tabelas. As colunas de auditoria nos dizem

quando o registro foi adicionado ao datawarehouse
quando o registro foi atualizado pela última vez no datawarehouse
se o registro foi excluído ou não na fonte
um identificador de registro alterado usando HASHBYTES('SHA2_256', CONCAT())

O identificador de registro alterado costumava ser CHECKSUM(), mas descobrimos que CHECKSUM() tem uma taxa de colisão muito alta para ser confiável. Acabei de adicionar uma coluna HASHBYTES() a cada tabela e preenchê-la.

Eu criei a coluna HASHBYTES() como VARBINARY(MAX). Agora, toda vez que uma tabela está sendo carregada, podemos dizer se um registro precisa ser atualizado comparando o novo valor de HASHBYTES(), calculado na tabela changeLog, com o valor persistido na tabela de destino.

Percebi imediatamente que a mudança de INT CHECKSUM() para VARBINARY(MAX) HASHBYTES() fez com que o processo de verificação de atualização ficasse significativamente lento. Eu tenho índices NONCLUSTERED em cada coluna CHECKSUM, mas não na coluna HASHBYTES que acabei de adicionar. Há também índices clusterizados para a chave exclusiva de cada tabela.

Qual é o índice ideal para adicionar para verificar atualizações?
Existe um índice padronizado que posso adicionar a cada tabela?
VARBINARY(MAX) é o tipo de dados adequado ou posso reduzi-lo com segurança para um tamanho menor?

Espero que isso seja suficiente para que essa pergunta faça sentido. Preciso acelerar esse processo o mais rápido possível.

EDIT: Estou adicionando um script SQL grande como exemplo, ele tem uma definição de tabela de exemplo para a versão changeLog da tabela e a versão de destino, bem como a consulta que é executada para atualizar a versão de destino.

--OBJECT DEFINITIONS
CREATE TABLE [changeLog].[DimSalesOffice](
    [Sales Office Code] [varchar](3) NOT NULL,
    [CCN_Key] [uniqueidentifier] NOT NULL,
    [Sales Office Name (Short)] [nvarchar](14) NOT NULL,
    [Sales Office Name (Long)] [nvarchar](50) NOT NULL,
    [Sales Office City] [nvarchar](50) NOT NULL,
    [Sales Office StateProvince] [nvarchar](50) NOT NULL,
    [Sales Office Postal Code] [nvarchar](20) NOT NULL,
    [Sales Office Country Code] [varchar](3) NOT NULL,
    [Sales Office Address] [nvarchar](65) NOT NULL,
    [Sales Office Address (Line 2)] [nvarchar](65) NOT NULL,
    [Sales Office Name (Short - Native Language)] [nvarchar](14) NOT NULL,
    [Sales Office Name (Long - Native Language)] [nvarchar](50) NOT NULL,
    [Sales Office Address (Native Language)] [nvarchar](65) NOT NULL,
    [Sales Office Address (Line 2 - Native Language)] [nvarchar](65) NOT NULL,
    [Sales Office City (Native Language)] [nvarchar](100) NOT NULL,
    [Sales Office StateProvince (Native Language)] [nvarchar](100) NOT NULL,
    [Sales Office Region] [nvarchar](100) NULL,
    [Native Language Code] [varchar](2) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DimSalesOffice](
    [SalesOffice_Key] [uniqueidentifier] NOT NULL,
    [Sales Office Code] [varchar](3) NOT NULL,
    [CCN_Key] [uniqueidentifier] NOT NULL,
    [Sales Office Name (Short)] [nvarchar](14) NOT NULL,
    [Sales Office Name (Long)] [nvarchar](50) NOT NULL,
    [Sales Office City] [nvarchar](50) NOT NULL,
    [Sales Office StateProvince] [nvarchar](50) NOT NULL,
    [Sales Office Postal Code] [nvarchar](20) NOT NULL,
    [Sales Office Country Code] [varchar](3) NOT NULL,
    [Sales Office Address] [nvarchar](65) NOT NULL,
    [Sales Office Address (Line 2)] [nvarchar](65) NOT NULL,
    [Sales Office Name (Short - Native Language)] [nvarchar](14) NOT NULL,
    [Sales Office Name (Long - Native Language)] [nvarchar](50) NOT NULL,
    [Sales Office Address (Native Language)] [nvarchar](65) NOT NULL,
    [Sales Office Address (Line 2 - Native Language)] [nvarchar](65) NOT NULL,
    [Sales Office City (Native Language)] [nvarchar](100) NOT NULL,
    [Sales Office StateProvince (Native Language)] [nvarchar](100) NOT NULL,
    [Sales Office Region] [nvarchar](100) NULL,
    [Native Language Code] [varchar](2) NOT NULL,
    [DW_CreatedOn] [datetime2](7) NULL,
    [DW_ModifiedOn] [datetime2](7) NULL,
    [DW_IsDeleted?] [bit] NULL,
    [DW_Checksum] [int] NULL,
    [Source_ModifiedOn] [datetime2](7) NULL,
    [DW_Hashbytes] [varbinary](max) NULL,
PRIMARY KEY NONCLUSTERED 
(
    [SalesOffice_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[DimSalesOffice] ADD  DEFAULT (newsequentialid()) FOR [SalesOffice_Key]
GO

CREATE UNIQUE CLUSTERED INDEX [IX_UK_DimSalesOffice] ON [dbo].[DimSalesOffice]
(
    [Sales Office Code] ASC
)
GO

--MERGE QUERY
DECLARE @InsertRecordCount INT, @UpdateRecordCount INT;

/*****UPDATE*****/
UPDATE [dbo].[DimSalesOffice] SET
    [CCN_Key] = [Source].[CCN_Key],
    [Sales Office Name (Short)] = [Source].[Sales Office Name (Short)],
    [Sales Office Name (Long)] = [Source].[Sales Office Name (Long)],
    [Sales Office City] = [Source].[Sales Office City],
    [Sales Office StateProvince] = [Source].[Sales Office StateProvince],
    [Sales Office Postal Code] = [Source].[Sales Office Postal Code],
    [Sales Office Country Code] = [Source].[Sales Office Country Code],
    [Sales Office Address] = [Source].[Sales Office Address],
    [Sales Office Address (Line 2)] = [Source].[Sales Office Address (Line 2)],
    [Sales Office Name (Short - Native Language)] = [Source].[Sales Office Name (Short - Native Language)],
    [Sales Office Name (Long - Native Language)] = [Source].[Sales Office Name (Long - Native Language)],
    [Sales Office Address (Native Language)] = [Source].[Sales Office Address (Native Language)],
    [Sales Office Address (Line 2 - Native Language)] = [Source].[Sales Office Address (Line 2 - Native Language)],
    [Sales Office City (Native Language)] = [Source].[Sales Office City (Native Language)],
    [Sales Office StateProvince (Native Language)] = [Source].[Sales Office StateProvince (Native Language)],
    [Sales Office Region] = [Source].[Sales Office Region],
    [Native Language Code] = [Source].[Native Language Code],
    [DW_Checksum] =
        CHECKSUM(
            [Source].[CCN_Key],
            [Source].[Sales Office Name (Short)],
            [Source].[Sales Office Name (Long)],
            [Source].[Sales Office City],
            [Source].[Sales Office StateProvince],
            [Source].[Sales Office Postal Code],
            [Source].[Sales Office Country Code],
            [Source].[Sales Office Address],
            [Source].[Sales Office Address (Line 2)],
            [Source].[Sales Office Name (Short - Native Language)],
            [Source].[Sales Office Name (Long - Native Language)],
            [Source].[Sales Office Address (Native Language)],
            [Source].[Sales Office Address (Line 2 - Native Language)],
            [Source].[Sales Office City (Native Language)],
            [Source].[Sales Office StateProvince (Native Language)],
            [Source].[Sales Office Region],
            [Source].[Native Language Code],
            0
        ),
    [DW_Hashbytes] = 
        HASHBYTES(
            'SHA2_256',
            ISNULL(CAST([Source].[CCN_Key] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Postal Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Country Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2 - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Region] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Native Language Code] AS NVARCHAR(MAX)), '') + '|'
            + '0'
        ),
    [Source_ModifiedOn] = NULL,
    [DW_ModifiedOn] = GETUTCDATE(),
    [DW_IsDeleted?] = 0
FROM [changeLog].[DimSalesOffice] [Source]
JOIN [dbo].[DimSalesOffice]
    ON [Source].[Sales Office Code] = [DimSalesOffice].[Sales Office Code]
    AND ISNULL([DimSalesOffice].[DW_Hashbytes], HASHBYTES('SHA2_256', '')) <> HASHBYTES(
            'SHA2_256',
            ISNULL(CAST([Source].[CCN_Key] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Postal Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Country Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2 - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Region] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Native Language Code] AS NVARCHAR(MAX)), '') + '|'
            + '0'
        )
SET @UpdateRecordCount = @@ROWCOUNT;

/*****Soft Deletes*****/
UPDATE [dbo].[DimSalesOffice] SET
    [DW_Checksum] = 0,
    [DW_Hashbytes] = 
        HASHBYTES(
            'SHA2_256',
            ISNULL(CAST([DimSalesOffice].[CCN_Key] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Name (Short)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Name (Long)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office City] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office StateProvince] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Postal Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Country Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Address] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Address (Line 2)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Name (Short - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Name (Long - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Address (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Address (Line 2 - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office City (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office StateProvince (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Sales Office Region] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([DimSalesOffice].[Native Language Code] AS NVARCHAR(MAX)), '') + '|'
            + '1'
        ),
    [Source_ModifiedOn] = NULL,
    [DW_ModifiedOn] = GETUTCDATE(),
    [DW_IsDeleted?] = 1
FROM [dbo].[DimSalesOffice]
WHERE NOT EXISTS
    (
        SELECT 1
        FROM [changeLog].[DimSalesOffice] [Source]
        WHERE [Source].[Sales Office Code] = [DimSalesOffice].[Sales Office Code]
    )
SET @UpdateRecordCount = @UpdateRecordCount + @@ROWCOUNT;

/*****INSERT*****/
INSERT INTO [dbo].[DimSalesOffice]
    (
        [Sales Office Code],
        [CCN_Key],
        [Sales Office Name (Short)],
        [Sales Office Name (Long)],
        [Sales Office City],
        [Sales Office StateProvince],
        [Sales Office Postal Code],
        [Sales Office Country Code],
        [Sales Office Address],
        [Sales Office Address (Line 2)],
        [Sales Office Name (Short - Native Language)],
        [Sales Office Name (Long - Native Language)],
        [Sales Office Address (Native Language)],
        [Sales Office Address (Line 2 - Native Language)],
        [Sales Office City (Native Language)],
        [Sales Office StateProvince (Native Language)],
        [Sales Office Region],
        [Native Language Code],
        [DW_Checksum],
        [DW_Hashbytes],
        [Source_ModifiedOn],
        [DW_ModifiedOn],
        [DW_IsDeleted?],
        [DW_CreatedOn]
    )
SELECT
    [Source].[Sales Office Code],
    [Source].[CCN_Key],
    [Source].[Sales Office Name (Short)],
    [Source].[Sales Office Name (Long)],
    [Source].[Sales Office City],
    [Source].[Sales Office StateProvince],
    [Source].[Sales Office Postal Code],
    [Source].[Sales Office Country Code],
    [Source].[Sales Office Address],
    [Source].[Sales Office Address (Line 2)],
    [Source].[Sales Office Name (Short - Native Language)],
    [Source].[Sales Office Name (Long - Native Language)],
    [Source].[Sales Office Address (Native Language)],
    [Source].[Sales Office Address (Line 2 - Native Language)],
    [Source].[Sales Office City (Native Language)],
    [Source].[Sales Office StateProvince (Native Language)],
    [Source].[Sales Office Region],
    [Source].[Native Language Code],
    [DW_Checksum] = 
        CHECKSUM(
            [Source].[CCN_Key],
            [Source].[Sales Office Name (Short)],
            [Source].[Sales Office Name (Long)],
            [Source].[Sales Office City],
            [Source].[Sales Office StateProvince],
            [Source].[Sales Office Postal Code],
            [Source].[Sales Office Country Code],
            [Source].[Sales Office Address],
            [Source].[Sales Office Address (Line 2)],
            [Source].[Sales Office Name (Short - Native Language)],
            [Source].[Sales Office Name (Long - Native Language)],
            [Source].[Sales Office Address (Native Language)],
            [Source].[Sales Office Address (Line 2 - Native Language)],
            [Source].[Sales Office City (Native Language)],
            [Source].[Sales Office StateProvince (Native Language)],
            [Source].[Sales Office Region],
            [Source].[Native Language Code],
            0
        ),
    [DW_Hashbytes] = 
        HASHBYTES(
            'SHA2_256',
            ISNULL(CAST([Source].[CCN_Key] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Postal Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Country Code] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Short - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Name (Long - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Address (Line 2 - Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office City (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office StateProvince (Native Language)] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Sales Office Region] AS NVARCHAR(MAX)), '') + '|'
            + ISNULL(CAST([Source].[Native Language Code] AS NVARCHAR(MAX)), '') + '|'
            + '0'
        ),
    [Source_ModifiedOn] = NULL,
    [DW_ModifiedOn] = GETUTCDATE(),
    [DW_IsDeleted?] = 0,
    [DW_CreatedOn] = GETUTCDATE()
FROM [changeLog].[DimSalesOffice] [Source]
WHERE NOT EXISTS
    (
        SELECT 1
        FROM [dbo].[DimSalesOffice]
        WHERE [Source].[Sales Office Code] = [DimSalesOffice].[Sales Office Code]
    )
SET @InsertRecordCount = @@ROWCOUNT;

SELECT [Update Record Count] = @UpdateRecordCount, [Insert Record Count] = @InsertRecordCount;

Antyan

Asked: 2021-12-16 10:03:59 +0800 CST

SQL Server 2017; Solução de problemas de memória; Não há memória de sistema suficiente no pool de recursos 'interno' para executar esta consulta

Estamos executando um SQL Server 2017 local para dar suporte a um banco de dados datawarehouse. O banco de dados é carregado em um agendamento por meio do SSIS em grande parte pelo uso de tabelas de preparo e da função MERGE. Recentemente, começamos a ver o erro "Há memória de sistema insuficiente no pool de recursos 'interno' para executar esta consulta". Ele se tornou cada vez mais desenfreado nas últimas semanas.

Nós tentamos:

Desativando o armazenamento de consultas
Atualizando o SQL Server através do CU27
Rastreando a consulta (sem bons resultados)
Executando o máximo de relatórios que pudermos encontrar para identificar o problema

Alterações recentes que se correlacionam com o tempo do erro:

Ativando o repositório de consultas
Adição de um grande número de índices

Outra informação:

Temos 32gb de memória no servidor e alocamos 26gb para o sql server
A tabela TARGET do MERGE é um CLUSTERED COLUMNSTORE INDEX
A tabela SOURCE do MERGE é um HEAP
Com o tempo/várias falhas, a tabela SOURCE que preparou os registros alterados aumentou para mais de 200.000 registros. A tabela TARGET tem aproximadamente 10 milhões de linhas.

Qualquer ajuda seria apreciada. Eu vasculhei a internet nos últimos dias procurando alguma orientação. Tudo o que vi até agora é:

Atualizar versão do SQL
Modifique sua consulta
Adicionar memória ao servidor

DECLARAÇÃO DE FUSÃO:

 DROP TABLE IF EXISTS #Changes;
DROP TABLE IF EXISTS #TransformedChanges;

CREATE TABLE #Changes
(
    [Change Type] VARCHAR(100)
);
MERGE [dbo].[FactOrderLine] AS TARGET
USING ( SELECT
[FactOrderLine].[OrderLine_Key],
[FactOrderLine].[BookedDate_Date_Key],
[FactOrderLine].[BookedDate_Time_Key],
[FactOrderLine].[Account_Key],
[FactOrderLine].[CCN_Key],
[FactOrderLine].[BillTo_SalesOffice_Key],
[FactOrderLine].[BillTo_Territory_Key],
[FactOrderLine].[ShipTo_SalesOffice_Key],
[FactOrderLine].[ShipTo_Territory_Key],
[FactOrderLine].[AssemblyLocation_Key],
[FactOrderLine].[ProductDivision_Key],
[FactOrderLine].[Product_Key],
[FactOrderLine].[Booked Date],
[FactOrderLine].[Ordered Quantity],
[FactOrderLine].[Unit Price - CCN],
[FactOrderLine].[Unit Price - Transaction],
[FactOrderLine].[Discount Factor],
[FactOrderLine].[Split Factor],
[FactOrderLine].[Is Split?],
[DW_Checksum] = CHECKSUM([FactOrderLine].[BookedDate_Date_Key],
[FactOrderLine].[BookedDate_Time_Key],
[FactOrderLine].[Account_Key],
[FactOrderLine].[CCN_Key],
[FactOrderLine].[BillTo_SalesOffice_Key],
[FactOrderLine].[BillTo_Territory_Key],
[FactOrderLine].[ShipTo_SalesOffice_Key],
[FactOrderLine].[ShipTo_Territory_Key],
[FactOrderLine].[AssemblyLocation_Key],
[FactOrderLine].[ProductDivision_Key],
[FactOrderLine].[Product_Key],
[FactOrderLine].[Booked Date],
[FactOrderLine].[Ordered Quantity],
[FactOrderLine].[Unit Price - CCN],
[FactOrderLine].[Unit Price - Transaction],
[FactOrderLine].[Discount Factor],
[FactOrderLine].[Split Factor],
[FactOrderLine].[Is Split?],
0)
FROM [changeLog].[FactOrderLine] ) AS SOURCE
ON [Source].[OrderLine_Key] = [Target].[OrderLine_Key]
WHEN MATCHED AND ISNULL([Source].[DW_Checksum], 0) <> ISNULL([Target].[DW_Checksum], 0) THEN UPDATE SET
[Target].[BookedDate_Date_Key] = [Source].[BookedDate_Date_Key],
[Target].[BookedDate_Time_Key] = [Source].[BookedDate_Time_Key],
[Target].[Account_Key] = [Source].[Account_Key],
[Target].[CCN_Key] = [Source].[CCN_Key],
[Target].[BillTo_SalesOffice_Key] = [Source].[BillTo_SalesOffice_Key],
[Target].[BillTo_Territory_Key] = [Source].[BillTo_Territory_Key],
[Target].[ShipTo_SalesOffice_Key] = [Source].[ShipTo_SalesOffice_Key],
[Target].[ShipTo_Territory_Key] = [Source].[ShipTo_Territory_Key],
[Target].[AssemblyLocation_Key] = [Source].[AssemblyLocation_Key],
[Target].[ProductDivision_Key] = [Source].[ProductDivision_Key],
[Target].[Product_Key] = [Source].[Product_Key],
[Target].[Booked Date] = [Source].[Booked Date],
[Target].[Ordered Quantity] = [Source].[Ordered Quantity],
[Target].[Unit Price - CCN] = [Source].[Unit Price - CCN],
[Target].[Unit Price - Transaction] = [Source].[Unit Price - Transaction],
[Target].[Discount Factor] = [Source].[Discount Factor],
[Target].[Split Factor] = [Source].[Split Factor],
[Target].[Is Split?] = [Source].[Is Split?],
[Target].[DW_Checksum] = [Source].[DW_Checksum],
[Target].[DW_ModifiedOn] = GETUTCDATE(),
[Target].[DW_IsDeleted?] = 0
WHEN NOT MATCHED BY TARGET THEN INSERT
(
[OrderLine_Key],
[BookedDate_Date_Key],
[BookedDate_Time_Key],
[Account_Key],
[CCN_Key],
[BillTo_SalesOffice_Key],
[BillTo_Territory_Key],
[ShipTo_SalesOffice_Key],
[ShipTo_Territory_Key],
[AssemblyLocation_Key],
[ProductDivision_Key],
[Product_Key],
[Booked Date],
[Ordered Quantity],
[Unit Price - CCN],
[Unit Price - Transaction],
[Discount Factor],
[Split Factor],
[Is Split?],
[DW_IsDeleted?], [DW_Checksum], [Source_ModifiedOn], [DW_ModifiedOn], [DW_CreatedOn] ) VALUES (
[Source].[OrderLine_Key],
[Source].[BookedDate_Date_Key],
[Source].[BookedDate_Time_Key],
[Source].[Account_Key],
[Source].[CCN_Key],
[Source].[BillTo_SalesOffice_Key],
[Source].[BillTo_Territory_Key],
[Source].[ShipTo_SalesOffice_Key],
[Source].[ShipTo_Territory_Key],
[Source].[AssemblyLocation_Key],
[Source].[ProductDivision_Key],
[Source].[Product_Key],
[Source].[Booked Date],
[Source].[Ordered Quantity],
[Source].[Unit Price - CCN],
[Source].[Unit Price - Transaction],
[Source].[Discount Factor],
[Source].[Split Factor],
[Source].[Is Split?],
0,
[Source].[DW_Checksum],NULL,GETUTCDATE(),GETUTCDATE()
)
OUTPUT $action INTO #Changes;
CREATE TABLE #TransformedChanges
    (
        [Update Record Count] INT,
        [Insert Record Count] INT
    );
INSERT INTO #TransformedChanges
SELECT *
FROM
    (
        SELECT 
            TRIM(#Changes.[Change Type])+' Record Count' AS [Change Type],
            COUNT(*) AS [Record Count]
        FROM #Changes
        GROUP BY TRIM(#Changes.[Change Type])+' Record Count'
    ) A
PIVOT
    (
        SUM([Record Count])
        FOR A.[Change Type] IN ([Update Record Count], [Insert Record Count])
    ) [B];
DECLARE @TransformationChecker INT;
SET @TransformationChecker = (SELECT COUNT(*) FROM #TransformedChanges);
IF @TransformationChecker = 0 INSERT INTO #TransformedChanges VALUES(0,0);
SELECT ISNULL([Update Record Count], 0) [Update Record Count], ISNULL([Insert Record Count], 0) [Insert Record Count] FROM #TransformedChanges;

DROP TABLE IF EXISTS #Changes;
DROP TABLE IF EXISTS #TransformedChanges;

Estratégia de indexação ideal para atualizações de Datawarehouse e Data Lake

SQL Server 2017; Solução de problemas de memória; Não há memória de sistema suficiente no pool de recursos 'interno' para executar esta consulta

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Antyan's questions