A colleague asked me the difference between Bitmap indexing and Bitmap filtering. They sound similar but there are some critical differences

 1)       Bitmap filtering was introduced in SQL Server 2008

2)       Bitmap filtering is an in-memory structure whereas Bitmap index is on disk.

3)       Bitmap filtering completes a semi – join reduction between one part of the operator tree with another part of the operator tree. In effect , this process excludes rows

4)       Bitmap filtering works well with star schemas.

5)       Bitmap indexing is a value list index . It uses a but vector or multiple bit vectors – having already completed the semi-join reduction. Bitmap indexing gets quite powerful when combined with other bit maps

Read More

SQL Server execution plan as text - SQL Server DBA

SQL Server - How to improve Execution Plan Reuse - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

Filtered statistics, T-SQL best practises for T-SQL Tuesday