Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to SQL Server Indexes: Step 7, Filtered Indexes Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 7, Filtered Indexes
Post #1063683
Posted Thursday, February 17, 2011 10:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 8:18 PM
Points: 831, Visits: 1,588
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
Post #1065818
Posted Tuesday, February 22, 2011 5:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:11 AM
Points: 216, Visits: 273
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
Post #1067521
Posted Wednesday, June 29, 2011 1:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 6:30 AM
Points: 9, Visits: 77
Something wrong with the listings....
Post #1133468
Posted Tuesday, August 23, 2011 10:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 31, 2013 9:20 PM
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!
Post #1164409
Posted Wednesday, August 24, 2011 2:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 23, 2011 2:36 AM
Points: 4, 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.
Post #1164500
Posted Wednesday, August 24, 2011 4:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:06 AM
Points: 330, Visits: 2,249
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.
Post #1164534
Posted Wednesday, August 24, 2011 6:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691
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?
Post #1164609
Posted Wednesday, August 24, 2011 7:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:27 AM
Points: 1,639, Visits: 1,984
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.
Post #1164691
Posted Wednesday, August 24, 2011 9:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:39 PM
Points: 3,120, Visits: 792
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/
Post #1164779
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse