Create and Modify SQL Server Indexes Properly

Indexes are critical to smooth data retrieval and manipulation in SQL Server. They are the way foreign keys maintain relationships among themselves and the way DB pros enforce order on data. The system decides how and where an index is built by following the database practitioners directions to address the following questions:

• Is the index the result of a constraint?
• Is the index clustered or not?
• If it is a non clustered index, should it be on a different file group?
• If it is clustered, what fill factor and padding should be used?
• Is the index unique or not?
• Is the index in ascending or descending order?
• Is the index built on a single column, or more than one column?

A look at the column and table constraint language from the create table statement reveals placeholders that implement these decisions:

< column_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor ]
[ON {filegroup | DEFAULT} ] ]
< table_constraint > ::= [ CONSTRAINT constraint_name ]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]