Primary Keys in SQL (Guid or Integer?)

Primary keys in SQL have come up quite a few times in my career so far. Let’s start by going back to the beginning with what a key really is and how it is used.

Each concept in programming typically starts with a record or a target. In the case of bank applications this would be an account, or sales would be a customer or an order. Regardless there is always some sort of unique object in coding that needs to be identified, tinkered with and some task completed. For a customer you would call them by their full name, Mike Calvert as an example. Unfortunately there are a lot of people named Mike Calvert in the world, so we need to learn to identify the specific Mike Calvert we are talking to.

From here a key was identified by using some sort of numbering scheme. Dynamics GP uses a limited alphanumeric scheme which you can use to assign a number such as MIKECALVERT01. This works until you run out of letters or you hit the maximum combinations and start coming up with one offs. The next step in the equation was to jump to auto incrementing integers, so Mike Calvert becomes 1, Mike Calvert 2 becomes 2, Jason Mills becomes 3, Howard Roberts becomes 4 etc. Everything was solved in the world because we now had an endless number that works forever. Moving into actual SQL syntax, you would have an issue with connecting up data links. Lets say you had to insert 50,000 records into this customer table, and you had a relationship table that referenced these people.

You now have to insert 50,000 records into the table, and then determine which of the 50,000 got assigned to a customer record because that record ID is generated when you insert it into the database. There are some tips and tricks around it but it is very messy. In a nut shell, if I wanted to find out what the ID was of Mike Calvert after I inserted 50,000 people into the database I would have to do a select to find Mike Calvert, and we run into the same issue we ran into before where there may be more than one Mike Calvert. At this point someone at Microsoft (thankfully) decided to put a Guid into play. Guid’s are by far the best key choice that I have come across as they provide three awesome things that keys need. The first and biggest requirement is being unique. Each Guid is unique no matter what table or database. If I put a customer record into a table with a Guid, I know that I can find that same customer record using that Guid from anywhere, without having to worry if that Guid will show up anywhere. The next big advantage is indexing. Indexing a guid is very efficient as they are the same length of characters, the same types of digits and are always unique. Finally, Guids can be generated prior to insert.

Generating a key prior to insert is a major advantage, as you can wrap up your 50,000 inserts into a single statement as well as the relationship. .NET provides a function called System.Guid.NewGuid that returns back a guid that you can use as a unique identifier in your inserts. Let us say that we generated our customer Mike Calvert and we created his guid. Any additional tables that required a relationship to Mike Calvert would already have this guid prior to inserting Mike Calvert, so I can create Mike Calvert and his sales histories and payment histories and insert it in one transaction instead of making multiple calls to fetch IDs. Fun stuff eh?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>