Geek Answers Handbook

GUID SQL Server (from Active Directory) vs Int

We transferred a lot of data from our old ordering system. This system stored the initials of users in our "Orders" table for each order created by him. Now that we have a view that looks at the active directory, I would like to update these initials with the active directory objectguid (or something that references guid). This will allow us to change the initials of users in the active directory without worrying about updating the entries in the Orders table.

I read that index performance when using commands against ints is small. One way to solve this problem is to use a table that maps pointers to ints and then stores the int value in our order table. Would that be a good idea? Any thoughts?

+1
guid sql-server int active-directory
source share
2 answers

I assume that the USER object already exists in your database design, but if it is not, I believe your solution will require it.

Thus, if it is assumed that the USER object exists, as you described, you can put the user identifier (int) in the ORDERS table, thereby linking all the relevant USER data to ORDER, and not just to the initials (Note: Initials may not will be stored in the ORDER table, and the USER or USER_DETAILS table, although not the focus of this discussion).

You can then add the GUID column to the USER table. I don't think a separate lookup table is needed.

Make sense?

+4
source share

The performance of an index by GUIDs does not differ at all from other indexes in a 16-byte field. If the GUID is lethal for performance, this is when you use them as a clustered key in a SQL Server table.

A SQL Server table is a clustered key and is physically ordered by that key. Since the GUIDs are completely random in nature, this leads to massive fragmentation of the index very quickly and therefore requires: a) constant nutrition, care and reorganization, but b) still suffers, even if you reorganize every night. Thus, it would be best to avoid the GUIDs (even the new "sequential" GUIDs) for your clustering key.

For more background information and excellent reviews on why GUIDs have very bad cluster keys, see the Indexing Queen blog, Kimberly Tripp:

Her understanding is the most valuable - read it, learn it, follow it! You will not regret it.

Mark

+4
source share

More articles:


All Articles