I’ve been thinking about a question from a application developer regarding generating a unique identifier outside of SQL and persisting it in SSQL. I have done a little research into it and have found 2 problem with GUIDs:

1. GUID is a 16-byte type while INT is a 4-byte type. With storage nowadays very cheap, this normally is not a problem from the storage point of view. The problem is that reads (and writes) will be multiplied by 4x.

2. The most serious problem is when you use the GUID ID as the clustered index of the table. In this case, with every INSERT you would be changing the layout of the data potentially having to move many data pages. This is unlike IDENTITY INT clustered indexes where data is stored sequentially providing the best performance.

However I have come up with a solution based on information from other developers having the same problem. This is what I propose do to the entity.

1. Add an IDENTITY INT column (I would call it Id) and set it to be the clustered index. If you do not want to use an additional column, you could use a DATETIME column to store a timestamp which is usually very useful

2. Add a UNIQUEIDENTIFIER column (GUID data type in SQL Server) as the main ID. And set that to be the primary key.

3. Add a non-clustered index for the GUID ID

This will keep the data storage sequential while benefiting from system generated IDs instead of database generated IDs. There are two drawbacks: 1) storing an additional column 2) having an additional non-clustered index which takes some space and makes reads and writes slightly slower. Bear in mind, this is recommended only if you keep the entity only for transactional usage and to store and retrieve by key and not for reporting. In most cases, this is what you would normally do especially if you are implementing CQS or CQRS.