Cláusula SARGable WHERE para duas colunas de data

Apenas adicionando isso rapidamente para que exista como uma resposta (embora eu saiba que não é a resposta que você deseja).

Uma coluna computada indexada geralmente é a solução certa para esse tipo de problema.

Isto:

• torna o predicado uma expressão indexável
• permite que estatísticas automáticas sejam criadas para uma melhor estimativa de cardinalidade
• não precisa ocupar nenhum espaço na tabela base

Para esclarecer esse último ponto, a coluna calculada não precisa ser persistida neste caso:

-- Note: not PERSISTED, metadata change only
ALTER TABLE #sargme
ADD DayDiff AS DATEDIFF(DAY, DateCol1, DateCol2);

-- Index the expression
CREATE NONCLUSTERED INDEX index_name
ON #sargme (DayDiff)
INCLUDE (DateCol1, DateCol2);

Agora a consulta:

SELECT
    S.ID,
    S.DateCol1,
    S.DateCol2,
    DATEDIFF(DAY, S.DateCol1, S.DateCol2)
FROM
    #sargme AS S
WHERE
    DATEDIFF(DAY, S.DateCol1, S.DateCol2) >= 48;

...dá o seguinte plano trivial :

Como disse Martin Smith, se você tiver conexões usando as opções de conjunto erradas, poderá criar uma coluna regular e manter o valor calculado usando gatilhos.

Tudo isso realmente importa (desafio do código à parte) se houver um problema real a ser resolvido, é claro, como Aaron diz em sua resposta .

É divertido pensar nisso, mas não conheço nenhuma maneira de conseguir o que você deseja razoavelmente, dadas as restrições da questão. Parece que qualquer solução ideal exigiria uma nova estrutura de dados de algum tipo; o mais próximo que temos é a aproximação de 'índice de função' fornecida por um índice em uma coluna computada não persistente como acima.

Arriscando-me a ser ridicularizado por alguns dos maiores nomes da comunidade do SQL Server, vou arriscar o pescoço e dizer não.

Para que sua consulta seja SARGable, você basicamente teria que construir uma consulta que pudesse identificar uma linha inicial em um intervalo de linhas consecutivas em um índice. Com o índice ix_dates, as linhas não são ordenadas pela diferença de data entre DateCol1e DateCol2, portanto, suas linhas de destino podem ser espalhadas em qualquer lugar do índice.

Autojunções, passagens múltiplas, etc., todos têm em comum que incluem pelo menos uma varredura de índice, embora uma junção (loop aninhado) possa usar uma busca de índice. Mas não vejo como seria possível eliminar o Scan.

Quanto a obter estimativas de linha mais precisas, não há estatísticas sobre a diferença de datas.

A construção CTE recursiva bastante feia a seguir elimina tecnicamente a varredura de toda a tabela, embora introduza uma junção de loop aninhada e um número (potencialmente muito grande) de buscas de índice.

DECLARE @from date, @count int;
SELECT TOP 1 @from=DateCol1 FROM #sargme ORDER BY DateCol1;
SELECT TOP 1 @count=DATEDIFF(day, @from, DateCol1) FROM #sargme WHERE DateCol1<=DATEADD(day, -48, {d '9999-12-31'}) ORDER BY DateCol1 DESC;

WITH cte AS (
    SELECT 0 AS i UNION ALL
    SELECT i+1 FROM cte WHERE i<@count)

SELECT b.*
FROM cte AS a
INNER JOIN #sargme AS b ON
    b.DateCol1=DATEADD(day, a.i, @from) AND
    b.DateCol2>=DATEADD(day, 48+a.i, @from)
OPTION (MAXRECURSION 0);

Ele cria um spool de índice contendo cada DateCol1na tabela e, em seguida, executa uma busca de índice (varredura de intervalo) para cada um deles DateCol1e DateCol2que são pelo menos 48 dias à frente.

Mais E/S, tempo de execução um pouco mais longo, estimativa de linha ainda distante e chance zero de paralelização por causa da recursão: acho que essa consulta pode ser útil se você tiver um número muito grande de valores em relativamente poucos distintos, consecutivos DateCol1(mantendo o número de Seeks baixo).

Eu tentei um monte de variações malucas, mas não encontrei nenhuma versão melhor do que uma das suas. O principal problema é que seu índice se parece com isso em termos de como date1 e date2 são classificados juntos. A primeira coluna ficará em uma bela linha arquitetada, enquanto o espaço entre elas será muito irregular. Você quer que isso pareça mais com um funil do que realmente será:

Date1    Date2
-----    -------
*             *
*             *
*              *
 *       * 
 *        *
 *         *
  *      *
  *           *

