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

      InvoiceNumber

      GrossAmount

      .

Table MemberShipList (existing table)

      MemberShipListID   UniqueIdentifier

      CustomerID            UniqueIdentifier

      StoreName

      StoreAddressLine1

      .

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,

Tsharp89

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. www.sqlskills.com/…/