SQL Server 2005 bitmap index?

Does SQL Server 2005 have a bitmap index like Oracle?
No. SQL Server 2005 does not include any new index types to support relational objects.

Oracle's bitmap index is designed to eliminate some of the cost associated with joining tables by building a 'hidden' column that pre-computes the join. This can reduce the cost of some queries against the tables.

One SQL Server feature (available since 2000) that you can use to get closer to Oracle's bitmap indexing is the indexed view. An indexed view materializes a query such that join conditions and aggregates do not need to be considered at runtime -– instead, they're computed when the view is built, and automatically kept up-to-date by the query engine as data is added or modified.

Using this feature, you should be able to see the same types of performance improvements for certain queries that might also benefit from Oracle's bitmap indexes. However, using SQL Server indexed views will incur a much greater disk storage cost than using Oracle's bitmap indexes –- instead of only pre-computing the join, the indexed view pre-computes the entire output for the query.

For more information on SQL Server indexed views, refer to this SQL Team article that covers the feature in SQL Server 2000, Indexed Views Basics in SQL Server 2000.

For more information on SQL Server 2005's enhancements for indexed views, refer to this Technet article, Improving Performance with SQL Server 2005 Indexed Views.

Do you have comments on this Ask the Expert Q&A? Let us know.

Dig Deeper on Microsoft SQL Server 2005