Geek Answers Handbook

Why do aspnet_users use guid for id and not for increment int? bonus points for helping to expand custom fields

Why do aspnet_users use guid for id instead of incrementing int?

Also, is there any reason not to use this in other tables as a primary key? This is a bit strange, since I know that most of the applications I have worked with in the past just use the regular int system.

I am also going to use this identifier to match the extended detail table for additional custom prefs, etc. I also considered using a link table with guid and int in it, but decided that I think I really need the user id to be public.

Although I would like to have an int (it’s easier to search for a user, etc. stackoverflow.com/users/12345/user-name), since I just want to have a username, I don’t think I need to move this item around and cause extra search complexity when I need to find user int.

Thanks for any help with this.

+6
authentication guid uniqueidentifier
source share
2 answers

It provides uniqueness in unrelated systems. Any data warehouse that may be required to interact with another previously unrelated data warehouse can potentially run into conflicts - for example. they both used int to identify users, now we need to go through an integrated resolution process to select new identifiers for conflicting ones and update all links accordingly.

The disadvantage of using the standard uniqueidentifier in SQL (with newid ()) as the primary key is that the GUIDs are not sequential, as new rows are created, they are inserted at an arbitrary position on the physical database page, rather than being attached to the end. This leads to severe page fragmentation for systems with significant insertion speed. It can be fixed with newsequentialid (). I discussed this in more detail here .

In general, it is recommended that you use newsequentialid () for your primary GUID or simply do not use the GUID for your primary key. You can always have a secondary indexed column that stores a GUID that you can use to make your links unique.

+13
source share

GUIDs as a primary key are quite popular among certain groups of programmers who actually (do not want or do not know) take care of their base database.

GUIDs are cool because they are (almost) guaranteed to be unique, and you can create them "ahead of time" on the client application in .NET code.

Unfortunately, these people are not aware of the terrible downsides (horrific index fragmentation and therefore performance loss) of these options when it comes to SQL Server. Many programmers really just don't care about anything ... and then blame SQL Server for being as slow as a dog.

If you want to use a GUID for your primary keys (and they really have very good use possibilities, as Rex M. pointed out - in replication scripts mostly), then OK, but be sure to use the INT IDENTITY column as your clustering in SQL Server, to minimize index fragmentation and therefore performance loss.

Kimberly Tripp, "Queen of SQL Server Indexing," has tons of really good and insightful articles on the subject - see some of my favorites:

and basically everything she ever posts on

+5
source share

More articles:


All Articles