sql.monkey (11/4/2010)
I have a table with an unindexed date column in a table of billions of rowsI collect the lowest and highest primary keys between those dates into variables
I set two variables
declare @min-2 int
declare @max-2 int
select @min-2 = min(primarykey) where datecol => 'begindate'
select @max-2= max(primarykey) where datecol <= 'enddate'
select primarykey, datecol, x,y,z from table where primarykey between @min-2 and @max-2
works for me
You'll get a syntax error - no FROM clause in the two queries that set @min-2 and @max.
After fixing that, if the datecol column is indeed unindexed, you get two complete table scans for setting the @min-2 and @max-2 variables. You can reduce that to one scan by using
SELECT @min-2 = min(primarykey), @max-2=max(primarykey)
FROM table
WHERE datecol BETWEEN @begindate AND @enddate;
But it's still a scan. The same scan you would get if you throws away all the unnecessary logic and use
SELECT primarykey, datecol, x,y,z
FROM table
WHERE datecol BETWEEN @begindate AND @enddate;
If you check the execution plan for your query, you will probably find that is uses an index on the datecol column that you had forgotten existed.
And the objection posted by GPO is valid as well - this (useless) technique only gives the correct results if ascending key order and ascending datecol order match up completely. Which is probably only the case if one column is an IDENTITY and the other has a DEFAULT(CURRENT_TIMESTAMP) and is never ever manually changed.