Não há realmente nenhuma maneira de tornar isso pesquisável para um determinado delta (ou intervalo de deltas) entre os dois pontos. E quero dizer uma única busca que é executada uma vez + uma varredura de alcance, não uma busca que é executada para cada linha. Isso envolverá uma verificação e/ou classificação em algum momento, e essas são coisas que você deseja evitar obviamente. É uma pena que você não possa usar expressões como DATEADD/ DATEDIFFem índices filtrados ou realizar quaisquer possíveis modificações de esquema que permitam uma classificação no produto da diferença de data (como calcular o delta no momento da inserção/atualização). Como está, esse parece ser um daqueles casos em que uma varredura é, na verdade, o método de recuperação ideal.

Você disse que esta consulta não era divertida, mas se você olhar mais de perto, esta é de longe a melhor (e seria ainda melhor se você deixasse de fora a saída escalar de computação):

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

A razão é que evitar DATEDIFFpotencialmente reduz alguma CPU em comparação com um cálculo apenas contra a coluna de chave não principal no índice e também evita algumas conversões implícitas desagradáveis ​​​​para datetimeoffset(7)(não me pergunte por que elas estão lá, mas estão). Aqui está a DATEDIFFversão:

<Predicado>
<ScalarOperator ScalarString="datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[splunge].[dbo].[sargme].[DateCol1] as [s].[DateCol1],0),CONVERT_IMPLICIT(datetimeoffset( 7),[splunge].[dbo].[sargme].[DateCol2] as [s].[DateCol2],0))>=(48)">

E aqui está o sem DATEDIFF:

<Predicado>
<ScalarOperator ScalarString="[splunge].[dbo].[sargme].[DateCol2] as [s].[DateCol2]>=dateadd(day,(48),[splunge].[dbo].[ sargme].[DateCol1] as [s].[DateCol1])">

Também encontrei resultados um pouco melhores em termos de duração quando alterei o índice para apenas incluir DateCol2 (e quando ambos os índices estavam presentes, o SQL Server sempre escolheu aquele com uma chave e uma coluna de inclusão versus várias chaves). Para esta consulta, como temos que varrer todas as linhas para encontrar o intervalo de qualquer maneira, não há benefício em ter a segunda coluna de data como parte da chave e classificada de qualquer maneira. E, embora eu saiba que não podemos obter uma busca aqui, há algo inerentemente bom em não impedir a capacidade de obter uma forçando cálculos na coluna de chave principal e apenas executá-los nas colunas secundárias ou incluídas.

Se fosse eu, e desistisse de encontrar a solução sargable, sei qual escolheria - aquela que faz o SQL Server dar menos trabalho (mesmo que o delta seja quase inexistente). Ou melhor ainda, eu relaxaria minhas restrições sobre mudança de esquema e coisas do gênero.

E o quanto tudo isso importa? Não sei. Fiz a tabela com 10 milhões de linhas e todas as variações de consulta acima ainda foram concluídas em menos de um segundo. E isso está em uma VM em um laptop (concedido, com SSD).

Todas as maneiras que pensei para tornar essa cláusula WHERE sargable são complexas e parecem trabalhar em busca de índice como um objetivo final, e não como um meio. Então, não, não acho que seja (pragmaticamente) possível.

Eu não tinha certeza se "não alterar a estrutura da tabela" significava nenhum índice adicional. Aqui está uma solução que evita completamente as varreduras de índice, mas resulta em MUITAS buscas de índice separadas, ou seja, uma para cada data DateCol1 possível no intervalo Min/Max de valores de data na tabela. (Ao contrário de Daniel, que resulta em uma busca para cada data distinta que realmente aparece na tabela). Teoricamente é candidato ao paralelismo porque evita a recursão. Mas, honestamente, é difícil ver uma distribuição de dados em que isso seja mais rápido do que apenas digitalizar e fazer DATEDIFF. (Talvez um DOP muito alto?) E... o código é feio. Acho que esse esforço conta como um "exercício mental".

