June 3, 2010 at 10:02 am
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 narrow 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.
A colleague 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.
Can anyone point me to any information on this or shed any light on the topic?
Many thanks,
Paul.
June 3, 2010 at 10:14 am
I assume they are talking about the implicit conversion that would be required between datatypes in SQL 2000 and this can confuse the optimizer. This is for bit to Int , but possibly would apply to tinyint to Int. Though i could be completly wrong.
As far as i am aware this is no longer an issue in 2005, I would use tinyint as it is smaller and will ensure your tables never get larger than 255 rows.
Also, all of this can be tested and proven by analysing the execution plans.
June 4, 2010 at 2:57 am
He may indeed be thinking about an issue that no longer exists in SQL 2005. I shall have a look at some execution plans when I get a chance.
Many thanks,
Paul.
June 14, 2010 at 10:43 am
steveb. (6/3/2010)
I would use tinyint as it is smaller and will ensure your tables never get larger than 255 rows.
Steve is spot-on on this one but this may cause a headache in the future if business rules change and one or more of those tables goes from 200 to lets say 256 rows in size.
At the end of the day everything depends on how confident data modeler is in the accuracy of such business rule.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply