Int for identity/primary key and Guid for public identifier, best practices?

Question

I almost always use an int for identity/primary (auto incrementing) clustered key for my tables. They're easy to query and debug, bounce around in SSMS, and plenty of other reasons.

I have a case where I'm creating an application that has your typical CRUD interface (create, read, update, delete) and I want to use a Guid in the url instead of the int.

An example table would be:

CustomerId int identity(1,1) PK clustered
CustomerName nvarchar(100)
Guid uniqueidentifier

In the past when someone was viewing a detail record I would just pass the identity:

/app/details/1 <-- id of the record

As I've seen in many applications, I like the idea of this:

/app/details/guidinstead

So in short, an int as the identity, and a guid as the public facing key.

What I'm concerned about is performance if any given table using this method grows substantially. There are business reasons for this, as in a person that has access can share a particular link with another to view details.

Another reason is because it does reduce some overhead on queries in a different manner, as in determining if the person has the "right" to view a particular record.

The initial hit is looking up the real ID of the record. After that all nested resources on the record are easily achieved with joins on the identity field across many tables.

I currently have no indexing set on the [Guid] uniqueidentifier column.

So to sum it up, I want to avoid showing incrementing keys in the URLs and I don't want a nightmare later on down the road if the tables end up with lots of data. Inserts and updates are currently using the int/identity column.

Operations are like this:

1. When creating a record, I use Guid.NewGuid() in .NET to generate the Guid column.
2. When updating a record, I find the CustomerId based on the Guid and update accordingly because this usually ends up updating/inserting in other relational tables as well and I need that CustomerId to do so.
3. When viewing the details of the record, it's WHERE Guid = @Guid
4. When deleting a record, it's WHERE Guid = @Guid

Questions:

1. Is it worth having both int and guid in the table or should I just drop the int entirely?
2. Any other settings I should place on the table?
3. Is there a better option? Another impossible to guess sequence of some sort that doesn't take as much space?

It's early stages so easy for me to make changes now.

Answer 1

Is it worth having both int and guid in the table or should I just drop the int entirely?

Yes, from the perspective that you can have the identity column be a unique clustered index to take advantage of inserts being append-only to the table, and you can have a nonclustered primary key on your GUID column for both security and uniqueness when locating records.

Any other settings I should place on the table?

You could consider using NEWSEQUENTIALID to have the GUID inserts be less random, or try to match the behavior natively in .NET. If someone were quite dedicated, they could perhaps guess the next sequential GUID. If you think you might attract that kind of attention, generate them in a normal, random way.

I'd also wanna make the identity column a BIGINT. They're much harder to run out of than INTs. People who get all up in arms about the 4 extra bytes per row have never dealt with the process of altering that attribute after running out of INTs.

Is there a better option? Another impossible to guess sequence of some sort that doesn't take as much space?

Nope, you're on the right track.

Answer 2

As I experienced, the clustered index on the identity type column cause lot's page latching due to sequential/last page writing, as clustered index store data in sorted order.

The behavior depends upon row size and number or transaction on the table. In one of the organizations where I worked, used to receive almost 35-40 GB / day 150-200 million transactions. A clustered index on identity column the table was causing latch contention, due to which I had to move clustered index to a candidate key.

So, if the table is for OLTP purpose, I would suggest creating primary with non-clustered index & use GUID column for a clustered index.

As you want to use GUID column as search criteria, it will not only improve your SELECT query performance (by avoiding key lookup) at the same time it'll help you t decrease page latching (if it's OLTP table).

Now coming to questions.

Is it worth having both int and guide in the table or should I just drop the int entirely?

Despite both of the columns will uniquely identify records in the table yet I don't see any drawback of keeping both columns. Identity may help you in future to identify missing records and other things.

Any other settings I should place on the table?

Apart from what Erik has suggested, I would suggest you for

1. using proper index fillfacter.
2. Make sure DB data files have extra allocated space (prevent runtime space allocation).
3. Don't forget to size the DB transaction log. An article transaction-log-vlfs-too-many-or-too-few will help you.

Is there a better option? Another impossible to guess sequence of some sort that doesn't take as much space?

It doesn't take less space but quite different from GUID. you can use it in SP (inserting records in the table, to generate a hash value against every record in the table. I'm sure you would like to go through HASHBYTE.

Thanks!

Answer 3

There are business reasons for this, as in a person that has access can share a particular link with another to view details.

Another reason is because it does reduce some overhead on queries in a different manner, as in determining if the person has the "right" to view a particular record.

If you are already authorizing user,then Why worry about Query String ? Main concern should be security.If you are already validating User on each URL then why worry about Query String.

Can you clear this point ?

In most of the case you can do without Query String. Many people use friendly url to make it User and SEO friendly.

What I meant is using GUID in url is little more secure than INT, if you are not handling Authorization and IDOR internally