Tip of the Week SQL Server filtered indexes

Did you know that we can use a special index type in SQL Server called filtered index? It allows the creation of indexes on part of the table information, minimizing its size and increasing the ability to execute queries.

They are widely used when we have tables with many information, where we usually do a lot of queries with specific "where" clauses.

For example, we have a table with client information: A and B.

We create an index for each client, keeping each index smaller, increasing performance.

In a normal scenario, a single index is created with all the information.

It can be used for example for multi-tenancy scenarios in the same table.

Advantages of using filtered indexes:

• Reduced index maintenance costs - insert, update, delete, and merge operations are not as expensive, since a filtered index is smaller and does not require as much time for reorganization or rebuilding.
• Reduced storage cost.
• More accurate statistics.
• Optimized query performance.

Know more about SQL Server filtered indexes in Brent Ozar's post. Click here and enjoy.