One of the features I like in SQL 2008 is like some people say Datacompression, but the feature I like the most is filtered index.
With this feature you can build covering indexes on a small subset of data for specialized queries. This will improve the performance a lot for the queries that will use the filtered index.
Lets say you have a table like this
CREATE TABLE [DWH].[contract](
[ID] int IDENTITY(100000000,1) NOT NULL,
[reportDate] [datetime] NOT NULL,
[contractnumber] [varchar](15) NOT NULL,
[_instrument_ID] [int] NULL,
[_package_ID] [int] NULL,
[_portfolio_ID] [int] NULL,
[_counterpart_ID] [int] NULL,
[ValueX] [datetime] NULL,
[ValueY] [datetime] NULL,
[ValueZ] [varchar](20) NULL,
[Status] int not null,
CONSTRAINT [PK_contract] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_contract_1] ON [DWH].[contract]
(
[reportDate] ASC
)
INCLUDE ( [ValueX],[ValueY],[ValueZ]
)
WHERE STATUS=10
GO
When you use the following query the engine does not have to do an index seek and a clustered index lookup:
SELECT ValueX, ValueY, ValueZ FROM dwh.contract WHERE reportdate=GETDATE() AND Status=10
SQL will solve the query with only an index seek, since all the data that is needed for the query exists in the index. And furhtermore, the index only contains records with Status=10, this will affect the INSERT/UPDATE/DELETE performance.
/Håkan Winther