Início / dba / Perguntas / 249715
Accepted
cocogorilla
Asked: 2019-09-27 10:35:31 +0800 CST

Como funciona o agrupamento que não diferencia maiúsculas de minúsculas?

  • 772

O tipo de agrupamento padrão no SQL Server permite a indexação em cadeias de caracteres que não diferenciam maiúsculas de minúsculas, mas as maiúsculas e minúsculas dos dados são mantidas. Como isso realmente funciona? Estou procurando as porcas e parafusos reais, bits e bytes, ou um bom recurso que explica isso em detalhes.

create table casetest (fruitnames nvarchar(50) not null);
create unique index IX_fruitnames on casetest(fruitnames);

insert into casetest values ('apples');
insert into casetest values ('Pears');
-- this insert fails
insert into casetest values ('pears');

-- this yields 'Pears' as a result
select * from casetest (forceseek) where fruitnames = 'PEARS'

update casetest set fruitnames = 'pears' where fruitnames = 'pEArs'

-- this yields 'pears' as a result
select * from casetest (forceseek) where fruitnames = 'PEARS'

Perguntas sobre agrupamentos do SQL Server que você era muito tímido para perguntar por Robert Sheldon aborda como usar o agrupamento. Ele não cobre como funciona o agrupamento. Estou interessado em como um índice pode ser criado/consultado com eficiência, sem se importar com o caso, enquanto armazena simultaneamente os dados do caso.

sql-server collation

