How is GUID internally stored and compared by SQL (particularly MS SQL server 2008)? Is it a number or string? Also, is there a big performance hit when using GUID as primary key?
Besides the problem with clustering mentioned here: What are the best practices for using a GUID as a primary key, specifically regarding performance?
I think it should be 128bit number (as described here), but I cannot find mode details on how is it implemented in SQL server.
Performance wise, normal GUID is slower than INT in SQL Server
If you plan to use GUID, use uniqueidentifier instead of varchar as data type. Microsoft did not mention how they implement it, there is some speed optimization when you use uniqueidentifier as the data type.
To use GUID as primary key without sacrificing speed of integer, make the GUID value sequential. Define uniqueidentifier data type as PK, set the default to NEWSEQUENTIALID().
See NEWSEQUENTIALID (Transact-SQL) for further details.
As to how sequential GUID values help performance, see The Cost of GUIDs as Primary Keys.
16 bytes, exactly as the GUID structure:
typedef struct _GUID {
DWORD Data1;
WORD Data2;
WORD Data3;
BYTE Data4[8];
} GUID;
You can also use nvarchar(128).
The next-best option would be a binary(16) column:
standard GUIDs are exactly 16 bytes in length. If you must store it as a string, the length really comes down to how you choose to encode it. As hex (AKA base-16 encoding) without hyphens it would be 32 characters (two hex digits per byte).
