x

Primary Key considerations

I have some tables containing lookup data. These tables are joined to tables with millions of rows by their primary key. The lookup tables will NEVER contain more than 200 rows each.

I was under the impression that a narrower primary key is better than a wider one. With this in mind I thought that a tinyint would be the best data type of the primary key on the tables described above.

My boss told me that he is sure that the primary key should be of type int and no smaller. He said that he has read on more than one occasion about how SQL Server's optimiser works most efficiently with ints. I've never heard this said before and can find nothing to back up his statement after lots of searching.

I have looked at execution plans and can see no reason not to use a tinyint in this scenario.

Can anyone point me to any information on this or shed any light on the topic?

Many thanks,

Paul.

more ▼

asked Jun 04 '10 at 11:09 AM in Default

Paul 4 gravatar image

Paul 4
18 1 1 1

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

3 answers: sort voted first

A tinyint would be marginally faster in IO terms, because less data would have to be read. However, with just 200 rows, it's hardly going to make much difference. Even if you had say 50,000 rows and went for a smallint, the difference would be marginal. I wouldn't say it would be faster to go with int's, but I would say there is little point in going with anything smaller.

more ▼

answered Jun 04 '10 at 11:30 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

agreed, never heard of difference between various INT types other than data width affecting access times.
Jun 04 '10 at 11:48 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

I have done some testing on something similar when evaluating the advantages of a natural key that was varchar or an artificial key that was int. Generally a smaller primary key will run faster.

I have never tested int versus tinyint though. Without testing, I am inclined to agree fully with Matt that the difference would be marginal at best. I am fairly confident (again without testing) that a tinyint will certainly not be any slower than an int.

There may be other reasons than speed to stay with the tinyint though. If you know for certain that your lookup table will never exceed 200 than using tinyint acts as a constraint to limit its size to 255 at most (you can use a check constraint to put a hard cap at 200 if you wish). Also, it signals to anyone looking at the table what its maximum size is, and this can be useful. Finally, while this probably does not matter for a primary key, limiting the size of a field signals to UI designers the maximum amount of space they need to display that value.

more ▼

answered Jun 04 '10 at 01:14 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

I agree with these assertions. The use of tinyint over int is more of a size issue than speed issue. It helps to encourage proper sizing of fields for all involved.
Jun 06 '10 at 12:08 PM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left

Many thanks for your responses.

Although the lookup table may only have 200 rows, it is linked by foreign key to a table with millions of rows. This larger table is frequently joined in queries to the lookup table. This is the main reason I was thinking of going with a tinyint instead of an int.

At least now I can go back to my boss and let him know that I can find no reason why an int would be more efficient than a tinyint.

more ▼

answered Jun 07 '10 at 06:23 AM

Paul 4 gravatar image

Paul 4
18 1 1 1

No worries - I can see what you meant now - sorry must have been half asleep before! In that case, I would say yes, go with the smaller key, but only if you are SURE that there will never be more than 255 rows in that table (assuming you don't want to use 0 as a key).
Jun 07 '10 at 06:49 AM Matt Whitfield ♦♦
(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1933
x107
x35
x34
x7

asked: Jun 04 '10 at 11:09 AM

Seen: 1951 times

Last Updated: Jun 04 '10 at 11:09 AM