CODEWITHSUNDEEP
sql.netsql-server
Srb1313711
Srb1313711

Reputation: 2047

SQL Server Int primary key and Guid

I’ve recently moved companies and they do something in SQL Server that I have not seen before. I am used to using a primary key column as an int that increments on inserts, very occasionally I've seen the use of guids but here they use both, see below.

PrimaryID | GUID | RestOfColumns

I can't get my head round why? When asking this question they told me it was another layer of protection against SQL injection. In stored procedures they are using the guid to find the primary key ID and then using the id anyway, I personally cannot see the benefit?

My questions are:

EDIT:

An example of the usage (pseudo code):

  1. I need a contact.
  2. Retrieve Contact Guid from QueryString.
  3. Call SQL Server stored procedure to get contact.
  4. In stored procedure: find the id in the contact table based on the guid.
  5. Use the id to get relevant values in others tables using the id as a foreign key.
  6. Return data.

Upvotes: 1

Views: 437

Answers (3)

Scott Chamberlain
Scott Chamberlain

Reputation: 127563

The main reason to have a int primary key and a separate Guid is for replication. Replication requires you specify a ROWGUIDCOL which must be of type uniqueidentifier.

This is done because you may be generating data on multiple servers and merging the data together during the replication process. To do that you need a ID that will be unique to the row even if the rows are generated across two servers that have no communication.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

If somebody looks at your website's html source, they might see that they are submitting a form and passing their ID which is "1003". So from there, they could construct a form that passes the ID of "1002" and see somebody else's data. It's not exactly "SQL Injection", it's more like parameter-guessing.

But if they see their ID is a GUID, which just looks like random characters, they'd have a much harder time guessing what other valid IDs might be in your system.

Upvotes: 5

Daniel Moses
Daniel Moses

Reputation: 5858

The main benefit would be: We already have a bunch of code that uses PrimaryID, including ORM code that maps PrimaryID as the primary key column. The only other reason I can see to keep PrimaryID is for human readability.

Also, I should let you know that GUID doesn't always protect you from data scraping. You could very well be using a database that generates GUIDs in a predictable pattern (sequential or otherwise).

Upvotes: 0

Related Questions