Composite clustered index as primary key vs heap table in SQL Server

Question

To ensure uniqueness there is a composite PK (clustered) containing:

[timestamp] [datetime2]
[userId] [varchar](36) 
[cost_type] [varchar](20) 

There are two more columns in the table:

[cost_cent] [bigint] NULL
[consumption_cent] [bigint] NULL

Composite clustered primary keys are not ideal (incl. varchar) but what is the alternative?

Having a heap table with a non clustered primary key? Additionally add another clustered index? But on what column? There is no identity column.

Background: there is a constant insert/update on this table via Merge statements. Table size is ~50 million rows

Queries will use the PK with a time range mainly.

Answer 1

Your index size is 58 bytes,i don't see a big issue with this size..

there is a constant insert/update on this table via Merge statements

if you go with existing setup of composite key(since 56 bytes is not that huge) ,updating primary key is a red flag,since

1.You may see some fragmentation
2.update/delete commands will also have to touch non clustered indexes

Some more options i would experiment with,since 50 million is not much huge

1. Leave this table as heap and add a non clustered index with timestamp column as leading column and rest of the columns needed for a query as included columns .When you leave this table as heap,try answering the following questions yourself to see if leaving this table as heap helps you

• Will you ever need to join this table to other tables?
• Do you need a way to uniquely identify a record?

2.I would also try adding an identity column and make it as primary key..