What data type is GUID in SQL server?
Solution 1
16 bytes, exactly as the GUID structure:
typedef struct _GUID {
DWORD Data1;
WORD Data2;
WORD Data3;
BYTE Data4[8];
} GUID;
Solution 2
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.
Solution 3
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).
Ondrej Peterka
I simply like programming. I do it for living as well as for my enjoyment. I am working mainly in C#, Java, C\C++, Objective-C and Javascript. I have also programmed with Typescript, Scala, F#, Haskell or even Cayenne.
Updated on July 09, 2022Comments
-
Ondrej Peterka almost 2 years
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.