A quick look at SQL Server UTF-8 collations

A client asked me about SQL Server collations, and if they should consider the new UTF8 collations (new since SQL Server 2019). I tried to hide my blank stare of ignorance, and promised them I’d look it up and get back to them.

Not gonna lie, I think UTF and Unicode can be pretty confusing at times, so I did some googling and some testing, and here’s what I found.

What’s the deal?

EDIT: The comment section adds a lot of useful in-depth information and corrections to some errors I’ve made in the post.

Ages ago, humanity defined (for the purposes of computing, at least) that 8 bits would be just the right size to store any character you could think of in an electronic medium. Well, actually, it used to be just 7 bits at some point, but I digress. 8 bits gives you the possibility to construct any one of 2⁸ characters in a byte. Using the ASCII table, we could translate any character to an integer value, or vice versa.

Did I say “any character”? I meant to say character in a small subset of western languages, because surely nobody in Japan would be using computers, right? In hindsight, this was a pretty dumb omission, albeit every byte was precious in the years before cloud computing.

The bolt-on solution to fix this omission was “code pages”. Essentially, those 255 different character codes could be defined to mean different things depending on how you read them. You see where this is going, right?

In the end, some committee finally figured out that we could use more than 8 bits to store text values. I know, right? But turning every single piece of text on the planet into 32-bit blocks was not really a practical idea for a number of reasons, least of which was storage. So they devised a way to declare a character using 8, 16, 24 or 32 bits, depending on how “exotic” it is – this is what’s known as a “variable-length encoding”. This is UTF-8.

Some definitions

At this point, I need to define some words:

• A “character” is one letter in the alphabet, one emoji or one kanji, depending on your writing preference.
• A “byte” is the basic storage unit of 8 bits.
• “ASCII” is the legacy 8-bit characterset.
• “Unicode” is the standardized characterset that encompasses all (current) languages and writing styles.

There are three flavours of UTF:

• UTF-8 is variable-length. Common western characters are 8 bits, but characters can be anything between 8 and 32 bytes.
• UTF-16 is fixed-width: every character takes up 16 bits (two bytes)
• UTF-32 is fixed-with: always 32 bits, but not very common.

SQL Server’s nvarchar datatype uses UTF-16, and so does Windows. It’s easy and predictable, but it does take up a bit of storage and RAM.

varchar and nvarchar in SQL Server

SQL Server can store string values in varchar or nvarchar, where varchar stores each character as 1 byte*, and nvarchar uses 2 bytes (UTF-16) for each character.

As a rule of thumb, you should use nvarchar to store anything that looks like a name, and varchar for anything that looks like a code (currency codes, SWIFT codes, country codes, etc). Codes are often indexed and regularly used to join and aggregate things, so you’ll want to keep those small to manage your storage. Anything else, put it in an nvarchar and forget everything about encoding.

At least, that used to be my blanket recommendation until today. Though I’d argue that most people reading this post will be fine with this advice for many years to come.

Which brings us to the subject of collations

I remember it like yesterday. SQL Server 2000 was the version that broke a lot of my old web code with the introduction “collations”. A collation order in SQL Server

• defines when characters are alike and when they are not – a collation can be
  • case-sensitive (“A” is different from “a”),
  • accent sensitive (“á” is different from “a”),
  • kana-sensitive (“あ” is different from “ア”),
  • … and a few other switches
• sets other likeness rules – for example,
  • whether “ss” and “ß” are equivalent, or
  • whether “æ” and “ae” are equivalent
• determines the sort order of two strings
• defines a codepage for 8-bit characters

When you think about it, collation orders affect a lot of things we do with string values in SQL Server – should we combine two values in a GROUP BY? How do we sort them with an ORDER BY? Are they equal or different? And if you would try to compare or combine two strings of different collations, you’d run into a collation error, because you’re literally trying to compare apples to oranges.

In SQL Server, you can define the collation order on the server, the database, even for individual columns and parameter values.

