Storing Emoji and Short Messages: nvarchar, UTF-8 and Length

The message looks cheerful on screen and arrives in the database as question marks. An emoji needs a Unicode path through the client, parameter, column, and output. Character counting and storage capacity then need separate rules, because one visible symbol does not always mean one character.

Protect the Whole Unicode Path for Emoji

Use Unicode parameters from the application and an appropriate destination type. An nvarchar column cannot restore a character already replaced during client encoding or a varchar conversion. In T-SQL examples, the N prefix marks a Unicode literal. Check the driver parameter type and size too. A correct column at the end of a broken path stores broken text faithfully.

I test the actual application connection before changing column lengths. Copying a working literal into SSMS tests the editor path, not the form that failed. Which parameter type does the application send? Check that along with the database column and the display component. Unicode support is a chain, and every link needs to preserve the intended value.

Choose a Supplementary Character Collation for Emoji

Many emoji live outside Unicode's basic multilingual plane. UTF-16 represents those code points with surrogate pairs. An nvarchar column stores the pair using two byte pairs. A supplementary character aware collation, identified here by _SC, allows string functions to treat the pair as one code point. It does not shrink the stored pair into fewer bytes.

DECLARE @symbol nvarchar(10)=N'😀';
SELECT LEN(@symbol COLLATE Latin1_General_100_CI_AS) AS LegacyLength,
       LEN(@symbol COLLATE Latin1_General_100_CI_AS_SC) AS SupplementaryLength,
       DATALENGTH(@symbol) AS StorageBytes;

The comparison isolates collation behavior while keeping the same stored value. DATALENGTH measures bytes, whereas LEN follows character counting rules and excludes trailing spaces. A change in LEN does not mean SQL Server compressed the value. Collation also affects equality, sorting, and searches, so review those effects before changing a production column for one string function.

Put UTF-16 and UTF-8 Beside Each Other

SQL Server 2019 and later support UTF-8 collations for char and varchar. The UTF8 suffix changes their encoding. It does not change nvarchar into UTF-8. The sample stores the same Unicode input in both types so you can compare value preservation and bytes. Run all temporary table examples in the same session.

CREATE TABLE #Message
(
    MessageID int NOT NULL PRIMARY KEY,
    NMessage nvarchar(560) COLLATE Latin1_General_100_CI_AS_SC NOT NULL,
    VMessage varchar(1120) COLLATE Latin1_General_100_CI_AS_SC_UTF8 NOT NULL
);
INSERT #Message(MessageID,NMessage,VMessage)
VALUES(1,N'Hello 😀',N'Hello 😀'),(2,N'Plain ASCII',N'Plain ASCII');
SELECT MessageID,NMessage,VMessage,
       LEN(NMessage) AS Utf16CodePoints,DATALENGTH(NMessage) AS Utf16Bytes,
       LEN(VMessage) AS Utf8CodePoints,DATALENGTH(VMessage) AS Utf8Bytes
FROM #Message;

ASCII uses fewer encoded bytes in UTF-8 than UTF-16. Other characters use different widths, and a supplementary code point uses four bytes in both encodings. Measure a representative message population instead of declaring either type universally smaller. Include indexing, client handling, conversion paths, and existing schema conventions in the choice. One attractive sample does not describe every user's text.

Understand What the Length Parameter Means

The n in nvarchar(n) counts byte pairs, not user visible characters. A supplementary code point needs two of those pairs. The n in varchar(n) counts bytes, even under UTF-8. A message of two hundred eighty code points therefore needs room for up to five hundred sixty UTF-16 byte pairs or eleven hundred twenty UTF-8 bytes.

Those bounds describe a code point limit, not a grapheme limit. A visible emoji can contain several code points joined into one displayed symbol. If the product limits visible symbols, the maximum encoded storage requirement needs its own bound or a larger storage type. Document the rule before a database length becomes an accidental product definition.

Count Trailing Spaces Deliberately

LEN ignores trailing spaces. A message counter that must count them can append a sentinel and subtract its contribution. Convert to nvarchar(max) before appending so a fixed length expression cannot truncate the sentinel. Use the supplementary character aware collation for code point counting. DATALENGTH remains a separate check of encoded storage.

