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
Related Q&A from Adam Machanic
Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the ... Continue Reading
Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic ... Continue Reading
SQL Server 2005 and T-SQL do have differences. The advantages and disadvantages would depend user environment as described by SQL Server 2005 expert... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.