SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DROP TABLE IF EXISTS dbo.TestTable;
CREATE TABLE dbo.TestTable (VarcharString varchar(30) COLLATE Latin1_General_100_CI_AS,
                            NvarcharString nvarchar(30) COLLATE Latin1_General_100_CI_AS,
                            UTF8String varchar(30) COLLATE Latin1_General_100_CI_AS_SC_UTF8);
INSERT INTO dbo.TestTable (VarcharString,NvarcharString, UTF8String)
SELECT V.String,V.String,V.String
FROM (VALUES(N'   Simple string   '), --19 characters
            (N'   Weißbier   '), --14 characters 
            (N'   Smile!🙂   '))V(String); --14 characters (🙂 counts as 2)
3 rows affected

SELECT *
FROM dbo.TestTable;


VarcharString
NvarcharString
UTF8String


   Simple string
   Simple string
   Simple string

   Weißbier
   Weißbier
   Weißbier

   Smile!??
   Smile!🙂
   Smile!🙂


SELECT DATALENGTH(VarcharString) AS VarcharLen,
       DATALENGTH(NvarcharString) / 2 AS NvarcharLen,
       DATALENGTH(UTF8String) AS UTF8Attempt1,
       DATALENGTH(UTF8String) / 2 AS UTF8Attempt2
FROM dbo.TestTable;


VarcharLen
NvarcharLen
UTF8Attempt1
UTF8Attempt2


19
19
19
9

14
14
15
7

14
14
16
8


SELECT VarcharString,
       LEN(VarcharString) AS Length,
       DATALENGTH(VarcharString) AS Datalength,
       LEN(TRIM(VarcharString)) + (DATALENGTH(VarcharString) - DATALENGTH(TRIM(VarcharString)))
FROM dbo.TestTable;


SELECT UTF8String,
       LEN(UTF8String) AS Length,
       DATALENGTH(UTF8String) AS Datalength,
       LEN(TRIM(UTF8String)) + (DATALENGTH(UTF8String) - DATALENGTH(TRIM(UTF8String))) AS UTF8Length,
       DATALENGTH(UTF8String COLLATE Latin1_General_100_CI_AS) AS UTF8CollatedLength
FROM dbo.TestTable;


VarcharString
Length
Datalength
(No column name)


   Simple string
16
19
19

   Weißbier
11
14
14

   Smile!??
11
14
14




UTF8String
Length
Datalength
UTF8Length
UTF8CollatedLength


   Simple string
16
19
19
19

   Weißbier
11
15
14
14

   Smile!🙂
10
16
13
14


DROP TABLE dbo.TestTable;

VarcharString	NvarcharString	UTF8String
Simple string	Simple string	Simple string
Weißbier	Weißbier	Weißbier
Smile!??	Smile!🙂	Smile!🙂