Et quelle différence choisit Collation?

Cet article a été préparé pour les étudiants du cours "MS SQL Server Developer"
Je veux partager une histoire d'un des projets précédents, qui illustre que la collation doit être choisie très soigneusement. Et sur ce qui se passe si ce paramètre est néanmoins mal choisi, et quelles options existent pour résoudre le problème.

Tout d'abord, une petite introduction sur ce qu'est le classement. Dans SQL Server, le paramètre Collation indique au serveur comment trier et comparer des lignes. Par exemple, les lignes «Apple» et «apple». Sont-ils différents ou non? Cela dépend du classement spécifié. Si le registre devient de plus en plus clair, que faire de l'exemple de «l'arbre de Noël» et de «l'arbre de Noël»? Les compter comme identiques ou différents? Tout cela est également dans Collation.

L'histoire s'est produite dans un projet dont la fonctionnalité est très similaire à DropBox ou Google Drive. Il offre la possibilité de gérer leurs dossiers et fichiers synchronisés sur différentes machines, ainsi que la possibilité pour d'autres utilisateurs d'avoir accès à ce dossier synchronisé.

Donc, l'histoire a commencé avec le fait que sur les serveurs Prod, il y avait 75 à 90% d'erreurs dans les journaux (voir la capture d'écran ci-dessous), et on ne sait pas d'où ils viennent et quelle était leur raison. L'erreur était: "ReadWrtLst n'est pas terminée". Viennent ensuite les détails de l'utilisateur et leurs dossiers.

Le code a rapidement trouvé un endroit qui a généré une erreur, mais nous ne pouvions pas comprendre pourquoi cela s'était produit et comment le reproduire. Il était clair que l'erreur était en quelque sorte liée au fait que l'utilisateur avait réussi à créez un autre dossier avec le même nom dans votre système d'exploitation.

Nous avons collecté des informations sur les utilisateurs pour lesquels cette erreur est générée. Et là, nous avons été confrontés à la première surprise: sur des millions d'utilisateurs du système, seuls 50 d'entre eux avaient cette erreur et ces 50 utilisateurs génèrent 90% des journaux d'erreurs. La situation n'ayant pu être reproduite, nous avons décidé de contacter l'un des utilisateurs et de découvrir pourquoi l'un des dossiers n'était pas synchronisé avec lui. Le dossier nous semblait le même que les autres, la seule différence était qu'il était appelé dans la langue de l'utilisateur en utilisant des hiéroglyphes. Et l'utilisateur était japonais. Soit dit en passant, parmi ces 50 utilisateurs, les Japonais étaient la majorité.

Grâce à l'un des développeurs de l'équipe, nous avons pu reproduire l'erreur. L'erreur était que le système d'exploitation considérait les noms de dossier différents et SQL Server les considérait comme identiques en raison du classement sélectionné.

Collation utilisée dans le projet:
SQL_Latin1_General_CP1_CI_AS

Une petite digression sur la façon de lire le classement. (Si vous le connaissez, n'hésitez pas à le sauter.)

Ainsi, Collation comporte plusieurs parties:

• SQL - options de tri par SQL Server (SQL au début du classement) ou Windows (alors ce serait juste Latin1_ ...);
• Latin1_General - paramètres régionaux ou langue utilisés;
• CP1 - page de codes - page de codes;
• CI - insensible à la casse - insensible à la casse;
• AS - Accent Sensitive - en tenant compte des axones ou des diacritiques, en d'autres termes, «a» n'est pas considéré comme égal à «ấ».

Ce classement était autrefois le classement par défaut lors de l'installation de SQL Server.

Quelles sont les options?

• _KS - en tenant compte des caractères japonais de hiragana et katakana, si l'option n'est pas sélectionnée, SQL Server interprétera les hiéroglyphes de hiragana et katakana de la même manière.
• _WS - compte tenu de la largeur des caractères, si le paramètre n'est pas sélectionné, alors "Texte" et "T ext" sont considérés comme les mêmes lignes.
• _VSS - tenant compte des signes de choix de l'option d'orthographe en japonais, apparu à partir de la version 2017.
• _UTF8 - vous permet de stocker des données dans UTF8.

Tous les champs de texte de la base de données ont utilisé le type NVARCHAR.
Il s'avère que, puisque le classement actuel a ignoré la différence dans l'orthographe des caractères japonais et la différence dans les largeurs de caractères, SQL Server n'a pas comparé les chaînes de la même manière que le système d'exploitation, ce qui a causé le problème, c'est-à-dire l'utilisateur pouvait créer des dossiers, ne pouvait pas les ajouter au système pour la synchronisation. La même chose se produirait plus tard lors de la comparaison des noms de fichiers.

Nous avons commencé à réfléchir à la façon de résoudre ce problème et de changer le classement.

Le classement peut être défini à plusieurs niveaux:

• Instance SQL Server
• Base de données
• Table
• Le terrain

Dans le même temps, il n'est pas recommandé d'avoir un classement différent dans la base de données, car chaque fois que vous comparez des lignes avec un classement différent, vous devrez effectuer la conversion à l'aide de COLLATE, en indiquant au serveur l'ordre de comparaison à utiliser.

Quelles sont les options dans une situation où il est clair que le classement n'est pas sélectionné correctement?

1. Modifier le classement au niveau de la base de données;
2. Modifier le classement au niveau du champ (dans notre cas, il était inutile de changer pour la table entière);
3. Ajoutez le champ Varbinary, dans lequel écrire un doublon du champ avec le nom du dossier, et utilisez-le pour la comparaison;
4. Dites aux utilisateurs qu'il existe des restrictions sur la prise en charge des caractères dans les noms de répertoire.

La première option - changer le classement au niveau de la base de données - est la plus difficile. Dans le cas de la base de données, il serait nécessaire de recréer la base de données et d'y recharger les données. Le système fonctionnant 24h / 24 et 7j / 7, cette option a été rejetée immédiatement.

La deuxième option concernant la modification du champ: la façon la plus simple de l'implémenter consiste à ajouter un champ avec le classement souhaité et à y transférer les données. Mais ensuite, il sera nécessaire de changer le code dans la base de données qui fonctionne avec ce champ, et il y avait beaucoup de code dans la base de données.

Nous avons le plus aimé la troisième option, car en théorie, elle apportait le moins de changements, car le champ principal continuerait d'exister avec le classement actuel, et nous n'aurions pas de problèmes avec sa conversion, tandis que toutes les fonctionnalités nécessaires sous la forme de la comptabilité de l'alphabet japonais ou large les personnages fonctionneraient. L'inconvénient était qu'il était nécessaire d'apporter des modifications à la partie logicielle, mais depuis cette partie serveur, cela pouvait être fait.

La quatrième option était la plus simple dans ce cas, car le nombre total d'utilisateurs était de plusieurs millions, et seulement 50 avaient un problème. Cependant, si l'application était activement utilisée au Japon, cette solution serait de peu d'utilité.

Après avoir présenté les données à la direction, il a été décidé d'informer les utilisateurs que le logiciel ne prend pas en charge un certain nombre de caractères, et lorsqu'il est utilisé au nom de fichiers et dossiers synchronisés, le logiciel peut ne pas fonctionner correctement. Il s'agit d'une solution temporaire, car avec une distribution plus poussée, le nombre d'utilisateurs confrontés à un problème similaire augmentera et il sera nécessaire de changer quelque chose en utilisant les trois premières options.

La meilleure option pour choisir le classement est basée sur les exigences de votre application. Si vous souhaitez que SQL Server compare les chaînes de la même manière que le système d'exploitation, le classement est définitivement incorrect par défaut. Malheureusement, de telles nuances sont rarement visibles au début d'un projet lors de la conception d'un système, mais, espérons-le, après avoir lu l'article, vous vous souviendrez de la situation décrite et ne monterez pas vous-même un tel râteau.

Ressources de collation utiles:
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/