SQL Server: Explaining to C-Level Executives the impact of using Primary Key of VARCHAR(50) in many tables throughout a database?
The document control management system needs to perform typical document control management system tasks...for many years (at least two decades).


The end of extended support for SQL Server 2014 Service Pack 3 is 7/9/2024.  That's 5 years from now.  So I think your first step should be to see if / when the vendor intends to get certified on a more modern version of SQL Server.  This is more important if you are subject to compliance rules that require software to recieve vendor support and security updates.



As far as other things to share with executive stakeholders, it's hard to make general statements, because you certainly won't be comparing systems that have the exact same schema with the exception of the primary key data type.

If you are comparing this product to a similar one that uses surrogate (integer) keys, assuming the varchar(50) primary keys are also clustered indexes (the default for SQL Server), issues you might raise would be:


You will likely need to pay for increased disk space for the database


the varchar(50) key will take up more space on disk than the integer key
this space increase is multiplied by any nonlclustered indexes on these tables, since the clustering key is included in them automatically
it's common to say "disk is cheap," but some very smart folks have disagreed with that assessment: Disk Is Cheap! ORLY?

Because of the above, you will also spend more on disk space for backups
You will likely need to pay for more RAM


since these tables and indexes are bigger on disk, they will be bigger in RAM too

Some reports and screens might be slower to load


index scans and seeks will have to consume more pages, and would take longer than they equivalent scan of an integer-keyed-table

Some reports and screens might be faster to load


since the text of this serial number would be stored in any table that references it as a foreign key, you might be able to avoid joins sometimes



Notice all of the hedge words above like "might" and "likely" - like I said, I don't think you can make a lot of useful general statements about this situation.