Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.
This was last published in April 2005

Dig Deeper on Microsoft SQL Server 2005

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close