Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-01-22 : 03:23:50
|
Anyone with experience of Filtered Indexes got any views of useful real-world benefits?I was never tempted by Indexed Views - seems like a bodge to me, so we didn't create a view of NOT NULL values in order to put a UNQIUE index on the column; instead we added a standard index and used a Trigger to enforce uniqueness (when not null)So having a unique filtered index on NOT NULL values will be good.Equally filtered indexes on NOT NULL for sparse columns sounds like it should save some bytes  . We have quite a few User Defined columns in our DB for clients to mis-use as they see fit. Filtered index on NOT NULL will be good for those.We also have quite a lot of IsActive (BIT) columns, and where they exist there may be some/many "inactive" rows in the table. It is rare to query Inactive rows, so presumably changing the existing indexes to be filtered indexes with IsActive=1 will help a lot (Inactive rows will be ignored at step one, rather than having to be removed later in the query). I can also stop trying to add IsActive BIT fields to the index to cover the query.Should I explicitly cast IsActive=CONVERT(bit, 1) ??I haven't spotted a need for filtered indexes for specific categories, or dates after some cutoff (as per examples in BOL), but if you have real world examples of those please say - that might spark my grey-cells a bit better! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-22 : 03:44:34
|
Just remember when using filtered indexes, the filter criteries must be used in the query too for query optimizer to choose the right index.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-01-22 : 03:50:44
|
Since a filtered index only can be done on nonclustered indexes, you also have the benefit of include to make covering indexes.CREATE UNIQUE NONCLUSTERED INDEX IX_Logins ON Security.Logins (Username) INCLUDE (Password)WHERE Username IS NOT NULL
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-22 : 04:03:36
|
| Yup, cheers for that. We would have IsActive=1 in everything (except where we don't Natch! but I don't care about the performance of those, they pretty much never happen. |
|
|
|
pedrod
Starting Member
2 Posts |
Posted - 2010-01-22 : 14:18:20
|
| You covered three good uses of filtered indexes: sparse columns, multiple NULLs in a UNIQUE constraint, and an "is active" bit. The last in particular does perform better for both queries and updates (index is updated less often, and faster).A variation I've heard on the last use is identifying more complex subsets of data that are actually worth an index. For instance, in an online store, if only some products have enough movement to warrant maintaining an index, you can create a filtered index over those specific products (e.g., CREATE INDEX ... WHERE ProductId IN (...); ).Here are two other real-world scenarios I've heard of:1. Better statistics for subsets of data: suppose you have a table storing different types of products. The characteristics of certain columns (e.g., price) will vary a lot between groups of products. Creating a filtered index over a type will create filtered statistics for that type, which can improve query performance.2. Better data locality: if you often select a subset of data together, creating a filtered index over the subset (and with the proper columns included) lets you localize all those rows together on disk. This can improve I/O performance.Pedro DeRoseProgram Manager, Microsoft SQL Server(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-22 : 14:22:31
|
| Thanks very much for that Pedro. Having confirmation that my perceived examples are actually worthwhile in the real world, and your other very useful recommended scenarios, will be a huge help. |
|
|
|
|
|
|
|
|
Filtered Index Benefits