--Add this index to avoid the scan when determining the @MaxDate value
--CREATE NONCLUSTERED INDEX [ix_dates2] ON [#sargme] ([DateCol2]);
DECLARE @MinDate DATE, @MaxDate DATE;
SELECT @MinDate=DateCol1 FROM (SELECT TOP 1 DateCol1 FROM #sargme ORDER BY DateCol1 ASC) ss;
SELECT @MaxDate=DateCol2 FROM (SELECT TOP 1 DateCol2 FROM #sargme ORDER BY DateCol2 DESC) ss;

--Used 44 just to get a few more rows to test my logic
DECLARE @DateDiffSearchValue INT = 44, 
    @MinMaxDifference INT = DATEDIFF(DAY, @MinDate, @MaxDate);

--basic data profile in the table
SELECT [MinDate] = @MinDate, 
        [MaxDate] = @MaxDate, 
        [MinMaxDifference] = @MinMaxDifference, 
        [LastDate1SearchValue] = DATEADD(DAY, 0-@DateDiffSearchValue, @MaxDate);

;WITH rn_base AS (
SELECT [col1] = 0
        UNION ALL SELECT 0
        UNION ALL SELECT 0
        UNION ALL SELECT 0
),
rn_1 AS (
    SELECT t0.col1 FROM rn_base t0
        CROSS JOIN rn_base t1
        CROSS JOIN rn_base t2
        CROSS JOIN rn_base t3
),
rn_2 AS (
    SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM rn_1 t0
        CROSS JOIN rn_1 t1
),
candidate_searches AS (
    SELECT 
        [Date1_EqualitySearch] = DATEADD(DAY, t.rn-1, @MinDate),
        [Date2_RangeSearch] = DATEADD(DAY, t.rn-1+@DateDiffSearchValue, @MinDate)
    FROM rn_2 t
    WHERE DATEADD(DAY, t.rn-1, @MinDate) <= DATEADD(DAY, 0-@DateDiffSearchValue, @MaxDate)
    /* Of course, ignore row-number values that would result in a
       Date1_EqualitySearch value that is < @DateDiffSearchValue days before @MaxDate */
)
--select * from candidate_searches

SELECT c.*, xapp.*, dd_rows = DATEDIFF(DAY, xapp.DateCol1, xapp.DateCol2)
FROM candidate_searches c
    cross apply (
        SELECT t.*
        FROM #sargme t
        WHERE t.DateCol1 = c.date1_equalitysearch
        AND t.DateCol2 >= c.date2_rangesearch
    ) xapp
ORDER BY xapp.ID asc --xapp.DateCol1, xapp.DateCol2 

_{Resposta do Community Wiki originalmente adicionada pelo autor da pergunta como uma edição da pergunta}

Depois de deixar isso parado um pouco e algumas pessoas realmente inteligentes entrarem em contato, meu pensamento inicial sobre isso parece correto: não há uma maneira sã e SARGable de escrever essa consulta sem adicionar uma coluna, seja computada ou mantida por meio de algum outro mecanismo, ou seja, gatilhos.

Eu tentei algumas outras coisas e tenho algumas outras observações que podem ou não ser interessantes para quem está lendo.

First, re-running the setup using a regular table rather than a temp table

• Even though I know their reputation, I wanted to try multi-column statistics out. They were useless.
• I wanted to see which statistics were used

Here's the new setup:

USE [tempdb]
SET NOCOUNT ON  

DBCC FREEPROCCACHE

IF OBJECT_ID('tempdb..sargme') IS NOT NULL
BEGIN
DROP TABLE sargme
END

SELECT TOP 1000
IDENTITY (BIGINT, 1,1) AS ID,
CAST(DATEADD(DAY, [m].[severity] * -1, GETDATE()) AS DATE) AS [DateCol1],
CAST(DATEADD(DAY, [m].[severity], GETDATE()) AS DATE) AS [DateCol2]
INTO sargme
FROM sys.[messages] AS [m]

ALTER TABLE [sargme] ADD CONSTRAINT [pk_whatever] PRIMARY KEY CLUSTERED ([ID])
CREATE NONCLUSTERED INDEX [ix_dates] ON [sargme] ([DateCol1], [DateCol2])

CREATE STATISTICS [s_sargme] ON [sargme] ([DateCol1], [DateCol2])

Then, running the first query, it uses the ix_dates index, and scans, just like before. No change here. This seems redundant, but stick with me.

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [sargme] AS [s]
WHERE
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) >= 48

Run the CTE query again, still the same...

WITH    [x] AS ( SELECT
                * ,
                DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) AS [ddif]
               FROM
                [sargme] AS [s])
     SELECT
        *
     FROM
        [x]
     WHERE
        [x].[ddif] >= 48;

Alright! Run the not-even-half-sargable query again:

SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

Now add the computed column, and re-run all three, along with the query that hits the computed column:

ALTER TABLE [sargme] ADD [ddiff] AS 
DATEDIFF(DAY, DateCol1, DateCol2) PERSISTED

CREATE NONCLUSTERED INDEX [ix_dates2] ON [sargme] ([ddiff], [DateCol1], [DateCol2])

SELECT [s].[ID] ,
       [s].[DateCol1] ,
       [s].[DateCol2]
FROM [sargme] AS [s]
WHERE [ddiff] >= 48

If you stuck with me to here, thanks. This is the interesting observation portion of the post.

Running a query with an undocumented trace flag by Fabiano Amorim to see which statistics each query used is pretty cool. Seeing that no plan touched a statistics object until the computed column was created and indexed seemed odd.

Heck, even the query that hit the computed column ONLY didn't touch a statistics object until I ran it a few times and it got simple parameterization. So even though they all initially scanned the ix_dates index, they used hard-coded cardinality estimates (30% of the table) rather than any statistics object available to them.

Um outro ponto que levantou uma sobrancelha aqui é que, quando adicionei apenas o índice não clusterizado, todos os planos de consulta examinaram o HEAP, em vez de usar o índice não clusterizado em ambas as colunas de data.

Obrigado a todos que responderam. Vocês são todos maravilhosos.