I've red a couple of blogs/stackoverflow posts about not using a Guid as primary clustered key. Unfortunately we have at the moment and due to this we're experiencing performance degradation (with 900.000 rows in the database).
Our table looks like this:
| ID (PK) | PARENTID (FK) | CONTENT | TYPE | ROWVERSION | | UNIQUEIDENIFIER | UNIQUEIDENTIFIER | NVARCHAR(MAX) | NVARCHAR(25) | rowversion |
In this table we're building up a tree (max 3 levels deep). The root has an empty ParentId, in other situation it's pointing to another Id (foreign key).
I want to refactor this table to the following, in order to prevent the UNIQUEIDENTIFIER to be a primary key:
| ID | PARENTID | TECHID | CONTENT | TYPE | ROWVERSION | | BIGINT | BIGINT | UNIQUEIDENTIFIER | NVARCHAR(MAX) | NVARCHAR(25) | rowversion |
I'm keeping the UNIQUEIDENTIFIER in the table because that's what we expose/communicate to the outside world.
The question I have specifically. Which column(s) should be handy for my primary key?
The TechId as (non clustered) primary key and a clustered index on ID.
The TechId as (non clustered) primary key and a clustered index on ID and ParentId.
The Id as clustered primary key and a non clustered key on the TechId.
Or any other suggestions?
Thanks in advance