Creating covering indexes in SQL Server 2005

CREATE INDEX INCLUDE statement (SQL Server 2005)

I've been testing the new INCLUDE statement in 2005.

From the BOL:

INCLUDE ( column [ ,... n ] )

Specifies the nonkey columns to
be added to the leaf level of the nonclustered index. The nonclustered index can
be unique or nonunique.

The maximum number of included nonkey columns is 1,023 columns; the minimum
number is 1 column.

Column names cannot be repeated in the INCLUDE list and cannot be used
simultaneously as both key and nonkey columns. For more information, see Index
with Included Columns.

All data types are allowed except text, ntext, and image. The index must be
created or rebuilt offline (ONLINE = OFF) if any one of the specified nonkey
columns are varchar(max), nvarchar(max), or varbinary(max) data types.

Computed columns that are deterministic and either precise or imprecise can
be included columns. Computed columns derived from image, ntext, text,
varchar(max), nvarchar(max), varbinary(max), and xml data types can be included
nonkey columns as long as the computed column data types is allowable as an
included column. For more information, see Creating Indexes on Computed
Columns.

Didn't have much time but I did a couple of quick tests. Just created a simple table with a covered index 'the old way' and then 'the new way'. From what I've seen until now the duration is not much less but inserting 10000 records in to the table takes about 10000 reads less with the INCLUDE index. From the info above I suppose it has something to do with the fact that the nonkey column is only present on the leaf level and not throughout the whole tree like a key column.

As soon as I find some time to test is a little deeper I'll try to let you know!