DECLARE @message nvarchar(560)=N'Hello 😀  ';
SELECT LEN(@message COLLATE Latin1_General_100_CI_AS_SC) AS WithoutTrailingSpaces,
       LEN((CONVERT(nvarchar(max),@message) COLLATE Latin1_General_100_CI_AS_SC)
           +NCHAR(1))-1 AS IncludingTrailingSpaces,
       DATALENGTH(@message) AS Utf16Bytes;
SELECT MessageID,
       LEN(CONVERT(nvarchar(max),NMessage)+NCHAR(1))-1 AS CodePointsIncludingSpaces
FROM #Message;

I compare the form's counter with the stored string on boundary tests. The two counts differ whenever a framework counts UTF-16 code units and SQL counts code points under an SC collation. A database saying accepted and a form saying too long can both follow their own rules perfectly. Agree on one rule and test it through the real input path.

Keep Visible Emoji Separate From Code Points

A heart with a variation selector, a skin tone modifier, and a joined family sequence each show why display counting is harder. SQL LEN does not implement a product's grapheme segmentation rule merely because the collation supports supplementary characters. It counts the component code points under the selected string rules. Application libraries need to handle visible cluster counting when that is the requirement.

DECLARE @heart nvarchar(20)=N'❤️',@family nvarchar(40)=N'👨‍👩‍👧‍👦';
SELECT N'Heart' AS ExampleName,
       LEN(@heart COLLATE Latin1_General_100_CI_AS_SC) AS CodePoints,
       DATALENGTH(@heart) AS Utf16Bytes
UNION ALL
SELECT N'Family',LEN(@family COLLATE Latin1_General_100_CI_AS_SC),DATALENGTH(@family);

Test combining marks, variation selectors, flags, and joined sequences with the fonts your application uses. Rendering support differs from storage support. A preserved value can display as boxes on a device lacking the glyphs. Question marks caused by conversion are a data problem. Missing glyph boxes are a display problem. Inspect the actual stored code points before blaming the table.

Reject Long Input Before It Is Truncated

Validate length while the input still has a sufficiently large parameter type. A parameter declared too short can truncate before a CHECK constraint sees it. For a code point based limit, a database check can use the same sentinel expression. Test spaces and supplementary values at the boundary. Review error handling so rejected input receives a helpful application message.

CREATE TABLE #BoundedMessage
(
    MessageID int NOT NULL PRIMARY KEY,
    MessageText nvarchar(560) COLLATE Latin1_General_100_CI_AS_SC NOT NULL,
    CHECK(LEN(CONVERT(nvarchar(max),MessageText)+NCHAR(1))-1<=280)
);
DECLARE @input nvarchar(max)=N'Hello 😀';
IF LEN((@input COLLATE Latin1_General_100_CI_AS_SC)+NCHAR(1))-1>280
    THROW 50001,'The message exceeds the code point limit.',1;
INSERT #BoundedMessage(MessageID,MessageText) VALUES(1,@input);

Avoid blindly cutting text at a byte boundary. That can split an encoded character or a visible joined sequence. Even a code point safe truncation can break a grapheme. Let the application apply the agreed segmentation policy, then revalidate the preserved value. A half smile is rarely the intended product feature.

Verify an Exact Round Trip

Compare binary representations after converting the UTF-8 value back to Unicode. That checks the stored characters rather than a case insensitive linguistic comparison. Perform equivalent tests through your application, exports, and downstream consumers. Include CSV and JSON output encoding, because a correct table cannot protect a later file written with the wrong character encoding.

SELECT MessageID,
       CASE WHEN CONVERT(varbinary(max),NMessage)=
                      CONVERT(varbinary(max),CONVERT(nvarchar(max),VMessage))
            THEN N'Exact Unicode match' ELSE N'Inspect conversion' END AS RoundTripCheck
FROM #Message;

I keep the test strings with the application's boundary tests. Store emoji using a complete Unicode path, choose capacity by bytes, and define the counter by its actual unit. Those three decisions prevent the familiar question marks and make a short message limit something the user and the database can both understand.

Related reading on this blog: UTF-8 Collations in SQL Server 2019: When They Save Space and LEN vs DATALENGTH: Trailing Spaces, Unicode Bytes and NULL.

A message length is not a universal character count, it is a limit with a defined unit.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.