1

I am retrieving the active directory GUID for AD users, the GUID is a 16 bytes long as mentioend on this link LINK . But can anyone adivse what is the best data type for storing this GUID inside my sql server 2008 R2 database, as I will need to search the related table for this GUID very frequently?

Rikalous
  • 4,514
  • 1
  • 40
  • 52
John John
  • 1
  • 72
  • 238
  • 501

2 Answers2

2

It would be most intuitive to use the uniqueidentifier type and put an index on it. The uniqueidentifier type is exactly made to stored GUIDs.

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
  • but what is the difference between defining the data type as uniqueidentifier OR as Nvarchar(16) OR int for the GUID? can you adivce ? – John John Dec 16 '14 at 17:18
  • Well, the data type is made to store guids, the others are not. I mean, you can store integers as strings or datetimes as doubles, but why not use the data type that's meant for it? – Thorsten Dittmar Dec 16 '14 at 18:44
  • so will i get better performance if i use the uniqueidentifier as the data type ? – John John Dec 17 '14 at 01:26
  • 1
    Performance depends on whether you get your indexes right, not necessarily on the column data type. Using anything but the designated types can even slow you down, as a lot of conversion needs to go on. If you use your database from C# for example, you will use GUIDs in C#. If you stored them as strings or ints in the DB, you'd have to convert them every time you want to query. – Thorsten Dittmar Dec 17 '14 at 07:38
  • seems that i can not add null values to a unique-identifier column, in sql server 2008 r2. in my case i have the AD Guid column to store users GUID as definied inside the AD. but also our system allow to add external users which are not defined inside the AD, and for those users the GUID column will be null,,, so seems that using unique-identifier column to store the AD GUID is not the right data type in my case,, can u adivce please ? – John John Dec 17 '14 at 18:05
2

Use can use uniqueidentifier and index the column.

Also have a look at What are the best practices for using a GUID as a primary key, specifically regarding performance?

Community
  • 1
  • 1
huMpty duMpty
  • 14,346
  • 14
  • 60
  • 99
  • thanks but in my case the GUID will be unique but not the primary key ,, i still will generate a separate system generated ID of type int and set ti as the primary key. what so you think ? – John John Dec 16 '14 at 16:55
  • @johnG: it doesn't need to be primary key, can stay as a normal column – huMpty duMpty Dec 16 '14 at 16:56
  • you mean i can have the GUID as a unique key , but also have another column set a the PK (which will be system generated (IsInedtity set to Yes))?? – John John Dec 16 '14 at 16:58
  • It doesn't have to be a unique key - GUIDs are unique by definition (that's why they are called Globally *Unique* Identifiers). Unless you want to use the key to prevent double-insertion of the same AD user. – Thorsten Dittmar Dec 17 '14 at 07:39
  • but will the GUID allow me to store null values ? because some records in my system will not represent active directory users,, so their GUID will be null – John John Dec 17 '14 at 18:06
  • @johnG: You can have anything as your PK, not only Identity fields. If you set a field as your primary key, it's up to you to have new (unique) values for each record inserted. If you set an Identity field, then the server will generate new values for you. And you can have any other fields as unique keys. The recommendation is that you don't use a GUID as your PK, because it's not sequential, which can quickly cause fragmentation in the PK index. And yes, you can have nullable UNIQUEID fields. – Alexandre Jul 13 '20 at 17:53