Pasted

-- These examples support the following bug report for SQL Server 2019 CTP 2.1:
-- https://feedback.azure.com/forums/908035-sql-server/suggestions/36136810-utf-8-in-sql-2019-collation-precedence-allows-for
 
-- By: Solomon Rutzky
-- On: 2018-11-28
-- https://SqlQuantumLeap.com/
 
 
------------------------------------------------------------------------------
-- SETUP:
USE [master];
 
IF (DB_ID(N'SilentTruncationTest') IS NULL)
BEGIN
    CREATE DATABASE [SilentTruncationTest] COLLATE Ukrainian_100_CI_AS;
    ALTER DATABASE [SilentTruncationTest] SET RECOVERY SIMPLE;
END;
GO
 
 
 
-- !! HIGHLIGHT EACH "TEST" SECTION BELOW AND EXECUTE IT INDIVIDUALLY !!
 
-------------------------------------------------------
-- TEST 1:
USE [SilentTruncationTest];
 
SELECT COLLATIONPROPERTY(N'Ukrainian_100_CI_AS', 'CodePage'); -- 1251
SELECT CHAR(212); -- Ф
 
 
 
-- Convert to UTF-8:
SELECT 'Ф' COLLATE Ukrainian_100_CI_AS_SC_UTF8;
-- {empty string}
 
SELECT SQL_VARIANT_PROPERTY('Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8, 'MaxLength');
-- 1 (but it should be 2)
 
SELECT CONVERT(VARBINARY(4), 'Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [Bytes],
       DATALENGTH('Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [NumBytes];
-- 0x   0
 
 
 
-- Convert to UTF-8 again, but add a character to make it 2 bytes:
SELECT 'ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8;
-- Ф (the "X" is missing)
 
SELECT SQL_VARIANT_PROPERTY('ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8, 'MaxLength');
-- 2 (but it should be 3)
 
SELECT CONVERT(VARBINARY(4), 'ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [Bytes],
       DATALENGTH('ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [NumBytes];
-- 0xD0A4   2
GO
 
 
------------------------------------------------------------------------------
-- TEST 2:
ALTER DATABASE [SilentTruncationTest] COLLATE Hebrew_100_CI_AS_SC;
 
 
SELECT COLLATIONPROPERTY(N'Hebrew_100_CI_AS_SC', 'CodePage'); -- 1255
SELECT CHAR(243); -- ף
 
 
 
-- Convert to UTF-8:
SELECT 'ף' COLLATE Hebrew_100_CI_AS_SC_UTF8;
-- {empty string}
 
SELECT SQL_VARIANT_PROPERTY('ף' COLLATE Hebrew_100_CI_AS_SC_UTF8, 'MaxLength');
-- 1 (but it should be 2)
 
SELECT CONVERT(VARBINARY(4), 'ף' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [Bytes],
       DATALENGTH('ף' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [NumBytes];
-- 0x   0
 
 
 
-- Convert to UTF-8 again, but add a character to make it 2 bytes:
SELECT 'ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8;
-- ף (the "X" is missing)
 
SELECT SQL_VARIANT_PROPERTY('ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8, 'MaxLength');
-- 2 (but it should be 3)
 
SELECT CONVERT(VARBINARY(4), 'ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [Bytes],
       DATALENGTH('ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [NumBytes];
-- 0xD7A3   2
 
 
 
------------------------------------------------------------------------------
-- TEST 3:
 
-- DROP TABLE #Test;
CREATE TABLE #Test
(
    VC VARCHAR(20) COLLATE Hebrew_100_CI_AS_SC_UTF8,
    NVC NVARCHAR(20) COLLATE Hebrew_100_CI_AS_SC_UTF8,
);
 
INSERT INTO #Test ([VC], [NVC]) VALUES ('Test VARCHAR:', N'Test NVARCHAR:');
 
SELECT CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228); -- אבגדה
 
SELECT [VC] + 'אבגדה' AS [VARCHAR],
       [NVC] + 'אבגדה' AS [NVARCHAR]
FROM   #Test;
-- Test VARCHAR:אב    Test NVARCHAR:אבגדה
 
DECLARE @TestVar VARCHAR(5) = CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228);
SELECT @TestVar; -- אבגדה
 
SELECT [VC] + @TestVar AS [VARCHAR],
       [NVC] + @TestVar AS [NVARCHAR]
FROM   #Test;
-- Test VARCHAR:אב    Test NVARCHAR:אבגדה
 
 
-- Same string as @TestVar, but variable is 1 byte larger:
DECLARE @TestVar2 VARCHAR(6) = CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228);
SELECT @TestVar2; -- אבגדה
 
SELECT [VC] + @TestVar2 AS [VARCHAR],
       [NVC] + @TestVar2 AS [NVARCHAR]
FROM   #Test;
-- Test VARCHAR:אבג  Test NVARCHAR:אבגדה
 
 
 
 
 
 
/*
USE [master];
DROP DATABASE [SilentTruncationTest];
*/