Yet Another Index Covering Tip
Suppose you have a table:
create table a(email_alias varchar(20), full_name varchar(20), /*many other columns*/)
Suppose you need to make sure email_alias are unique. Obviously you will create a unique index on email_alias. Suppose you also need an index on (email_alias, full_name) to cover several frequently run queries. In SQL Server 2005 you can have one and the same index accomplish both goals. Use a new INCLUDE option in CREATE INDEX statement:
create unique index a1 on a(email_alias) include(full_name)
Both columns will be stored in the index, which will guarantee index covering, but the uniqueness of email_alias will be also preserved:
insert into a values('jsmith', 'Jack Smith')
insert into a values('jsmith', 'Jared Smith')
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.a' with unique index 'a1'.
The statement has been terminated.
It is very important to keep the number of indexes as low as possible, and the new INCLUDE option comes very handy in accomplishing that goal.