E que diferença a Collation escolhe?

Este artigo foi preparado para os alunos do curso "MS SQL Server Developer"
Quero compartilhar uma história de um dos projetos anteriores, que ilustra que o agrupamento deve ser escolhido com muito cuidado. E sobre o que acontece se esse parâmetro for escolhido incorretamente e quais opções existem para resolver o problema.

Primeiro, uma pequena introdução sobre o que é Collation. No SQL Server, o parâmetro Collation informa ao servidor como classificar e comparar linhas. Por exemplo, as linhas "Apple" e "apple". Eles são diferentes ou não? Depende do agrupamento especificado. Se o registro estiver ficando cada vez menos claro, o que fazer com o exemplo de “Árvore de Natal” e “Árvore de Natal”? Contá-los como iguais ou diferentes? Isso também está no Collation.

A história aconteceu em um projeto cuja funcionalidade é muito semelhante ao DropBox ou Google Drive. Ele fornece a capacidade de gerenciar suas pastas e arquivos sincronizados em máquinas diferentes, além de outros usuários terem acesso a essa pasta sincronizada.

Portanto, a história começou com o fato de que nos servidores Prod havia 75 a 90% de erros nos logs (veja a captura de tela abaixo), e não está claro de onde eles vieram e qual foi o motivo. O erro foi: “ReadWrtLst não está completo”. Em seguida, vieram os detalhes do usuário e suas pastas.

O código encontrou rapidamente um local que gerou um erro, mas não conseguimos entender por que ocorreu e como reproduzi-lo. Ficou claro que o erro estava de alguma forma relacionado ao fato de que o usuário conseguiu crie outra pasta com o mesmo nome no seu sistema operacional.

Reunimos informações sobre usuários para os quais esse erro foi emitido. E aqui fomos confrontados com a primeira surpresa: dos milhões de usuários do sistema, apenas 50 deles tiveram esse erro e esses 50 usuários geram 90% dos logs de erros. Como a situação não pôde ser reproduzida, decidimos entrar em contato com um dos usuários e descobrir por que uma das pastas não estava sincronizada. A pasta tinha a mesma aparência que as outras, a única diferença era que ela era chamada no idioma do usuário usando hieróglifos. E o usuário era japonês. A propósito, entre esses 50 usuários, os japoneses eram a maioria.

Graças a um dos desenvolvedores da equipe, conseguimos reproduzir o erro. O erro foi que o sistema operacional considerou os nomes das pastas diferentes e o SQL Server os considerou o mesmo devido ao agrupamento selecionado.

Agrupamento usado no projeto:
SQL_Latin1_General_CP1_CI_AS

Uma pequena digressão sobre como ler Agrupamento. (Se você estiver familiarizado com isso, fique à vontade para ignorá-lo.)

Portanto, há várias partes no agrupamento:

• SQL - opções de classificação pelo SQL Server (SQL no início do agrupamento) ou Windows (então seria apenas Latin1_ ...);
• Latin1_General - local ou idioma usado;
• CP1 - página de código - página de código;
• CI - Insensível a maiúsculas e minúsculas;
• AS - Accent Sensitive - levando em consideração axônios ou diacríticos, em outras palavras, 'a' não é considerado igual a 'ấ'.

Esse agrupamento já foi o agrupamento padrão ao instalar o SQL Server.

Que opções existem?

• _KS - levando em consideração os caracteres japoneses de hiragana e katakana, se a opção não estiver selecionada, o SQL Server interpretará os hieróglifos de hiragana e katakana da mesma forma.
• _WS - levando em consideração a largura dos caracteres, se o parâmetro não for selecionado, “Text” e “T ext” serão considerados as mesmas linhas.
• _VSS - levando em consideração os sinais de escolha da opção de ortografia em japonês, apareceu a partir da versão 2017.
• _UTF8 - permite armazenar dados em UTF8.

Todos os campos de texto no banco de dados usado digite NVARCHAR.
Acontece que, como o Collation atual ignorou a diferença na ortografia dos caracteres japoneses e a diferença na largura dos caracteres, o SQL Server não comparou as seqüências da mesma maneira que o sistema operacional, o que causou o problema, ou seja, o usuário pode criar pastas, não pôde adicioná-las ao sistema para sincronização. O mesmo aconteceria mais tarde ao comparar nomes de arquivos.

Começamos a pensar em como resolver esse problema e alterar o agrupamento.

O agrupamento pode ser definido em vários níveis:

• Instância do SQL Server
• Banco de Dados
• Quadro
• O campo

Ao mesmo tempo, não é recomendável ter um agrupamento diferente dentro do banco de dados, pois sempre que comparar linhas com agrupamento diferente, será necessário fazer a conversão usando COLLATE, indicando ao servidor que ordem de comparação ele deve usar.

Que opções existem em uma situação em que fica claro que o agrupamento não está selecionado corretamente?

1. Alterar agrupamento no nível do banco de dados;
2. Alterar agrupamento no nível do campo (no nosso caso, não havia sentido em mudar para a tabela inteira);
3. Adicione o campo Varbinary, no qual escrever uma duplicata do campo com o nome da pasta e use-o para comparação;
4. Informe aos usuários que existem restrições no suporte a caracteres nos nomes de diretório.

A primeira opção - alterar o agrupamento no nível do banco de dados - é a mais difícil. No caso do banco de dados, seria necessário recriar o banco de dados e recarregar os dados lá. Como o sistema funcionava 24/7, essa opção foi rejeitada imediatamente.

A segunda opção sobre como alterar o campo: a opção mais fácil de implementá-lo é adicionar um campo com o agrupamento desejado e transferir os dados para lá. Porém, será necessário alterar o código no banco de dados que funciona com esse campo, e havia muito código no banco de dados.

Gostamos mais da terceira opção, uma vez que, em teoria, ela fez menos alterações, já que o campo principal continuaria a existir com o agrupamento atual e não teríamos problemas com sua conversão, enquanto todas as funcionalidades necessárias na forma de contabilizar o alfabeto japonês ou amplo caracteres funcionariam. A desvantagem foi que era necessário fazer alterações na parte do software, mas, como essa parte do servidor, isso poderia ser feito.

A quarta opção foi a mais simples nesse caso, porque o número total de usuários era de vários milhões e apenas 50 tinham um problema.No entanto, se o aplicativo fosse usado ativamente no Japão, essa solução seria pouco útil.

Após apresentar os dados ao gerenciamento, foi decidido notificar os usuários que o software não suporta vários caracteres e, quando usado em nome de arquivos e pastas sincronizados, o software pode não funcionar corretamente. Esta é uma solução temporária, porque, com a distribuição posterior, o número de usuários que enfrentam um problema semelhante aumentará e será necessário alterar alguma coisa usando as três primeiras opções.

A melhor opção para escolher Agrupamento é baseada nos requisitos de seu aplicativo. Se você deseja que o SQL Server compare cadeias de caracteres da mesma maneira que o sistema operacional, o agrupamento está definitivamente incorreto por padrão. Infelizmente, essas nuances raramente são visíveis no início de um projeto ao projetar um sistema, mas, esperançosamente, depois de ler o artigo, você irá se lembrar da situação descrita e não se comprometer.

Recursos úteis de agrupamento:
https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask/
https://www.virtual-dba.com/sql-server-collation/