SQL: How to limit characters in a SQL Server string when varchar(n) won't work
In a recent post, I talked about how varchar(10) doesn’t mean up to 10 characters, particularly since the introduction of UTF-8 in SQL Server 2019.
So given I do want to limit the number of characters in strings at times, how exactly should I now do that?
A plan takes shape ?
One of my current plans is to do this:
Step 1: Start by forgetting that the number in brackets for the string data types is really relevant to a developer in any way.
Step 2: Create alias types for string, to just reinforce that.
Here’s an example:
Note that there are still things in SQL Server that do or don’t work with varchar vs varchar(max) so I’ll keep the distinction for now.
Step 3: Never use char, nchar, varchar, nvarchar, varchar(max) or nvarchar(max) again.
Step 4: Use a UTF-8 based collation every time, set at the DB level.
Step 5: Use constraints to limit the length of string values in databases, as shown in the main image above. Note that I’ve used our SDU_Tools function for checking string length because LEN() doesn’t work properly, and can’t be used safely in this situation, unless you write something more convoluted like this (yuck):
Step 6: Wonder how on earth we’re going to limit the size of values in variables and parameters, given they can’t have constraints…
Step 7: Petition Microsoft to give us a way to make the n in varchar(n) actually mean characters, perhaps by implementing varchar(10 characters) as valid syntax. (To avoid breaking the current syntax) Here’s a link to a UserVoice suggestion that you should vote for:
Would love to hear your thoughts on this. The database should be doing this, and I don’t currently see any really good plan, but this feels somewhat workable.
2019-08-01