Why use an int as a lookup table's primary key?

I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string). I understand that using a nvarchar(50) rather than an int would use way more space if it is linked to a table with many records. But on the other hand using the lookup value directly would basically save us doing a join. I can imagine this would be a big saving if the join is always required.

What are the advantages of using a int primary key (specifically for a lookup table) other than it being "the standard thing to do"?
more ▼

asked Mar 25, 2011 at 04:12 AM in Default

Jaco Briers gravatar image

Jaco Briers
23 1 1 3

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Kevin's answer is absolutely fantastic, but I think no discussion on primary keys can be complete without considering the pros and cons of natural, artificial, and surrogate keys, and the best place for that is Joe Celko's article at [http://www.informationweek.com/news/software/bi/showArticle.jhtml?articleID=201806814][1]

[1]: http://www.informationweek.com/news/software/bi/showArticle.jhtml?articleID=201806814
more ▼

answered Mar 25, 2011 at 11:00 AM

TimothyAWiseman gravatar image

15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 25, 2011 at 04:12 AM

Seen: 4444 times

Last Updated: Mar 25, 2011 at 04:40 AM