Add new column just to have clustered table

Question

Working on deadlock problems, I need to optimize a table which used guid column for clustered primary key.

I know guid column is a bad choice for clustered index. So I changed the primary key clustered to primary key nonclustered. So now I have a heap and I’m not sure about the consequence of this.

I read that clustered table have better performance but there is no other column which contains unique value. So I was thinking of adding a new integer identity column just to have a clustered index.

But if this new column is not used as foreign key or in queries, is it really helpful?

And if I use the new identity column as foreign key in the other tables instead of the guid primary will that be (at least in theory) better?

Answer 1

Working on deadlock problems

What can you tell about this?

So now I have a heap and I’m not sure about the consequence of this.

1. Deletes and inserts will let the table grow to big sizes. [1]
2. You will have to do full table scans for each query unless the data is in a non-clustered index. SQL can't traverse a b-tree to your row(s) of data.

I know guid column is a bad choice for clustered index.

Doesn't have to be the case, if you leave a lot of space for fragmentation this can actually speed up inserts (less pagelocking at the end) and thus lead to less locks. edit: SQL Server 2019 has optimizations for "less page locking at the end".[2]

So I was thinking of adding a new integer identity column just to have a clustered index.

Unless people are going to look for this, it is of no use. Your clustered index is the most important index, so it should sort on some column(s) that people/programs frequently query on.

[1] why-does-my-heap-have-a-bunch-of-empty-pages
[2] MS docs