Introducing UTF-8 collations

Starting with SQL Server 2019, there are a couple of new UTF-8 collations, meaning that you can apply a UTF collation order to a varchar value. Meaning you can store multi-byte characters in regular old varchar columns!

Let’s take it for a spin and see how UTF-8 compares to the old ways:

CREATE TABLE #helloworld (
    Plain       varchar(100) COLLATE Finnish_Swedish_100_CI_AS NULL,
    nPlain      nvarchar(100) COLLATE Finnish_Swedish_100_CI_AS NULL,
    UTF         varchar(100) COLLATE Finnish_Swedish_100_CI_AS_SC_UTF8 NULL,
    nUTF        nvarchar(100) COLLATE Finnish_Swedish_100_CI_AS_SC_UTF8 NULL
);

INSERT INTO #helloworld
VALUES (N'Hello world', N'Hello world', N'Hello world', N'Hello world'),
       (N'Chào thế giới', N'Chào thế giới', N'Chào thế giới', N'Chào thế giới'),
       (N'こんにちは世界', N'こんにちは世界', N'こんにちは世界', N'こんにちは世界'),
       (N'👋🌍', N'👋🌍', N'👋🌍', N'👋🌍');

I’m prefixing string values in my code with an “N” to tell SQL Server that I’m using unicode strings. This is a pretty good practice to get used to for when you’re working with non-ASCII values. Without the N prefix, SQL Server will implicitly convert the data to varchar, potentially losing valuable information.

Here’s how the data comes out:

SELECT Plain, DATALENGTH(Plain) AS bytes, LEN(Plain) AS [len],
       nPlain, DATALENGTH(nPlain) AS bytes, LEN(nPlain) AS [len],
       UTF, DATALENGTH(UTF) AS bytes, LEN(UTF) AS [len],
       nUTF, DATALENGTH(nUTF) AS bytes, LEN(nUTF) AS [len]
FROM #helloworld;

Using non-ASCII characters in plain varchar fields just converts them to “?” characters, which is expected.

Note how nvarchar completely ignores UTF-8 collation. nvarchar is UTF-16. It still uses the defined collation order – string comparisons, sorting rules, etc – but nvarchars are always stored in two bytes.

The all-ANSI phrase “Hello world” takes up a single byte per character in varchar and two in nvarchar, just like we expected.

The Vienamese text contains some plain-ANSI characters as well as some unicode characters. Plain varchar won’t work here. In nvarchar, it uses two bytes per character, but the UTF-8 varchar uses about 1.4 characters on average!

The Japanese hiragana characters use two bytes per character in nvarchar and three bytes in UTF-8. So that’s worse than using nvarchar.

Finally, the emojis (yes, they literally went with all written languages) use two bytes in nvarchar and four bytes in nvarchar and UTF-8. To be fair, the storage space is 8 bytes across all types, but the definition of “length” varies for some reason.

Pros of UTF-8 collations

• UTF-8 collation can save you some space when the majority of your string values are in 8-bit ASCII, because it dynamically allocates space for longer characters.

Cons of UTF-8 collations

• SQL Server will implicitly convert between varchar and nvarchar, but it won’t implicitly re-collate strings. If you’re going for a UTF collation, you would be wise to make the entire database in the same collation or spend time manually adding COLLATE keywords all over your codebase.
• You could run into the maximum length of the varchar field with UTF-8, even though you’re seemingly inserting a shorter string. If you have a currency field in a char(3) type with a UTF collation, and a malicious user enters TH฿:

DECLARE @t TABLE (
    ccy char(3) COLLATE Finnish_Swedish_100_CI_AS_SC_UTF8
)

DECLARE @ccy nvarchar(10)=N'TH฿';

INSERT INTO @t
SELECT @ccy;

Msg 8152, Level 16, State 30, Line 7
String or binary data would be truncated.

Want more?

Erland Sommarskog has a 75-minute recorded sessions about collations, which is well worth watching.