When we see an Index created with some WHERE clause then that is actually a FILTERED INDEX.
CREATE NONCLUSTERED INDEX NCI_Event
WHERE Event= 'CREATE_TABLE'
Points to remember when creating Filtered Index:
- They can be created only as Nonclustered Index
- They can be used on Views only if they are persisted views.
- They cannot be created on full-text Indexes.
Query1: Select * from DatabaseLog where Event='CREATE_TABLE'
Query2: Select * from DatabaseLog where Event='ALTER_TABLE'
Query1 is using a Filtered index and it's Query Cost is 29%. Query2 is not using Filtered Index and it's cost is 71%. See below execution plan for reference.