2 respostas

  1. Best Answer

    indexação em strings que não diferenciam maiúsculas de minúsculas, mas as maiúsculas e minúsculas dos dados são mantidas. Como isso realmente funciona?

    Na verdade, esse não é um comportamento específico do SQL Server, é apenas como essas coisas funcionam em geral.

    Então, os dados são os dados. Se você estiver falando sobre um índice especificamente, os dados precisam ser armazenados como estão, caso contrário, seria necessário uma consulta na tabela principal a cada vez para obter o valor real e não haveria possibilidade de um índice de cobertura (no menos não para tipos de string).

    Os dados, na tabela/índice clusterizado ou no índice não clusterizado, não contêm nenhuma informação de agrupamento/classificação. São simplesmente dados. O agrupamento (regras e sensibilidades de localidade/cultura) são apenas metadados anexados à coluna e usados ​​quando uma operação de classificação é chamada (a menos que substituída por umCOLLATEcláusula), que incluiria a criação/reconstrução de um índice. As regras definidas por uma ordenação não binária são usadas para gerar chaves de classificação, que são representações binárias da string (as chaves de classificação são desnecessárias em ordenações binárias). Essas representações binárias incorporam todas as regras de localidade/cultura e sensibilidades selecionadas. As chaves de classificação são usadas para colocar os registros na ordem correta, mas não são armazenadas no índice ou na tabela. Eles não são armazenados (pelo menos eu não vi esses valores no índice e me disseram que eles não são armazenados) porque:

    1. Eles não são realmente necessários para classificação , pois estariam apenas na mesma ordem que as linhas na tabela ou índice de qualquer maneira. Mas, a ordem física do índice é apenas classificação, não comparação.
    2. Embora armazená-los possa tornar as comparações mais rápidas, também tornaria o índice maior, pois o tamanho mínimo para um único caractere é de 5 bytes, e isso é apenas "sobrecarga" (da estrutura de chave de classificação). A maioria dos caracteres tem 2 bytes cada, mais 1 byte se houver acento, mais 1 byte se for maiúsculo. Por exemplo, "e" é uma chave de 7 bytes, "E" e "é" são ambas de 8 bytes e "É" é uma chave de 9 bytes. Portanto, não vale a pena armazená-los no final.

    Existem dois tipos de agrupamentos: SQL Server e Windows.

    servidor SQL

    Os agrupamentos do SQL Server (aqueles com nomes que começam com SQL_) são a maneira mais antiga e anterior ao SQL Server 2000 de classificar/comparar (embora aindaSQL_Latin1_General_CP1_CI_AS seja o padrão de instalação em sistemas operacionais em inglês dos EUA, infelizmente). Nesse modelo mais antigo, simplista e não Unicode, cada combinação de localidade, página de código e as várias sensibilidades recebem um mapeamento estático de cada um dos caracteres dessa página de código. Cada caractere recebe um valor (ou seja, peso de classificação) para denotar como ele se equipara aos outros. As comparações neste modelo parecem fazer uma operação de duas passagens:

    1. Primeiro, ele remove todos os acentos (de modo que "  ü  " se torne "  u  "), expande caracteres como "  Æ  " em "  A  " e "  E  ", depois faz uma classificação inicial para que as palavras fiquem em uma ordem natural (como você faria espere encontrá-los em um dicionário).
    2. Em seguida, ele vai caractere por caractere para determinar a igualdade com base nesses valores subjacentes por cada caractere. Esta segunda parte é o que mustaccio está descrevendo em sua resposta .

    As únicas sensibilidades que podem ser ajustadas nestes agrupamentos são: "case" e "accent" ("width", "kana type" e "variation selector" não estão disponíveis). Além disso, nenhum desses agrupamentos oferece suporte a caracteres suplementares (o que faz sentido, pois são específicos do Unicode e esses agrupamentos se aplicam apenas a dados não Unicode).

    Essa abordagem se aplica apenas a dados não Unicode VARCHAR. Cada combinação exclusiva de localidade, página de código, distinção entre maiúsculas e minúsculas e distinção entre acentos tem um "ID de classificação" específico, que você pode ver no exemplo a seguir:

    SELECT COLLATIONPROPERTY(N'SQL_Latin1_General_CP1_CI_AS', 'SortID'), -- 52
       COLLATIONPROPERTY(N'SQL_Latin1_General_CP1_CS_AS', 'SortID'), -- 51
       COLLATIONPROPERTY(N'Latin1_General_100_CI_AS',     'SortID'); --  0

    A única diferença entre os dois primeiros agrupamentos é a diferenciação entre maiúsculas e minúsculas. A terceira ordenação é uma ordenação do Windows e, portanto, não possui uma tabela de mapeamento estática.

    Além disso, esses agrupamentos devem classificar e comparar mais rapidamente do que os agrupamentos do Windows devido a serem pesquisas simples de caracteres para classificar o peso. No entanto, esses agrupamentos também são muito menos funcionais e geralmente devem ser evitados, se possível.

    janelas

    Os agrupamentos do Windows (aqueles com nomes que não começam com SQL_) são a maneira mais recente (começando no SQL Server 2000) de classificar/comparar. Nesse modelo Unicode mais novo e complexo, cada combinação de localidade, página de código e as várias sensibilidades não recebem um mapeamento estático. Por um lado, não há páginas de código neste modelo. Esse modelo atribui um valor de classificação padrão a cada caractere e, em seguida, cada localidade/cultura pode reatribuir valores de classificação a qualquer número de caracteres. Isso permite que várias culturas usem os mesmos personagens de maneiras diferentes. Isso tem o efeito de permitir que vários idiomas sejam classificados naturalmente usando o mesmo agrupamento se eles não usarem os mesmos caracteres (e se um deles não precisar reatribuir nenhum valor e puder simplesmente usar os padrões).

    Os valores de classificação neste modelo não são valores únicos. Eles são uma matriz de valores que atribuem pesos relativos à letra base, quaisquer diacríticos (ou seja, acentos), maiúsculas, etc. portanto, insensível). Se o agrupamento for sensível ao acento, a parte "diacrítica" da matriz será usada, caso contrário, será ignorada (portanto, insensível).

    As comparações neste modelo são uma operação multi-pass:

    1. Primeiro, a string é normalizada para que várias maneiras de representar o mesmo caractere sejam iguais. Por exemplo, " ü " pode ser um único caractere/ponto de código (U+00FC). Você também pode combinar um " u " não acentuado (U+0075) com um trema de combinação " ̈ " (U+0308) para obter: " ü ", que não apenas parece o mesmo quando renderizado (a menos que haja um problema com sua fonte), mas também é considerado o mesmo que a versão de caractere único (U+00FC), a menos que use um agrupamento binário (que compara bytes em vez de caracteres). A normalização divide o caractere único em várias partes, o que inclui expansões para caracteres como "  Æ  " (conforme observado acima para agrupamentos do SQL Server).
    2. A operação de comparação neste modelo vai caractere por caractere por cada sensibilidade . As chaves de classificação para as strings são determinadas aplicando os elementos apropriados de cada matriz de agrupamento de caracteres de valores com base em quais sensibilidades são "sensíveis". Os valores da chave de classificação são organizados por todas as sensibilidades primárias de cada caractere (o caractere base), seguidas por todas as sensibilidades secundárias (peso diacrítico), seguidas pelo peso de maiúsculas e minúsculas de cada caractere e assim por diante.
    3. A classificação é realizada com base nas chaves de classificação calculadas. Com cada sensibilidade agrupada, você pode obter uma ordem de classificação diferente da que faria com um agrupamento equivalente do SQL Server ao comparar strings de vários caracteres, e os acentos estão envolvidos, e o agrupamento é sensível ao acento (e ainda mais se o agrupamento for também diferencia maiúsculas de minúsculas).

    Para obter mais detalhes sobre essa classificação, eventualmente publicarei uma postagem que mostra os valores da chave de classificação, como eles são calculados, as diferenças entre os agrupamentos do SQL Server e do Windows etc. Mas, por enquanto, veja minha resposta para: Accent Sensitive Sort ( observe que a outra resposta a essa pergunta é uma boa explicação do algoritmo oficial do Unicode, mas o SQL Server usa um algoritmo personalizado, embora semelhante, e até uma tabela de peso personalizada).

    Todas as sensibilidades podem ser ajustadas nestes agrupamentos: "case", "accent", "width", "kana type" e "seletor de variação" (a partir do SQL Server 2017 e apenas para os agrupamentos japoneses). Além disso, alguns desses agrupamentos (quando usados ​​com dados Unicode) dão suporte a caracteres suplementares (a partir do SQL Server 2012). Essa abordagem se aplica a ambos NVARCHAR e VARCHAR a dados (mesmo a dados não Unicode). Aplica-se a VARCHARdados não Unicode convertendo primeiro o valor para Unicode internamente e, em seguida, aplicando as regras de classificação/comparação.

    Observe:

    1. Não há agrupamento padrão universal para SQL Server. Há um padrão de instalação que difere com base na configuração de localidade/idioma atual do sistema operacional no momento da instalação (que infelizmente é SQL_Latin1_General_CP1_CI_ASpara sistemas em inglês dos EUA, portanto , vote nesta sugestão ). Isso pode ser alterado durante a instalação. Esse agrupamento em nível de instância define o agrupamento para o [model]banco de dados que é o modelo usado ao criar novos bancos de dados, mas o agrupamento pode ser alterado durante a execução CREATE DATABASEespecificando a COLLATEcláusula. Esse agrupamento em nível de banco de dados é usado para literais de variável e string, bem como o padrão para colunas novas (e alteradas!) quando a COLLATEcláusula não é especificada (que é o caso do código de exemplo na pergunta).
    2. Para obter mais informações sobre agrupamentos / codificações / Unicode, visite: Informações de agrupamentos
    • 26

  2. Normalmente, isso é implementado usando tabelas de agrupamento que atribuem uma determinada pontuação a cada caractere. A rotina de classificação tem um comparador que usa uma tabela apropriada, seja padrão ou especificada explicitamente, para comparar strings, caractere por caractere, usando suas pontuações de agrupamento. Se, por exemplo, uma tabela de agrupamento específica atribuir uma pontuação de 1 a "a" e 201 a "A", e uma pontuação mais baixa nessa implementação específica significar precedência mais alta, "a" será classificado antes de "A". Outra tabela pode atribuir pontuações inversas: 201 para "a" e 1 para "A", e a ordem de classificação será posteriormente reversa. Ainda outra tabela pode atribuir pontuações iguais a "a", "A", "Á" e "Å", o que levaria a uma comparação e classificação sem distinção entre maiúsculas e minúsculas e acentos.

    Da mesma forma, esse comparador baseado em tabela de ordenação é usado ao comparar uma chave de índice com o valor fornecido no predicado.

    • 5

relate perguntas