Can I use a SQL Filtered Index for querying "recently modified" rows
I am using SQL Server 2008-R2, but I'd be interested in a more general
answer too ...
I have a table with hundreds of millions of rows, each with a
"DateModified" field (datetime2(7))
Now I very frequently poll that table with a query like
select * Where DateModified > @P1 from [...]
Here the parameter is always recent (like within the last few minutes) and
there are probably only a few records matching that value.
It occurs to me that I am maintaining a big index on the whole table, when
I will never use the index for many of those values ... so this sounds
like a perfect use of a Filtered Index where I only index the rows that I
would possible be querying against...
But in this case what could the filter look like? My only idea was to
Filter on
where DateModified > [yesterday]
where [yesterday] is a date literal, but then I'd have to re-define the
filter periodically or the advantage of the filter would diminish over
time.
On a whim I tried ModifiedDate > DATEADD(d,-1,GETDATE()) but that gave a
nondescript error ... wasn't sure how that would be possible.
Is there some other way to accomplish this?
Then finally, if there is a way to do this, should I expect the stats to
be wildly wrong in my situation, and would that affect my query
performance?
My concern about the stats comes from this article.
I'm trying to propagate changes from one system to another with some
disconnected data ... if you'd like to suggest a completely alternate
approach to polling "DateModified", I'd be happy to consider it.
No comments:
Post a Comment