2

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?

Carvellis
  • 3,992
  • 2
  • 34
  • 66
  • 2
    Just curious, why would you choose that over an int or a numerical datatype for linking the data together? – Wadih M. Jul 21 '10 at 11:47
  • 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 Jul 21 '10 at 12:09
  • 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 Jul 21 '10 at 13:02

1 Answers1

2

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.

Community
  • 1
  • 1
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • In the future, merging of data could become a requirement. Perhaps at that time it can be solved with an extra "server id" as described in the article link. – Carvellis Jul 21 '10 at 12:58