Included Columns on Filtered Indexes

Rob Farley take a look at included columns on filtered indexes:

First let’s think a little about indexes in general.

An index provides an ordered structure to a set of data. (I could be pedantic and point out that reading through the data in an index from start to end might jump you from page to page in a seeming haphazard way, but still as you’re reading through pages, following the pointers from one page to the next you can be confident the data is ordered. Within each page you might even jump around to read the data in order, but there is a list showing you which parts (slots) of the page should be read in which order. There really is no point in my pedantry except to answer those equally pedantic who will comment if I don’t.)

And this order is according to the key columns – that’s the easy bit that everyone gets. It’s useful not only for being able to avoid re-ordering the data later, but also for being able to quickly locate any particular row or range of rows by those columns.

Rob does a great job of covering some of the nuances of filtered indexes.

Related Posts

The (Un)Importance of Index Fragmentation

Tibor Karaszi argues that index fragmentation is a less serious issue than most DBAs think: You know the story. Every week or so, we defragment the indexes. Many of us uses Ola Hallengren’s great script for this, some uses Maintenance Plans, and there are of course other alternatives as well. But are we just wasting […]

Read More

The Performance Hit From Ignoring Duplicate Keys

Paul White explains why there is a big performance hit when using IGNORE_DUP_KEY on clustered indexes: The IGNORE_DUP_KEY index option can be specified for both clustered and nonclustered unique indexes. Using it on a clustered index can result in much poorer performance than for a nonclustered unique index. The size of the performance difference depends on how […]

Read More

Categories

April 2019
MTWTFSS
« Mar  
1234567
891011121314
15161718192021
22232425262728
2930