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:

https://feedback.azure.com/forums/908035-sql-server/suggestions/38804818-when-using-utf-8-varchar-10-should-mean-10-chara

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.

Leave a Reply

Your email address will not be published.