If a Microsoft SQL Server 2005 database has two or more tables that are frequently joined together, then the columns used for the JOINs should have an index configured. If the columns used in the join are "wide", for example Email varchar(70), then use a surrogate key for the JOINs such as

EmailID INT IDENTITY(1,1) PRIMARY KEY

INT is 4 bytes fixed length column yields extremely good performance in JOINs. Example for index create:

-- T-SQL create index - create nonclustered index on contactid column

CREATE NONCLUSTERED INDEX [idxContactPK] ON [Person].[Contact] ([ContactID] ASC)

GO

Regular index maintenance should include rebuilding indexes every weekend. Extremely dynamic OLTP tables should be rebuilt with FILLFACTOR 70-90.

-- MSSQL index rebuild with fillfactor

USE AdventureWorks;

GO

ALTER INDEX ALL ON Sales.SalesOrderDetail

REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON);

GO

Covering indexes are good for queries which are executed very frequently, business critical or mission critical. SQL Server 2005 allows the inclusion of nonkey (not indexed) columns in covering indexing. That is, all columns referenced in the query are included in the index as either key or nonkey columns. Composite index can also cover a query (pre-2005). This allows the query optimizer to locate all the required information from an index seek or scan; the table or clustered index data will not be accessed. Example: Category and Subcategory form a composite index key. Frequent query includes Category, Subcategory, Color and Size. Including Color and Size as nonkey columns in the index will speed up the query significantly.

-- SQL convering index demo - INCLUDE option in create index

USE AdventureWorks;

GO

IF EXISTS (SELECT name FROM sys.indexes

            WHERE name = N'idxContactCovering')

    DROP INDEX idxContactCovering ON Person.Contact;

GO

-- Create index covers all the columns in the following query

CREATE NONCLUSTERED INDEX idxContactCovering

    ON Person.Contact (ContactID)

    INCLUDE (FirstName, LastName, EmailAddress);

GO

-- SQL covered query

SELECT FirstName+' '+ LastName as FullName, EmailAddress, ContactID

FROM Person.Contact

WHERE ContactID BETWEEN N'10000' and N'11000'

ORDER BY FullName;

GO

/* Partial results

FullName          EmailAddress                        ContactID

Alexandra Allen   alexandra90@adventure-works.com     10115

Alexandra Hall    alexandra89@adventure-works.com     10058

Alexandra Smith   alexandra92@adventure-works.com     10494

Alexandra Young   alexandra91@adventure-works.com     10181

Allison Allen     allison45@adventure-works.com       10160

*/

------------