Filtered Indexes

Kendra Little explains the two types of filtered indexes:

These two filtered indexes are very different – and the SQL Server optimizer can use them very differently!

While classic filtered nonclustered rowstore indexes must reliably “cover” parts of the query to be used to the optimizer, filtered nonclustered columnstore indexes may be combined with other indexes to produce a plan returning a larger range of data.

This sounds a little weird. I’ll show you what I mean using the WideWorldImporters database.

Kendra exposes an interesting difference in the two types of index and a case where filtered indexes simply fail  (though that’s not a situation you want to be in anyhow!).

Related Posts

Using Filtered Indexes

Monica Rathbun fills us in on filtered indexes: What is a filtered index?Simply it’s an index with a where clause. It is an optimized non clustered index that can be narrowed down in scope to better fit a subset of data. Example being date ranges, years, non NULLs or specific product types. I wish filtered […]

Read More

Foreign Key Checks and Index Usage Stats

Marek Masko walks through an attendee question: Last week I was speaking at SQLDay 2019 conference in Wroclaw, Poland. My session was about things you should focus on during work with Legacy Databases. One of the topics I discussed was concerning the database usage statistics collection and aggregation (mainly indexes and stored procedures). After the session, one of […]

Read More

Categories

November 2016
MTWTFSS
« Oct Dec »
 123456
78910111213
14151617181920
21222324252627
282930