SQL Server: Explaining to C-Level Executives the impact of using Primary Key of VARCHAR(50) in many tables throughout a database?

Question

Suppose a global, mid-sized company is considering a document control management solution that is backed by a SQL Server database and allows developers to create document form types using a form creation tool. Each form type created using the tool is represented in the database by a table with a primary key of datatype VARCHAR(50).

What impact would the requirement to use VARCHAR(50) primary keys have on system performance vs. a system using a more conventional design approach, e.g., this? Assume the core audience for the answer is mostly non-technical, i.e., C-Level Executives making a go or no-go decision on the solution.

Notes:

• The document control management system needs to perform typical document control management system tasks for a global mid-sized company for many years (at least two decades).

• This is a canned solution and the primary key data type cannot be altered.

• The VARCHAR(50) data type maps to the company's serial number for products. The serial number format is alphanumeric and can contain dashes.

• The system is web based and uses SQL Server 2014 and the .NET Framework (C#) technology stack.

• Questions researched before creating this question, all of which don't align with the technology stack and/or have answers too in the weeds for a non-technical audience:

  1. VARCHAR primary key - MySQL
  2. Should I replace my varchar primary key with an integer primary key?
  3. Can I use VARCHAR as the PRIMARY KEY?
  4. Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

Answer 1

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.

Answer 2

If the company's serial number contains characters other than digits, varchar seems to be the correct data type.

If it contains only digits, an integral data type would be better, since it probably saves space and makes comparisons faster.