Toad World® Forums

Clustered index example of exception to the rule

Per my research the ideal clustered index should be a narrow column and unique. We are currently on SQL Server 2012 and have a database setup for merge replication. I am in the process of adding a new table, StoreOPenDocuments, to our database. I am unsure what the clustered index should be.

Table StoreOpenDocuments

      StoreOpenDocumentsID defined as UniqueIdentifier

      CustomerID defined as UniqueIdentifier




Table MemberShipList (existing table)

      MemberShipListID   UniqueIdentifier

      CustomerID            UniqueIdentifier




Would it make sense to make the StoreOpendocuments.CustomerID the clustered index even though it is not unique and is done on a UniqueIdentifier column?

Majority of our select statements will have where CustomerID = XXX

Or should I have the StoreOpenDocumetnsID be the clustered index, at least it is unique.

Thank you in advance for your time,


I’d rather leave the table as a heap omitting any clustered index before I would choose a uniqueidentifier-column as clustered index. Instead define non-clustered Indexes on the unique (primarykey) and another one on the customerid-column. Anyway, you’ll still have the problem of fast fragmentation but not on the clustered index.

If you’re not willing to use a heap then maybe an additional int-identity column would be a good choice for a clustered index.

see e.g.…/