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.