SQL server filtered indexes

Question

Hi,

I have a table that is quite modest in size at the moment but will grow to a few million rows so I’m looking to build things right from the start.

The table contains 15 or so column containing information for agent telephony performance but that is not really relevant. One of the queries that is run on this table looks at data from 4 columns for the last month by agent_ID. Of course normally I would just a covering index on Agent_ID and date including the 4 columns and be done with it but I’m looking to see if I can be a bit smarter than that.

As I’m using SQL server 2008R2 I thought I would take a look at filtered indexes and only have the index populated with data from the current month. The problem is I cant seem to get it to accept anything other than a hard value on the filter.

Am I barking up the wrong tree with this one, can you even do that in SQL server without having to resort to having two tables or some other kind of partitioning?

EDIT: Thanks to Marcus I know know it is not possible to do this without some manual work.

To that end I have made this script that I will run at the start of each month during dark hours to move the index window on

USE [Tracker_3]
GO
DECLARE @FirstOfMonth datetime
DECLARE @LastOfMonth datetime

declare @strSQL as varchar(max)
set @FirstOfMonth = CONVERT(smalldatetime, CONVERT(varchar(4), DATEPART(yy, GETDATE())) + '-' + CONVERT(varchar(2), DATEPART(mm, GETDATE())) + '-' + '1')
set @LastOfMonth = DATEADD(dd, -1, DATEADD(mm, +1, @FirstOfMonth))


set @strSQL='

CREATE NONCLUSTERED INDEX [tblAgent_int_data_Covering_1] ON [dbo].[tblAgent_interval_data] 
(
    [Login_ID] ASC,
    [Date] ASC
)
INCLUDE ( [i_acdtime],
[i_acwtime],
[holdacdtime],
[acdcalls])  
WHERE [date] >= ''' + convert(char(10),convert(date,@firstOfMonth)) + ''' AND [date] <= '''+ convert(char(10),convert(date,@LastOfMonth)) + '''
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = ON, ONLINE = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, 
    FILLFACTOR = 100) ON [T3_Data_2]

'

exec (@strSQL)

Answer 1

What you try to accomplish is not possible without some kind of "manual" maintenance--it's similar to the problem of indexing "Age".

I'd say the best approach is partitioning--but you will need to drop/create new partitions regularly.