(Clustered) Indexing on SQL

by Damiaan Peeters 6. August 2012 13:45

Published inititially in this forum post on sql Azure.

Use always the primary key + clustered index with integer with Identity.  Use an ever increasing value.  If you use a Guid use the NEWSEQUENTIALID function.

A few additional thoughts:

  • Try to create a clustered index on the most frequently used column to retrieve data.
  • created a (clustered) index on as few columns as possible
  • Clustered index columns shouldn't have duplicate values
  • Put clustered indexes on columns which are not updated

There is a very good write up on best practices on SQL Indexing (not azure specific) here: http://blogs.technet.com/b/josebda/archive/2009/03/17/indexing-best-practices-for-sql-server-2008.aspx

EDIT: Concerning an INSERT: i don't think it's always the same whether you have an (clustered) index or not. A Clustered index creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. Depending on your choice of clustered index this may affect insert performance.  Read the introduction to clustered and non-clustered index data structures at sql-server-performance.com.

Who.I.am

Certified Umbraco Master, Part of Umbraco Certified partner comm-it, .Net and Azure developer, seo lover. Magician in my spare time.

Month List