Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to SQL Server Indexes: Step 7, Filtered Indexes


Stairway to SQL Server Indexes: Step 7, Filtered Indexes

Author
Message
David Durant
David Durant
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 7, Filtered Indexes
GPO
GPO
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 1920
So a filtered index reduces the number of columns and (importantly) the number of rows you need to search, whereas an indexed view... does pretty much the same thing. Where would you use one over the other? We have not moved to SQL 2008 yet but we've had great success under certain conditions, putting an indexed view over a given table. The sorts of things we look out for are the need to regularly query a small stable subset of the table using an uncomplicated where clause.

:-)

One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell

Andrew Diniz
Andrew Diniz
SSC Veteran
SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)

Group: General Forum Members
Points: 280 Visits: 293
In your article you write:

"You might be surprised to learn that changing the WHERE clause in the previous statement from "SpecialOfferID <> 1" to "SpecialOfferID = 2" will prevent SQL Server from using the filtered index. This is because SQL Server compares the WHERE clause of the SELECT statement against the WHERE clause of the CREATE INDEX statement for lexical equivalence, not for logical equivalence. Therefore, SQL Server does not realize that the filtered index covers the query."

While this is true, it only affects queries with predicates containing the same column(s) used in the filtered index predicate. Moreover, it should be pointed out that including the columns used in the filtered index predicate (SpecialOfferID in this case) either in the index key or included columns, will allow the optimizer to consider the filtered index (provided it is valid for the query predicate of course).

ie:


CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON Sales.SalesOrderDetail (ProductID,ModifiedDate)
INCLUDE (OrderQty,UnitPrice,LineTotal,SpecialOfferID) -- NOTE the addition of the predicate-column)
WHERE SpecialOfferID <>1




Is valid and will be used for the following query:


SELECT ProductID ,
ModifiedDate ,
SUM(OrderQty) 'No of Items' ,
AVG(UnitPrice) 'Avg Price' ,
SUM(LineTotal) 'Total Value'
FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 2
GROUP BY ProductID ,
ModifiedDate



Of course the optimizer may choose not to use the filtered index if the query predicate is not selective enough in which case you may decide to force the optimizer's hand with an index hint:
...WITH (INDEX(FK_ProductID_ModifiedDate)) WHERE   SpecialOfferID = 2 ...



While index hints can force a plan to utilize a filtered index (forcing even our filtered index without an 'INCLUDED SpecialOrderID' to be used) they should not substitute well-designed indexes. If the optimizer isn't selecting your filtered index, check if it's covering the query. Would a non-filtered index be valid and cover the same query? If not, try adding the column(s) used in the query predicate to the filtered index's included columns (or key if appropriate). If this doesn't help, check your cardinality estimates. Are your stats up to date? etc...

David's done a stirling job on this Stairway so it's disappointing that he's not kept to the same high standard on this level. Many more explanations and considerations on filtered indexes can be found at: http://msdn.microsoft.com/en-us/library/cc280372(v=sql.105).aspx
dronusoid
dronusoid
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 98
Something wrong with the listings....
Ross Crawford
Ross Crawford
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 98
dronusoid (6/29/2011)
Something wrong with the listings....

Quite a few typos too - happy to proof-read articles if it would help!
Steve.Cornelius
Steve.Cornelius
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 27
I was disappointed to find that the first example of a filtered index was over half way down the article, that put the whole top half not quite out of context, but sort of out of focus. Also an explanation of the lexical v logical limitation might have been useful before getting us interested in a feature that is basically crippled beyond virtually all usefulness by that one limitation (i.e. only if you're going to use the exact query in the filtered index will it actually work, IRL that probably eliminates 90%+ of potential uses). That limitation does put the whole article out of context, because every reader for whom a logical comparison would have made it useful (Over 10x as many as for whom it is actually useful) is mentally extrapolating impossible uses for it and then you slam them down to earth with an annoying thump at the end.
peter-757102
peter-757102
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 2551
I like filtered indexes but have ran into a very annoying issue with them once.

The where clause of a filtered index cannot have OR operators in it (it does support AND, IN and NOT IN). But as soon as you want to filter using more complex conditions you hit a brick wall! Computed columns can also not be used, thus there is no way around this limitation.

A good use for unique filtered indexes is to apply a business rule on a nullable field by using a where IS NOT NULL. In many cases there is no alternative way of doing this as a regular unique index will allow for only one occurence of a NULL value. This form of filtering improves performance in multiple ways, one is uniqueness versus a regular non-unique index and the other is by accessing less indexed rows and thus operating on a smaller index.

One final comment:

Use of filtered indexes only works with non-parameterised where conditions

A where type = 1 will work, but a parameterised where type = ? with 1 as the parameter value will not. This is because the queryplan will be generated to handle any input value for the parameter. This is implicitly incompatible with a filtered index that singles out a single (set) of values.
roger.plowman
roger.plowman
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 1172
My database design requires the use of bit fields, the majority of which will be false, and it would be nice to have a filtered index for bit fields that are true.

Does this work? Or will the optimizer skip over bit field indexes?
chrisfradenburg
chrisfradenburg
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1766 Visits: 2060
roger.plowman (8/24/2011)
My database design requires the use of bit fields, the majority of which will be false, and it would be nice to have a filtered index for bit fields that are true.

Does this work? Or will the optimizer skip over bit field indexes?


Give it a try. Run this and look at the execution plan.
if OBJECT_ID('dbo.test') is not NULL
drop table dbo.test

create table dbo.test (i int primary key,
isinactive bit
)

create index FIX_Test_Active on dbo.test (i)
where isinactive = 1
go
with i as (select top 8000 ROW_NUMBER() over (order by a.object_id) id
from sys.columns a, sys.columns b)

insert into test
select id,
case
when (id % 2) = 0 then 1
else 0
end
from i

select i from dbo.test where isinactive = 1



You can also turn stats IO and CPU time on and see how much of a time savings there was.
Thomas LeBlanc
Thomas LeBlanc
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3823 Visits: 902
This article needs to be proof read by somebody, lots of mistakes in listings and missing queries to try.

Should never have brought in the 3rd normal form stuff.

Thomas

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search