What is the best choice for clustered index when the primary key can't be used?

Question

Given an application where the primary key for most tables is uuid, what is the best choice for a clustered index on these tables?

• The clustered index is required because the back-end database is Azure SQL Server and Azure requires a clustered index.
• The uuid was a design choice to satisfy the need for n clients to create entities in a disconnected state and then sync when connected.
• We are not using the primary key as the clustered index in order to avoid fragmentation issues due to the randomness of uuids.

Considering the following data types:

• int or bigint. Easy - can be auto incrementing (good/bad) but seems so arbitrary and has limited utility. Feels most like a hack.
• datetime - increased utility - could be a createdOnServer column. but would result in some dupes and thus require uniqueifiers (how big a problem this is, I don't know)
• datetime2 - wider than datetime but greater precision and less dupes.

Looking for comments on which is best, things to consider, or alternative ideas.

Answer 1

If you don't want to create an index you can consider to upgrade/move to a V12 Azure SQL Database. These don't have the requirement for a clustered index.

On the index itself: You should create it on the columns that you query on. If you do lookups on the UUIDs + a date for example, you should create the index on the two to supported your queries.

Answer 2

I am not sure why you dismiss auto incrementing int. Anything that is widely used, works very well and as long as you are not planning on merging the table (via a Union) with other versions of the table why wouldn't you want to use it? It will provide a very good key (not too wide) for a balanced B Tree that clustered indexes are behind the curtain. Remember that in a table, that has a clustered index, all the other indexes will use the clustered index column to get to the desired page and row so you want to make it as small as possible.