How does performance of guids compare to strings in sql

1
votes

For primary keys on a large SQL Server 2008 data table, I have the choice of using guids or a string of about the same size.

Performance-wise, how does a guid (16 bytes) compare to a string of about 16 chars, when doing joins, selects, etc. Is SQL better at handling guids because internally they are represented as a number?

1
sql-serversql-server-2008guid
Just curious, why would you choose that over an int or a numerical datatype for linking the data together? – Wadih M.
Not sure about the performance, but as per Wadih: => One of the implications is that all FK's to this table will now carry the 16 byte width overhead as well. => Would suggest that you look at changing the Clustered index away from your 16 byte PK as else would likely incur page splits if the char / guid is randomly distributed. => Recommend that you use fixed width char, not e.g. varchar(16) – StuartLC
Merging data could be a requirement in the future, therefore I would use a guid. I will probably go for the bigint anyway, but from a professional interest, I would still like to know how they compare. – Carvellis

1 Answers

2
votes

The short answer to your question is, ideally, to use neither.

If you can use an int/bigint key (as I suggested in my answer to your related question), you should.

Unless you need the functionality to non-destructively merge copies of this dataset stored on more than one SQL Server instance, using a GUID primary key adds a considerable overhead in index maintenance. This article has a reasonable discussion of the issue.

A string PK should have less overhead if the sequence you generate is consistently ordered - i.e. new values are always added at the "end" of the table.