Primary Key considerations

Question

question

Paul 4 asked Jun 4, '10

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.

sql-server-2005 database-design primary-key datatypes table-designer

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-04T11:30:21Z

Matt Whitfield answered Jun 4, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-06-04T13:14:03Z

TimothyAWiseman answered Jun 4, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-06-07T06:23:11Z

Paul 4 answered Jun 7, '10

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.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ commented · Jun 07, 2010 at 06:49 AM

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).

0 ·

question