Como faço para definir uma string Unicode / NVARCHAR do SQL Server para um emoji ou caractere suplementar?

A codificação UCS-2 é sempre de 2 bytes por caractere e tem um intervalo de 0 a 65535 (0x0000 - 0xFFFF). UTF-16 (independentemente de Big Endian ou Little Endian) tem um intervalo de 0 - 1114111 (0x0000 - 0x10FFFF). O intervalo 0 - 65535 / 0x0000 - 0xFFFF de UTF-16 é de 2 bytes por caractere, enquanto o intervalo acima de 65536 / 0xFFFF é de 4 bytes por caractere.

O Windows e o SQL Server começaram usando a codificação UCS-2 porque ela estava disponível e o UTF-16 ainda não havia sido finalizado. Felizmente, no entanto, houve planejamento suficiente nos projetos de UCS-2 e UTF-16 para que os mapeamentos UCS-2 sejam um subconjunto completo dos mapeamentos UTF-16 (ou seja: o intervalo 0 - 65535 / 0x0000 - 0xFFFF de UTF-16 é UCS-2). E, o intervalo 65536 - 1114111 (0x10000 - 0x10FFFF) de UTF-16 é construído a partir de dois pontos de código no intervalo UCS-2 (intervalos 0xD800 – 0xDBFF e 0xDC00 – 0xDFFF, especificamente) que foram reservados para essa finalidade e, de outra forma, não têm significado. Essa combinação de dois Pontos de Código é conhecida como Par Surrogate, e os Pares Surrogate representam caracteres além do intervalo UCS-2 que são conhecidos como Caracteres Suplementares.

Todas essas informações explicam dois aspectos dos NVARCHARdados / Unicode no SQL Server:

1. Várias funções incorporadas (não apenas NCHAR()) não lidam com Pares substitutos / caracteres suplementares quando não estão usando um agrupamento com reconhecimento de caractere suplementar (SCA; ou seja, um com _SC, ou _140_ mas não _BIN*no nome) porque os agrupamentos não SCA (especialmente o SQL_Collations) foram originalmente implementados antes da conclusão do UTF-16 (em algum momento de 2000, acredito). Os não SQL_-Agrupamentos que possuem _90_ou _100_em seus nomes, mas não _SCpossuem suporte mínimo para Caracteres Suplementares em termos de comparação e classificação.
2. O conjunto completo de caracteres Unicode / UTF-16 pode ser armazenado, sem perda de dados, nos tipos de dados NVARCHAR/ NCHAR/ XML/ NTEXTporque UCS-2 e UTF-16 são exatamente as mesmas sequências de bytes. A única diferença é que o UTF-16 usa os pontos de código substitutos para construir pares substitutos, e o UCS-2 simplesmente não pode mapeá-los para nenhum caractere, portanto, eles aparecem para as funções internas como dois caracteres desconhecidos.

Com essas informações básicas em mente, agora podemos passar pelas perguntas específicas:

Gostaria SELECT NCHAR(128512);de devolver o mesmo:SELECT N'?';

Isso só pode acontecer se o banco de dados atual — onde a consulta está sendo executada — tiver um Collation padrão com reconhecimento de caracteres suplementares e aqueles que foram introduzidos no SQL Server 2012. Funções internas que possuem parâmetros de entrada de string podem ter o Collation fornecido inline através da COLLATEcláusula (ie LEN(N'string' COLLATE Some_Collation_SC)) e não precisa ser executado dentro de um Banco de Dados que tenha um Collation padrão SCA. No entanto, funções internas como NCHAR()aceitar um INTparâmetro de entrada e a COLLATEcláusula não são válidas nesse contexto (e é por isso que NCHAR()suporta apenas caracteres suplementares quando o banco de dados atual tem um agrupamento padrão que reconhece caracteres complementares; mas isso é desnecessário inconveniente que pode ser alterado, então vote na minha sugestão:A função NCHAR() deve sempre retornar Caractere Suplementar para valores 0x10000 - 0x10FFFF, independentemente do agrupamento padrão do banco de dados ativo ).

Existe uma explicação de por que, independentemente do agrupamento, o SQL Server pode entender e lidar com os caracteres estendidos, exceto da perspectiva de NCHAR?

Como o SQL Server pode armazenar e recuperar caracteres suplementares sem perda de dados foi explicado na seção superior desta resposta. Mas não é verdade que NCHARé a única função interna que tem problemas com caracteres suplementares (quando não está usando um agrupamento SCA). Por exemplo, LEN(N'?' COLLATE SQL_Latin1_General_CP1_CI_AS)retorna um valor de 2 enquanto LEN(N'?' COLLATE Latin1_General_100_CI_AS_SC)retorna um valor de 1.

Se você for para o segundo link postado na pergunta (ou seja, "Informações de agrupamento de caracteres suplementares da Microsoft") e rolar um pouco para baixo, verá um gráfico das funções internas e como elas se comportam com base no agrupamento efetivo.

Como faço para encontrar um agrupamento que tenha o sinalizador "caractere suplementar"?

Em uma versão do SQL Server anterior a 2012, você não pode. Mas, a partir do SQL Server 2012, você pode usar a seguinte consulta:

SELECT col.*
FROM   sys.fn_helpcollations() col
WHERE  col.[name] LIKE N'%[_]SC'
OR     col.[name] LIKE N'%[_]SC[_]%'
OR     (COLLATIONPROPERTY(col.[name], 'Version') = 3
      AND col.[name] NOT LIKE N'%[_]BIN%');

