How does using char(17) as primary key to store VIN numbers in a table with SQL Server affect performance?

Question

I am designing a database with a table to store vehicles and since the vehicle identification number is a 17 digit alphanumeric serial number my idea is to use it as the primary key, with a datatype of char(17).

Numerous other tables will then have the VIN as a foreign key.

A number of queries/searches will run with the VIN number as parameter since it's how we would like to track the vehicles as well as other data related to it.

The VIN number will never change, but I'm unsure if it would cause any serious performance degradation (or other complications I'm not aware of) since some queries will use joins and others not :/

By using the VIN as primary key I do not have to create a unique constraint / additional index - BUT it has to be char(17) a data type other than int for which primary keys are supposedly optimized...

What I'm also not 200% sure of is that every VIN number out there is the same length (very unlikely) but in that case how would using a varchar(17) affect the whole situation... if at all.

Thanks!

Answer 1

Just a personal opinion..

I always use int as a primary key. Primary key is in most cases always a clustered index. It's 4 bytes vs. 17 bytes and you can always put a non-clustered index on your VIN column. Keep things simple and clear. It's just my opinion though.

Answer 2

In my opinion, regarding performance, it indeed is not a good idea. It very much depends how many cars you will store in the database though. On the other hand, if your applications and queries use the VIN as parameter then it is the best option as the column is indexed and must be unique.

hope this helps

ps: akward seeing other people's suggestions on this topic!