GUID or Varchar(36)?

While working on an application which had a foreign key GUID reference, we had to change the implementation for that field to accept a string. Then we came across the question as to whether GUIDs or a Varchar(36) field would perform better?

First off, what is a GUID?

In essence a GUID is an integer value. Of course, it is a 128 bit integer value taking 16 bytes to store. What we usually see (b6aa92e9-5ae9-47f4-ad2f-ebc36452c61d) is the human readable hexadecimal string. But GUID values are not stored or processed as strings. The other difference to a 128-bit integer is how the next GUID is generated – randomly, rather than sequentially.

See comments in this article for more info:
http://blog.sqlauthority.com/2010/04/28/sql-server-guid-vs-int-your-opinion/

Why compare against Varchar(36)?

That's because the hexadecimal representation of a GUID is 36 characters long. So if you are storing the GUID as a string, you need a Varchar(36) field.

See this article on the pros and cons:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/df3caee5-3751-469b-a943-f0c8cb501f75/unique-identifier-vs-varchar36-for-storing-guids?forum=transactsql

So what performs better?

Having said that a GUID takes 16 bytes and Varchar(36) takes 36 bytes, the GUID will always outperform it's counterpart. Other than that, it would be somewhat faster to compare GUID values because it's a simple numeric comparison and doesn't have to consider lexical rules.

Caveats

If it is either a GUID or Varchar(36) field, make sure that it is indexed to have a performance gain. Also consider your requirements to make sure what suits you best.

No comments:

Post a Comment