HardCode's questions

Fundo

Temos contratos de serviço que podem começar e terminar em qualquer data arbitrária. Normalmente, porém, os contratos terminam em “X anos menos um dia”. Por exemplo, intervalos de datas típicos:

• Data de início = 08/01/2018, Data de término = 07/01/2023
• Data de início = 01/06/2021, Data de término = 31/05/2023

No entanto, alguns contratos abrangem intervalos de datas muito arbitrários. Alguns são muito curtos, mas também podem durar alguns anos:

• Data de início = 30/04/2024, Data de término = 16/07/2024
• Data de início = 07/10/2024, Data de término = 30/10/2024
• Data de início = 15/03/2021, Data de término = 25/12/2025

Requerimento

Calcule o número de meses que o contrato abrange, para determinar quantas faturas mensais serão criadas durante a vigência do contrato. Suponha que as faturas sejam geradas no último dia do mês, portanto, um contrato com data de início em 15 de abril teria sua primeira fatura gerada em 30 de abril para a "Fatura de abril".

Problema

Usar apenas DATEDIFF(MONTH, @Date1, @Date2) produz resultados que nem sempre estão alinhados com o requisito.

Quando digo abaixo "produz um resultado incorreto", não quero dizer que a DATEDIFF()função esteja errada, estou dizendo que não é o resultado que preciso para minha exigência.

• PRINT DATEDIFF(MONTH, '2018-01-08', '2023-01-07')produz um resultado correto de 60 meses.
• PRINT DATEDIFF(MONTH, '2021-06-01', '2023-05-31')produz um resultado incorreto de 23 meses, onde preciso de um resultado de 24.
• PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30')produz um resultado incorreto de 0, onde preciso de um resultado de 1.
• PRINT DATEDIFF(MONTH, '2024-04-15', '2024-07-16')produz um resultado incorreto de 3, onde preciso de um resultado de 4
• PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30')produz um resultado incorreto de 0, onde preciso de um resultado de 1.
• PRINT DATEDIFF(MONTH, '2024-09-15', '2024-10-15')produz um resultado incorreto de 1, onde preciso de um resultado de 2.

Portanto, não posso apenas + 1o DATEDIFF()resultado para todos os casos. Quando um contrato começa e termina no mesmo mês do mesmo ano, DATEDIFF()produz um in

Solução atual

Esta é a minha solução atual:

PRINT CASE
    WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
        -- Special case when a contract starts and ends in the same month and year.
        THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
    WHEN MONTH(@StartDate) = MONTH(@EndDate)
        THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
    ELSE
        DATEDIFF(MONTH, @StartDate, @EndDate) + 1
    END

Esta é minha solução atual na forma de 5 testes de diferentes períodos:

DECLARE @StartDate AS DATE = '2018-01-08'
DECLARE @EndDate AS DATE = '2023-01-07'

PRINT '-------------------------------------------------------'
PRINT 'Test #1: @StartDate = 2018-01-08, @EndDate = 2023-01-07'
PRINT '-------------------------------------------------------'
PRINT ''

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #2: @StartDate = 2021-06-01, @EndDate = 2023-05-31'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2021-06-01'
SET @EndDate = '2023-05-31'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #3: @StartDate = 2024-04-30, @EndDate = 2024-07-16'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-04-30'
SET @EndDate = '2024-07-16'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #4: @StartDate = 2024-10-07, @EndDate = 2024-10-30'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-10-07'
SET @EndDate = '2024-10-30'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #5: @StartDate = 2024-09-15, @EndDate = 2024-10-15'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-09-15'
SET @EndDate = '2024-10-15'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

Resultados:

-------------------------------------------------------
Test #1: @StartDate = 2018-01-08, @EndDate = 2023-01-07
-------------------------------------------------------
The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.
60
 
-------------------------------------------------------
Test #2: @StartDate = 2021-06-01, @EndDate = 2023-05-31
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
24
 
-------------------------------------------------------
Test #3: @StartDate = 2024-04-30, @EndDate = 2024-07-16
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
4
 
-------------------------------------------------------
Test #4: @StartDate = 2024-10-07, @EndDate = 2024-10-30
-------------------------------------------------------
The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.
1
 
-------------------------------------------------------
Test #5: @StartDate = 2024-09-15, @EndDate = 2024-10-15
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
2

Os resultados correspondem aos meus requisitos, para os 5 conjuntos de intervalos de datas de teste.

Pergunta

Existe uma maneira mais confiável de fazer isso, além da minha CASEdeclaração desajeitada? Certamente não estou reinventando a roda aqui. Tenho certeza de que há mais casos extremos de intervalos de datas que ainda não pensei em testar. Estou fazendo isso da maneira mais difícil?

Cenário

Eu tenho um produto de software de middleware chamado Pro2SQL da Progress, copiando dados de um banco de dados Progress para um SQL Server em tempo real. Pro2SQL gerencia o esquema do SQL Server por meio do middleware. Os únicos índices criados no SQL Server pelo Pro2SQL são índices de ID de linha exclusivos e não agrupados. Posso ignorar completamente esse ID de linha em minhas consultas, pois ele é usado exclusivamente para simultaneidade Pro2SQL.

