1

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:

  • Sadly this is not the worst approach I've ever seen. – kevinsky Aug 9 at 14:12
  • 1
    A simple way to explain repeating a larger piece of data in multiple tables instead of inventing an arbitrary, smaller key to tie them together without repetition, is take Netflix or Hulu, if you download a movie onto one TV, any device in the house can watch that one copy, instead of downloading and storing a copy on every device. If you frame that as if every byte of storage used on every device costs you $100, you will quickly see them lean toward the more optimal storage/pointer model than splatter the key all over the place. Also think of the impact of changing a real-world key. – Aaron Bertrand Aug 9 at 16:07
2

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.

-1

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.

  • Even if it didn't contain numbers and symbols, I'd be hesitant to use an integer to store this. What if a serial number starts with a zero? – Josh Darnell Aug 9 at 14:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.