Guidelines for Creating Indexes in SQL Server

June 04, 2014 , , 0 Comments

Database Developer need to consider listed guidelines while creating indexes on a table. These guidelines explains about some special features or we can say requirements needed to create an index.

Earlier article was about to create an index with syntax and description of parameters. Each parameter have its own speciality discussed in that article, here we noticed some guidelines that may use in create in index:

  • Create clustered indexes on columns that have unique or not null values.
  • Do not create an index that is not used frequently. You require time and resources to maintain indexes.
  • Create a clustered index before creating a nonclustered index. A clustered index changes the order of rows. A nonclustered index would need to be rebuilt if it is built before a clustered index.
  • Create nonclustered indexes on all columns that are frequently used in predicates and join conditions in queries.

Consider an example of an organization that maintains employee details in the Employee table. You can create a clustered index on the EmployeeID attribute of the Employee table by using the following code:

ON Employee (EmployeeID)

In the preceding code, the FILLFACTOR value of 10 has been specified to reserve a percentage of free space on each data page of the index to accommodate future expansion.

The following example creates a nonclustered index on the ManagerID attribute of the Employee table:

ON Employee (ManagerID)
When a PRIMARY KEY or UNIQUE constraint is created on a table, an index is created automatically with the same name as the constraint.

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google