Algumas das tabelas contêm mais de 100 milhões de linhas, então preciso criar índices. Em cada tabela copiada do Progress para o SQL Server, existe um campo “domínio”. Este campo possui um de dois valores de três caracteres cada. Vamos chamá-los de 'ABC' ou 'XYZ'. No lado do banco de dados Progress, este domaincampo faz parte do índice.

Pergunta

Adicionar o domaincampo como parte de um índice de vários campos no lado do SQL Server agrega algum valor ou pode piorar as coisas? Por exemplo, posso ter o seguinte índice não clusterizado de vários campos em uma tabela (observe que esses campos são o que torna as linhas na tabela do banco de dados Progress exclusivas):

• isb_eu_nbr(muito seletivo)
• isb_part(muito seletivo)
• isb_serial(muito seletivo)
• isb_ref(moderadamente seletivo)
• isb_domain(extremamente não seletivo, apenas 'ABC' ou 'XYZ', a fonte da minha pergunta)

Esta tabela terá cerca de 500.000+ linhas.

Uso

Todas as consultas serão consultas somente leitura. Não haverá inserções, atualizações ou exclusões. Eles são tratados pelo middleware Pro2SQL. Em todas as minhas consultas, terei que adicionar na WHEREcláusula o seguinte:

/* WHERE <tableAbbr>_domain = 'ABC' */

/* Example */
WHERE
    /* Conditions 1 through N */
    AND isb_domain = 'ABC'

/* Another Example, with JOINed tables */
WHERE
    /* Conditions 1 through N */
    AND isb_domain = 'ABC'
    AND ls_domain = 'ABC'
    AND pt_domain = 'ABC'

outras considerações

O fornecedor, Progress, indica que, como há atualizações em tempo real acontecendo constantemente ao longo do dia útil, todas as consultas devem ser sem bloqueio. Então eles realmente sugerem(!!!) NOLOCK. Habilitamos SNAPSHOTno banco de dados, então todas as consultas conectadas diretamente ao servidor serão executadas com SET TRANSATION ISOLATION LEVEL SNAPSHOT, e todas as consultas executadas através de servidores vinculados serão executadas com SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED(aceitando os efeitos colaterais).

ATUALIZADA

Descobri que posso criar índices agrupados e não exclusivos nas tabelas do SQL Server. Além disso, com as sugestões abaixo, posso criar índices filtrados não exclusivos e não agrupados em cluster, onde o filtro estaria no campo <abc>_domainde cada tabela. Então minha pergunta se transformou em:

Devo criar índices agrupados não exclusivos que correspondam aos campos do banco de dados Progress quanto à exclusividade, menos os <abc>_domaincampos e, em seguida, criar índices filtrados não exclusivos e não agrupados que incluam o <abc>_domaincampo em cada tabela?

Eu entendo que um sim/não rígido nem sempre é viável sem realmente ver o banco de dados e os dados. Ainda farei testes de desempenho. Estou procurando mais uma resposta "Eu começaria com ...".

Cenário

Eu executo uma consulta longa e complexa que retorna mais de 30 campos e mais de 77.000 linhas no SSMS. Os resultados estão na grade. Os resultados são todos com muito texto. A grade SSMS começa a ser preenchida com resultados após alguns segundos (3 a 4) segundos, mas o tempo de execução da consulta é de 2 minutos. A maior parte desses 2 minutos são resultados anexados à grade no SSMS.

Questões)

Quando a consulta foi "concluída"? Isso foi feito nos primeiros 3 a 4 segundos quando as linhas começaram a ser anexadas à grade do SSMS? O restante dos 1 minuto e 56 segundos é apenas a atualização da interface do usuário da grade com os resultados? Se a consulta estiver "concluída" após 3 a 4 segundos, não devo tentar otimizá-la, correto? Por outro lado, se a consulta não for feita até que a execução pare no SSMS (os dois minutos completos), devo poder otimizá-la.

Temos uma tabela que serve como tabela de trilha de auditoria para um aplicativo. Vamos chamá-lo ProductAudite vamos chamar a tabela de dados para esta tabela de auditoria Products. ProductsAuditfoi poluído com cerca de 150 milhões de linhas devido a um bug. Existem cerca de 2 milhões de linhas válidas que precisam permanecer.

Então, para limpeza, eu quero:

• criar uma nova tabela,ProductAudit_TEMP
• copiar mais de 2 milhões de linhas boas
• mesa suspensaProductAudit
• renomear ProductAudit_TEMPpara ProductAudit.

Isso será muito mais rápido do que excluir os 150 milhões de linhas inválidas - mesmo em lotes.

Minha pergunta é, a tabela Productspossui gatilhos INSERT, UPDATE e DELETE, que gravam na tabela ProductAudit. Meu plano quebrará os gatilhos? Terei todos os usuários fora do sistema e até mesmo desabilitarei os gatilhos por segurança ao realizar a limpeza.

EDITAR:

Só para esclarecer, ProductAuditnão tem gatilhos, Productstem. ProductAuditestá sendo preenchido pelos gatilhos INSERT, UPDATE e DELETE que pertencem à tabela Products.