Uniqueidentifier (GUID) on SQL

Uniqueidentifiers are alternate way to have them as primary keys. Till recent past, I always used to recommend Identity column on our SQL designs . So I was curious to find out what are the advantages and limitations of using uniqueidentifiers than using identity.


Only advantage I could find by using uniqueidentifier is that the number which is generated is unique throughout the world. Another advantage is that you don’t have rely on SQL to create this uniqueidentifier, we could even generate using out C# code and insert to sql tables. The value which is generated by C# and SQL will be unique anyways.

Is uniqueidentifier suitable for primary key?

The uniqueidentifier can be used as primary key but we need to consider some of the conditions.

• The primary key will have clustered index by default which doesn’t work very well for uniqueidentifier. The uniqueueidentfier doesn’t create the ids in sequential order unless we use newsequentialid() (which is available from sql 2005 onwards). When we create clustered index, the records will be physically sorted. This means that, if you put a clustered index on a column that does not sequentially grow, SQL Server will have some work making sure that the records are correctly ordered physically when you insert new records.

• Some of the applications expect the identifier of last inserted record. E.g if we are using identity column, we can get the last inserted value using scope_identity or @@identity. We will not be able to get the uniqueidentifier value unless using a query to get the latest record.

Above considerations are also kinds of limitations for some of the DB designs. Another important note here is that the uniqueueidentifier column stores 16 byte so it bigger than int.

But if you consider replicating the database and having identity column in your tables, it would create lot of issues. So I would say uniqueidentifier really helpful on the DB’s which requires replications.