Create Index Syntax

To add an index in SQL Server use the CREATE INDEX statements. When adding indexes remember there can only be one clustered index per table. The main options when creating an index are clutered or nonclustered or unique vs non unique. Using SQL 2005+ you can also specify columns to include at the leaf level of the index.

Create a single nonclustered index

[cc lang=”sql”]
CREATE UNIQUE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name
ON dbo.Presidents (PresidentNumber) — specify table and column name
[/cc]

Create a multi-column (composite) nonclustered index

[cc lang=”sql”]
CREATE UNIQUE NONCLUSTERED INDEX IX_NC_PresidentNumber_PresidentName — specify index name
ON dbo.Presidents (PresidentNumber,PresidentName) — specify table and column names
[/cc]

Create a multi-column (composite) clustered index

[cc lang=”sql”]
CREATE UNIQUE CLUSTERED INDEX IX_C_PresidentNumber — specify index name
ON dbo.Presidents (PresidentNumber,PresidentName) — specify table and column names
[/cc]

Create a non clustered index with included columns

[cc lang=”sql”]
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name
ON dbo.Presidents (PresidentNumber) — specify table and column name
INCLUDE (President,YearsInOffice,RatingPoints) — specify included columns
[/cc]

Create index with fill factor

[cc lang=”sql”]
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber — specify index name
ON dbo.Presidents (PresidentNumber) — specify table and column name
WITH (FILLFACTOR = 80) — specify the fill factor
[/cc]

SQL Server 2008 options

SQL Server 2008 provides additional index options. Most notably it provides the ability to filter an index. This can help decrease the size of the index for very large or partitioned table to only include the records that are normally queried.
[cc lang=”sql”]
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber
ON dbo.Presidents (PresidentNumber)
INCLUDE (President,YearsInOffice,RatingPoints)
WHERE ElectoralVotes IS NOT NULL
[/cc]
Another 2008 option is the ability to compress the contents of the index based on the row or the page.
[cc lang=”sql”]
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber
ON dbo.Presidents (PresidentNumber)
WITH ( DATA_COMPRESSION = ROW ) ;
[/cc]
Compress based on page:
[cc lang=”sql”]
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber
ON dbo.Presidents (PresidentNumber)
WITH ( DATA_COMPRESSION = PAGE ) ;
[/cc]