SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Generating a unique identifier outside of SQL and persisting it…

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.

GO-SQL blog A blog dedicated for SQL Server Developers alike!

My name is Andrew Fenna, and I am a database developer at The Laterooms Group. I have written this blog to educate my fellow database developers in subjects that I think are necessary to keep at the top of our game. I have been working with RDBMS’s since 2002 and I have worked within financial, gaming, public, and travel sectors within the Northwest of England. I have gained quite a lot of experience throughout my journey and have tackled some very difficult challenges along the way. Therefore, I am blogging my experiences, which I hope will give you the inspiration to try and tackle problems that are deemed impossible to start, but with some encouragement and guidance become much more simpler and structured, thank you for your time. Andrew J Fenna.


Leave a comment on the original post [gosql.co.uk, opens in a new window]

Loading comments...