- Star join query optimization
- Partitioned table parallelism
- ROW and PAGE compression
- Partition-aligned indexed views
select ProductAlternateKey, CalendarYear,sum(SalesAmount) from FactInternetSales Fact join DimTime on Fact.OrderDateKey = TimeKey join DimProduct on DimProduct.ProductKey = Fact.ProductKey where CalendarYear between 2003 and 2004 and ProductAlternateKey like 'BK%' group by ProductAlternateKey,CalendarYear
- The largest of the tables participating in the n-ary join is considered the fact table. There are additional restrictions on the minimum size of the fact table. For instance, if even the largest table is not beyond a specific size, the n-ary join is not considered a star join.
- All join conditions of the binary joins in a star join query have to be single column equality predicates. The joins have to be inner joins. While this might sound restrictive, it covers the vast majority of joins between the fact table and dimension tables on the surrogate key in typical star schemas. If a join has a more complex join condition that doesn't fit the pattern described above, the join is excluded from the star join. A five-way join, for example, can lead to a three-way star join (with two additional joins later on), if two of the joins have more complex join predicates.
- Support for the MERGE syntax in T-SQL to update, delete, or insert (dimension) data with one statement and round-trip into the database.
- Optimized logging performance of the SQL Server engine to allow for more efficient ETL.
- Grouping sets to facilitate writing aggregate decision support queries in T-SQL.
- Backup compression to reduce the I/O requirements for both full and incremental backups.
- Resource governance to control system resource allocation to different workloads.
Sunil Agarwal is a Senior Program Manager in the SQL Server Storage Engine Group at Microsoft. He is responsible for concurrency, indexes, tempdb, LOBS, supportability, and bulk import/export.
Torsten Grabs is the Senior Program Manager Lead for the Core Storage Engine in the Microsoft SQL Server team. He holds a PhD in database systems and has 10 years of experience working with SQL Server.
Dr. Joachim Hammer is a Program Manager in the Query Processing group at Microsoft. He specializes in query optimization for large-scale data warehouse applications as well as in distributed querying, ETL, and information integration.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.