Sua consulta foi fechada, mas o padrão iniciado com SQLe os agrupamentos do SQL Server (ou seja, aqueles que começam com SQL_) foram preteridos por um tempo em favor dos agrupamentos do Windows (aqueles que não iniciam com SQL_). Portanto, os SQL_agrupamentos não estão sendo atualizados e, portanto, não têm versões mais recentes que incluam a _SCopção (e, a partir do SQL Server 2017, todos os novos agrupamentos suportam automaticamente caracteres suplementares e não precisam ou têm o _SCsinalizador; e sim, a consulta mostrado imediatamente acima explica isso, além de pegar os _UTF8agrupamentos adicionados no SQL Server 2019).

Você pode instalar agrupamentos em instâncias mais antigas?

Não, você não pode instalar Collations em uma versão anterior do SQL Server.

Como posso definir uma variável de string Unicode (por exemplo, nvarchar) para um caractere suplementar usando código (sem usar o caractere suplementar real) em um banco de dados onde o agrupamento "não contém o sinalizador de caractere suplementar (SC)"?
...
Embora o servidor seja o SQL Server 2008 R2, também estou curioso sobre as soluções para versões posteriores.

Quando não estiver usando um agrupamento SCA, você pode injetar pontos de código acima de 65535 / U+FFFF de duas maneiras:

1. Especifique o Par Surrogate em termos de duas chamadas para a NCHAR()função, cada uma com uma parte do par
2. Especifique o Par substituto em termos de conversão da VARBINARYforma da sequência de bytes Little Endian (ou seja, invertida).

Esses dois métodos de inserção de caracteres suplementares/pares substitutos funcionarão mesmo se o agrupamento efetivo for compatível com caracteres suplementares e deve funcionar da mesma forma em todas as versões do SQL Server, pelo menos até 2005 (embora provavelmente também funcione em SQL Server 2000 também).

Exemplo:

• Personagem:
                     ? ==
• Nome:                Pilha de Poo
• Decimal:            128169
• Ponto de código:       U+1F4A9
• Par substituto: U+D83D e U+DF21

SELECT N'?', -- ?
       UNICODE(N'?' COLLATE Latin1_General_100_CI_AS), -- 55357
       UNICODE(N'?' COLLATE Latin1_General_100_CI_AS_SC), -- 128169
       NCHAR(128169), -- ? in DB with _SC Collation, else NULL
       NCHAR(0x1F4A9), -- ? in DB with _SC Collation, else NULL
       CONVERT(VARBINARY(4), 128169), -- 0x0001F4A9
       CONVERT(VARBINARY(4), N'?'), -- 0x3DD8A9DC
       CONVERT(NVARCHAR(10), 0x3DD8A9DC), -- ? (regardless of DB Collation)
       NCHAR(0xD83D) + NCHAR(0xDCA9) -- ? (regardless of DB Collation)

ATUALIZAR

Você pode usar o seguinte iTVF para obter os valores do Par substituto (em ambos INTe na BINARYforma) de qualquer Code Point entre 65536 - 1114111 (0x010000 - 0x10FFFF). E, enquanto o parâmetro de entrada é do tipo INT, você pode passar no formato binário / hexadecimal do Code Point e ele será convertido implicitamente no valor inteiro correto.

CREATE FUNCTION dbo.GetSupplementaryCharacterInfo(@CodePoint INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

WITH calc AS
(
  SELECT 55232 + (@CodePoint / 1024) AS [HighSurrogateINT],
         56320 + (@CodePoint % 1024) AS [LowSurrogateINT]
  WHERE  @CodePoint BETWEEN  65536 AND 1114111
)
SELECT @CodePoint AS [CodePointINT],
       HighSurrogateINT,
       LowSurrogateINT,
       CONVERT(VARBINARY(3), @CodePoint) AS [CodePointBIN],
       CONVERT(BINARY(2), HighSurrogateINT) AS [HighSurrogateBIN],
       CONVERT(BINARY(2), LowSurrogateINT) AS [LowSurrogateBIN],
       CONVERT(binary(4), NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT)) AS [UTF-16LE],
       NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT) AS [Character]
FROM   calc;
GO

Usando a função acima, as duas consultas a seguir:

SELECT * FROM dbo.GetSupplementaryCharacterInfo(128169);

SELECT * FROM dbo.GetSupplementaryCharacterInfo(0x01F4A9);

ambos retornam o seguinte:

CodePoint  HighSurrogate  LowSurrgate  CodePoint  HighSurrgate  LowSurrgate  UTF-16LE   Char
INT        INT            INT          BIN        BIN           BIN                     actr
128169     55357          56489        0x01F4A9   0xD83D        0xDCA9       0x3DD8A9DC   ?

ATUALIZAÇÃO 2: Uma atualização ainda melhor!

Eu adaptei o iTVF mostrado acima para agora retornar 188.657 pontos de código para que você não precise ajustar nenhum valor específico. Claro, sendo um TVF, você pode adicionar uma WHEREcláusula para filtrar um ponto de código específico, ou intervalo de pontos de código, ou "caracteres semelhantes", etc. E inclui colunas adicionais com sequências de escape pré-formatadas para construir cada código point (tanto BMP quanto caracteres suplementares) em T-SQL (sem exigir um agrupamento " _SC" ou " _140_"), HTML (e XML), o estilo comum a muitas linguagens de aplicativos ("\uHHHH" ; usado para C++ / C# / F# / Java / JavaScript / Julia / etc), e finalmente o um pouco mais novo, outro estilo comum que lida com todos os pontos de código, não apenas BMP ("\UHHHHHHHH" ; usado para C / C++ / C# / F# / Julia / etc).

Leia tudo sobre isso aqui:

Dica 3 do SSMS: acesse/pesquise facilmente TODOS os caracteres Unicode (sim, incluindo emojis ?)