creating userID (int) - Use AutoIncrement?

[Train]

You may want randomized IDs but still want it in the URL so it can be bookmarked for instance.

What would be the point of that?

 

[uclabachelor]

For data you want to obscure, he's correct. AutoIncrement makes things predictable. If you don't want somebody scraping your site, then use GUIDs. If you want to make it easy to scrape then go ahead and use an autoincrement and put it right in the URL. There are reasons to do both.

If a page has data that should not and cannot be seen by anyone but an authenticated user, then this discussion is moot.

If a page is public and you want to prevent scraping, then there are a few ways I can think of off the top of my head to limit or prevent scraping that doesn't enforce a requirement on the database.

Also if you're sharding you'll avoid hotspots by using UUIDs.

I don't see how using GUIDs over integer numbers can avoid creating hotspots. If I have 2 partitions, what's the difference between assigning the partitions A-M and N-Z versus odds and evens?

 

[clamum]

In your case OP, it sounds like an auto-increment integer would work fine.

GUIDs can be used (SQL Server can generate them by calling NEWID()) but they require much more storage than a simple int and indexes would be slower on them. From SQLTeam.com:

The major advantage of using GUIDs is that they are unique across all space and time. This comes in handy if you're consolidating records from multiple SQL Servers into one table, as in a data warehousing situation. GUIDs are also used heavily by SQL Server replication to keep track of rows when they're spread out among multiple SQL Servers.

The main disadvantage to using GUIDs as key values is that they are BIG. At 16 bytes a pop, they are one of the largest datatypes in SQL Server. Indexes built on GUIDs are going to be larger and slower than indexes built on IDENTITY columns, which are usually ints (4 bytes).

 

[TechBoyJK]

In your case OP, it sounds like an auto-increment integer would work fine.

GUIDs can be used (SQL Server can generate them by calling NEWID()) but they require much more storage than a simple int and indexes would be slower on them. From SQLTeam.com:

That's the winning point to this discussion, imho. I don't have any security needs to obfuscate the userID, and I have programmatic protections against abusing the url's (such as comparing userID to session.userID for authentication, etc, etc).

If I'm going to have 100million users (hypothetically), having GUID's as the primarykey and running indexes on that could be a game changer when compared to the smaller INT type.

 

[clamum]

That's the winning point to this discussion, imho. I don't have any security needs to obfuscate the userID, and I have programmatic protections against abusing the url's (such as comparing userID to session.userID for authentication, etc, etc).

If I'm going to have 100million users (hypothetically), having GUID's as the primarykey and running indexes on that could be a game changer when compared to the smaller INT type.

Exactly. I try to follow the KISS principle as much as possible in development, and in your case here I'd say KISS means auto-inc. 🙂