I'm creating a Notification table, with the following specification:

CREATE TABLE [Notification] (
    [NotificationId] [int] NOT NULL IDENTITY (1, 1),
    [IsActive] [bit] NOT NULL,
    [TriggerKey] [nvarchar](50) NOT NULL,
    [ObjectId] [int] NOT NULL,
    [RecipientType] [tinyint] NOT NULL,
    CONSTRAINT PK_Notification PRIMARY KEY CLUSTERED 
    (
        [NotificationId] ASC
    )
)

Every time a notification gets changed, rather than updating its values, I want to create a new, "Active" notification and deactivate the previous one. But the previous notification needs to still be accessible when specified by NotificationId. So I expect to eventually have far more inactive notifications than active ones.

It will be relatively uncommon for Notifications to be added or changed. Far more common will be:

1. A query on an external table that joins with this table based on the NotificationId, and
2. A query for "Active" notifications with a given TriggerKey and ObjectId

For the first case, I figure the primary key index will suffice. For the second case, I am thinking of creating the following filtered index:

CREATE NONCLUSTERED INDEX ID开发者_如何学PythonX_Notification_ActiveTriggerObject 
ON [Notification] (IsActive, TriggerKey, ObjectId)
WHERE IsActive = 1

However, when I check the execution plan before and after creating this index, it appears to not be getting used. Why?

Update

Thanks to the article linked in the accepted answer, here's the corrected version of the index:

CREATE NONCLUSTERED INDEX IDX_Notification_ActiveTriggerObject 
ON [Notification] (TriggerKey, ObjectId)
INCLUDE (IsActive, NotificationId, RecipientType)
WHERE IsActive = 1

SELECT * means the index won't usually be used.

Your index satisfies only the WHERE clause, not the SELECT clause, so SQL Server decides the index is no use for this query. In other words, it isn't covering

This will most likely use the index

SELECT IsActive, TriggerKey, ObjectId
FROM [Notification]
WHERE IsActive = 1 AND ObjectId = 2 AND TriggerKey = 'test'

If you changed the index to this, then it covers all columns. SELECT * may use it now

CREATE NONCLUSTERED INDEX IDX_Notification_ActiveTriggerObject 
ON [Notification] (IsActive, TriggerKey, ObjectId)
INCLUDE (RecipientType, NotificationId)
WHERE IsActive = 1

If you have many (majority?) of 'active' records, then probably query estimator doesn't use that index - because query need to return all fields, accessing and scanning clustered key (table itself) is needed anyway. Try selecting only fields, contained in index - does anything change?

When you have few 'active' records (maybe below 10-30% or so, depends on rows count and many other factors), then it would be more efficient to use index to find RIDs and seek them on clustered index (table).

Another thing with I would experiment - declare your filtered index on fields (TriggerKey, ObjectId) only (if filtered index can be created this way). Putting IsActive on start of index kills selectivity IMHO.

Note that all above is speculation only - I've no experience with filtered indexes.