And that works great. Except that error entries in our table make up only 2.5% of our total rows. Assuming we’ll never need to query WHERE ErrorType = ‘Warning’ , this index is using a lot of unnecessary space.

Filtered JSON Indexes…

A filtered index should benefit us significantly here: it should save us space (since it won’t include all of those warning rows) and it should make our INSERT queries into this table faster since the index won’t need to be maintained for our non-“Error” rows.

If we compare the sizes of our nonclustered index to our filtered index, you’ll immediately that the filtered index is significantly smaller:

However, our table size is now slightly larger because of the added table column.

Conclusion

So what do you do if you run into this situation? Well, if the ratio of undesired records to desired records is large like in the example above, you might want to make a permanent column to include in your filtered index – the size/performance benefit is certainly there. This does mean that your table size will be larger (additional column) but performance will be faster if your queries are able to use the smaller filtered index.