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.