基于名称生成5位唯一字符串ID及作为主键的可行性咨询

Great question—let’s break this down into two clear parts: assessing your ID generation approach, and comparing the performance implications of string vs integer primary keys.

Part 1: Is Your ID Generation Scheme Reasonable? #

Your core idea is solid: generating human-readable IDs tied to the company name (like Northwind’s CustomerID) is super useful for quick identification, and adding a uniqueness check ensures you avoid duplicates. That said, there are a few tweaks and gaps to address:

Strengths of Your Approach #

• Readability-first priority: Starting with the simplest, most intuitive ID (first segment of the name) aligns perfectly with Northwind’s design, making IDs easy to associate with the company at a glance.
• Uniqueness guard: Building in a check for existing IDs prevents collisions, which is critical for a primary key.

Areas to Improve #

1. Length mismatch in the first rule
   Your first case tries to take SUBSTRING(@StringName,1,6) but you need a 5-character ID. This will generate a 6-character string that won’t fit your NCHAR(5) variable. Fix this to SUBSTRING(@StringName,1,5).

2. Inefficient duplicate check
   Using COUNT(ID) to check for duplicates forces the database to scan all matching rows, even if it finds a duplicate immediately. Instead, use EXISTS—it stops searching as soon as it finds a match, which is much faster:

   -- Replace your COUNT check with this logic
   IF EXISTS(SELECT 1 FROM MyTable WHERE StrCode = @StrCode)
       RETURN 1 -- Duplicate exists
   ELSE
       RETURN 0 -- No duplicate
   

   Also, scalar user-defined functions (UDFs) can be slow when called repeatedly (e.g., during bulk inserts). Consider rewriting this as an inline table-valued function or integrating the check directly into your ID generation logic to avoid performance hits.

3. Incomplete fallback rules
   Your current rules only cover three scenarios, but there will be cases where all three generate duplicates. You need a final fallback, like appending an incrementing number. For example:

   • If ALFKI is taken, try ALFK1, then ALFK2, etc.
   • To implement this, query the highest existing suffix for the base ID and increment it.

4. Case and special character handling
   Northwind’s IDs are all uppercase and free of special characters. Normalize your input to avoid issues:

   • Convert the name to uppercase (or lowercase) with UPPER(@StringName) to prevent duplicates like Alfki and ALFKI (depending on your database’s collation settings).
   • Strip out non-alphabetic characters (spaces, punctuation, accents) before generating the ID—otherwise you might end up with IDs containing hard-to-use symbols.

5. Short name edge cases
   What if the company name is shorter than 5 characters? For example, a company named "Zoo"—your first rule would generate ZOO (padded to 5 characters with NCHAR). You might want to handle this by padding with a consistent character (like X) or using a different rule for short names.

Part 2: String Primary Key vs INT Primary Key Performance #

Using a string ID (like NCHAR(5)) as a primary key will have performance tradeoffs compared to an INT primary key, but the impact depends on your data scale:

Performance Downsides of String Primary Keys #

1. Larger storage footprint
   An INT takes 4 bytes of storage, while NCHAR(5) takes 10 bytes (since Unicode characters are 2 bytes each). This adds up:

   • Your primary key index will be larger, meaning more disk I/O when reading/writing.
   • Any foreign keys referencing this primary key will also take 10 bytes per row, increasing storage in related tables.

2. Slower index operations
   Integer indexes are more compact, so the database can fit more entries per index page. This reduces the number of pages it needs to read/write during queries, inserts, or updates. String comparisons are also slightly slower than integer comparisons, though this is negligible for small datasets.

3. Worse cache efficiency
   Since string keys take more space, your database’s buffer cache can hold fewer primary key entries. This means more frequent disk reads for frequently accessed data, which slows down queries.

When String Keys Are Acceptable #

If your dataset is small (e.g., thousands to tens of thousands of customers), the performance difference will be barely noticeable. The readability benefit (being able to look at ALFKI and immediately know it’s Alfreds Futterkiste) can outweigh the minor performance hit—this is exactly why Northwind uses string IDs.

A Middle Ground #

If you want the best of both worlds:

• Use an INT as your primary key (for performance).
• Add a unique constraint on your generated string ID column (for readability and uniqueness).
• This way, you get fast index operations and compact storage, while still having human-readable IDs for debugging and user-facing tasks.

内容的提问来源于stack exchange，提问作者Oak_3260548