Return to Answer

Risking ridicule from some of the biggest names in the SQL Server community, I'm going to stick my neck out and say, nope.

In order for your query to be SARGable, you'd have to basically construct a query that can pinpoint a starting row in a range of consecutive rows in an index. With the index ix_dates, the rows are not ordered by the date difference between DateCol1 and DateCol2, so your target rows could be spread out anywhere in the index.

Self-joins, multiple passes, etc. all have in common that they include at least one Index Scan, although a (nested loop) join may well use an Index Seek. But I can't see how it would be possible to eliminate the Scan.

As for getting more accurate row estimates, there are no statistics on the date difference.

The following, fairly ugly recursive CTE construct does technically eliminate scanning the whole table, although it introduces a Nested Loop Join and a (potentially very large) number of Index Seeks.

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);

It creates an Index Spool containing every DateCol1 in the table, then performs an Index Seek (range scan) for each of those DateCol1 and DateCol2 that are at least 48 days forward.

More IOs, slightly longer execution time, row estimate is still way off, and zero chance of parallelization because of the recursion: I'm guessing this query could possibly be useful if you have a very large number of values within relatively few distinct, consecutive DateCol1 (keeping the number of Seeks down).

Edit: added maxrecursion hint to the CTE and a minor tweak in the inital TOP query to handle "special" values in the DateCol1 column (like 9999-12-31). Thanks, @PaulWhite.

Risking ridicule from some of the biggest names in the SQL Server community, I'm going to stick my neck out and say, nope.

In order for your query to be SARGable, you'd have to basically construct a query that can pinpoint a starting row in a range of consecutive rows in an index. With the index ix_dates, the rows are not ordered by the date difference between DateCol1 and DateCol2, so your target rows could be spread out anywhere in the index.

Self-joins, multiple passes, etc. all have in common that they include at least one Index Scan, although a (nested loop) join may well use an Index Seek. But I can't see how it would be possible to eliminate the Scan.

As for getting more accurate row estimates, there are no statistics on the date difference.

The following, fairly ugly recursive CTE construct does technically eliminate scanning the whole table, although it introduces a Nested Loop Join and a (potentially very large) number of Index Seeks.

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);

It creates an Index Spool containing every DateCol1 in the table, then performs an Index Seek (range scan) for each of those DateCol1 and DateCol2 that are at least 48 days forward.

More IOs, slightly longer execution time, row estimate is still way off, and zero chance of parallelization because of the recursion: I'm guessing this query could possibly be useful if you have a very large number of values within relatively few distinct, consecutive DateCol1 (keeping the number of Seeks down).

Risking ridicule from some of the biggest names in the SQL Server community, I'm going to stick my neck out and say, nope.

In order for your query to be SARGable, you'd have to basically construct a query that can pinpoint a starting row in a range of consecutive rows in an index. With the index ix_dates, the rows are not ordered by the date difference between DateCol1 and DateCol2, so your target rows could be spread out anywhere in the index.

Self-joins, multiple passes, etc. all have in common that they include at least one Index Scan, although a (nested loop) join may well use an Index Seek. But I can't see how it would be possible to eliminate the Scan.

As for getting more accurate row estimates, there are no statistics on the date difference.

The following, fairly ugly recursive CTE construct does technically eliminate scanning the whole table, although it introduces a Nested Loop Join and a (potentially very large) number of Index Seeks.

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 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);

It creates an Index Spool containing every DateCol1 in the table, then performs an Index Seek (range scan) for each of those DateCol1 and DateCol2 that are at least 48 days forward.

More IOs, slightly longer execution time, row estimate is still way off, and zero chance of parallelization because of the recursion: I'm guessing this query could possibly be useful if you have a very large number of values within relatively few distinct, consecutive DateCol1 (keeping the number of Seeks down).

Risking ridicule from some of the biggest names in the SQL Server community, I'm going to stick my neck out and say, nope.

In order for your query to be SARGable, you'd have to basically construct a query that can pinpoint a starting row in a range of consecutive rows in an index. With the index ix_dates, the rows are not ordered by the date difference between DateCol1 and DateCol2, so your target rows could be spread out anywhere in the index.

Self-joins, multiple passes, etc. all have in common that they include at least one Index Scan, although a (nested loop) join may well use an Index Seek. But I can't see how it would be possible to eliminate the Scan.

As for getting more accurate row estimates, there are no statistics on the date difference.

The following, fairly ugly recursive CTE construct does technically eliminate scanning the whole table, although it introduces a Nested Loop Join and a (potentially very large) number of Index Seeks.

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);

It creates an Index Spool containing every DateCol1 in the table, then performs an Index Seek (range scan) for each of those DateCol1 and DateCol2 that are at least 48 days forward.

More IOs, slightly longer execution time, row estimate is still way off, and zero chance of parallelization because of the recursion: I'm guessing this query could possibly be useful if you have a very large number of values within relatively few distinct, consecutive DateCol1 (keeping the number of Seeks down).

Edit: added maxrecursion hint to the CTE and a minor tweak in the inital TOP query to handle "special" values in the DateCol1 column (like 9999-12-31). Thanks, @PaulWhite.

Risking ridicule from some of the biggest names in the SQL Server community, I'm going to stick my neck out and say, nope.

In order for your query to be SARGable, you'd have to basically construct a query that can pinpoint a starting row in a range of consecutive rows in an index. With the index ix_dates, the rows are not ordered by the date difference between DateCol1 and DateCol2, so your target rows could be spread out anywhere in the index.

Self-joins, multiple passes, etc. all have in common that they include at least one Index Scan, although a (nested loop) join may well use an Index Seek. But I can't see how it would be possible to eliminate the Scan.

As for getting more accurate row estimates, there are no statistics on the date difference.

The following, fairly ugly recursive CTE construct does technically eliminate scanning the whole table, although it introduces a Nested Loop Join and a (potentially very large) number of Index Seeks.

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 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);

It creates an Index Spool containing every DateCol1 in the table, then performs an Index Seek (range scan) for each of those DateCol1 and DateCol2 that are at least 48 days forward.

More IOs, slightly longer execution time, row estimate is still way off, and zero chance of parallelization because of the recursion: I'm guessing this query could possibly be useful if you have a very large number of values within relatively few distinct, consecutive DateCol1 (keeping the number of Seeks down).

Risking ridicule from some of the biggest names in the SQL Server community, I'm going to stick my neck out and say, nope.

In order for your query to be SARGable, you'd have to basically construct a query that can pinpoint a starting row in a range of consecutive rows in an index. With the index ix_dates, the rows are not ordered by the date difference between DateCol1 and DateCol2, so your target rows could be spread out anywhere in the index.

Self-joins, multiple passes, etc. all have in common that they include at least one Index Scan, although a (nested loop) join may well use an Index Seek. But I can't see how it would be possible to eliminate the Scan.

As for getting more accurate row estimates, there are no statistics on the date difference.

The following, fairly ugly recursive CTE construct does technically eliminate scanning the whole table, although it introduces a Nested Loop Join and a (potentially very large) number of Index Seeks.

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 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);

It creates an Index Spool containing every DateCol1 in the table, then performs an Index Seek (range scan) for each of those DateCol1 and DateCol2 that are at least 48 days forward.

More IOs, slightly longer execution time, row estimate is still way off, and zero chance of parallelization because of the recursion: I'm guessing this query could possibly be useful if you have a very large number of values within relatively few distinct, consecutive DateCol1 (keeping the number of Seeks down).