4

AFAIK SQL table indices are to improve queries speed, so are not absolutely necessary. However SQL Azure for whatever reason demands that each table has a clustered index, so I guess there're cases when an index is necessary.

What can be a case when a clustered index is absolutely required and why?

Igor Borisenko
  • 3,806
  • 3
  • 34
  • 49
sharptooth
  • 167,383
  • 100
  • 513
  • 979
  • It's a good, common practice to **have** a **good** clustering key (e.g. on an `INT IDENTITY`). Almost all operations are **faster** on a clustered table vs. a heap table. See [Kimberly Tripp's blog post](http://sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx) for details – marc_s Dec 08 '11 at 11:57

2 Answers2

4

The absence of a clustered index implies a completely different storage model for the table, namely a heap table. I suspect that the SQL Azure team simply didn't implement this, and therefore tables must be stored in the "regular" way.

This article describes some of the pros and cons of heap tables and clustered tables.

Jonas Høgh
  • 10,358
  • 1
  • 26
  • 46
1

SQL Azure requires clustered indices because of the replication on the back-end of the system. Here's a reference article from the MSDN blog that goes into some of the technical details:

http://blogs.msdn.com/b/sqlazure/archive/2010/05/12/10011257.aspx

Mike Taber
  • 833
  • 6
  • 21