9

Earlier, there were non-conclusive to me debates / discussions on whether to (always) engage / avoid clustered indexes.

Well, I understood that they are to be used sometimes with proper + specific purposes and context.

SQL Azure Database Clustered Index Requirement:

"SQL Azure does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table"

does not fit in the previous conclusions, rationale and explanations.

What is the rationale, that I have missed from prev explanations, of rigidly imposing ubiquity of clustered indexes without any exceptions?

Improve this question
1
  • 1
    Note that in v12 of the Azure SQL Database Service, you may have a table without a clustered index.
    – Troy Hunt
    Mar 18 '15 at 4:13
11

Read Inside SQL Azure:

SQL Azure provides logical databases for application data storage. In reality, each subscriber’s data is actually stored multiple times, replicated across three SQL Server databases that are distributed across three physical servers in a single data center. Many subscribers may share the same physical database.

Clustered keys are required so that the three replicas of your data can be kept in sync. W/o a key, is impossible to know which rows were updated. Heaps (tables w/o a clustered index) have only physical 'keys' (fileid:pageid:slot) and since your 3 replicas of the logical database share physical database with other logical databases the physical address on one server has no meaning on the other replicas, hence heaps could not possibly be replicated.

Improve this answer
3
  • A (logical) key doesn't have to reside in a clustered index. It could be nonclustered. Perhaps the real reason why a (presumably unique?) clustered index is required is that heaps use RIDs whereas unique clustered indexes do not. Is that what you meant?
    – nvogel
    Nov 5 '10 at 20:23
  • 3
    From the linked document: "The underlying high availability and replication technology in SQL Azure is based on replicating B-Tree rows.". So even if a heap would have a NC key, you could only replicate the NC, but not the heap itself.
    – Remus Rusanu
    Nov 5 '10 at 20:41
  • Another source: azure.microsoft.com/en-us/blog/why-do-i-need-a-clustered-index
    – usr
    Oct 4 '15 at 21:46
1

Azure is a distributed, cloud based system on remote servers. The data will likely be stored on multiple drives/servers, and it would be extremely inefficient to do this on a heap (since the system will need to know which machine to check, and without a clustered index this is a resource-intensive operation).

The clustered index provides a lookup for all rows and all other indexes on the table, so without one every operation in azure would be a table scan across multiple machines.

Improve this answer
1
  • 2
    That could be true, but at the moment is not. Read article I linked, it explains how the request routing works and why your queries run always on one single box and never span multiple servers. Ie. there is no sharding.
    – Remus Rusanu
    Nov 5 '10 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.