Este artículo fue preparado para estudiantes del curso "MS SQL Server Developer"
Quiero compartir una historia de uno de los proyectos anteriores, que ilustra que la recopilación debe elegirse con mucho cuidado. Y sobre lo que sucede si este parámetro se elige de forma incorrecta y qué opciones existen para resolver el problema.
Primero, una pequeña introducción sobre qué es la colación. En SQL Server, el parámetro Collation le dice al servidor cómo ordenar y comparar filas. Por ejemplo, las líneas "Apple" y "apple". ¿Son diferentes o no? Depende de la clasificación especificada. Si el registro se está volviendo menos o menos claro, ¿qué hacer con el ejemplo de "árbol de Navidad" y "árbol de Navidad"? ¿Los cuenta igual o diferente? Todo esto también está en colación.
La historia sucedió en un proyecto cuya funcionalidad es muy similar a DropBox o Google Drive. Proporciona la capacidad de administrar sus carpetas y archivos sincronizados en diferentes máquinas, así como la posibilidad de que otros usuarios tengan acceso a esta carpeta sincronizada.
Entonces, la historia comenzó con el hecho de que en los servidores Prod había 75-90% de errores en los registros (vea la captura de pantalla a continuación), y no está claro de dónde vinieron y cuál fue su razón. El error fue: "ReadWrtLst no está completo". Luego vinieron los detalles del usuario y sus carpetas.
El código encontró rápidamente un lugar que generó un error, pero no pudimos entender por qué ocurrió y cómo reproducirlo. Solo estaba claro que el error estaba relacionado de alguna manera con el hecho de que el usuario de alguna manera logró cree otra carpeta con el mismo nombre en su sistema operativo.
Hemos recopilado información sobre los usuarios para quienes se emite este error. Y aquí nos enfrentamos con la primera sorpresa: de millones de usuarios del sistema, solo 50 de ellos tenían este error, y estos 50 usuarios generan el 90% de los registros de errores. Como la situación no se pudo reproducir, decidimos contactar a uno de los usuarios y averiguar por qué una de las carpetas no estaba sincronizada con ella. La carpeta nos parecía la misma que a las demás, la única diferencia era que se llamaba en el idioma del usuario utilizando jeroglíficos. Y el usuario era japonés. Por cierto, entre estos 50 usuarios, los japoneses eran la mayoría.
Gracias a uno de los desarrolladores del equipo, pudimos reproducir el error. El error fue que el sistema operativo consideraba los nombres de las carpetas diferentes y SQL Server los consideraba iguales debido a la Clasificación seleccionada.
Colación utilizada en el proyecto:
SQL_Latin1_General_CP1_CI_AS
Una pequeña digresión sobre cómo leer Colación. (Si está familiarizado con él, no dude en saltearlo).
Entonces, hay varias partes en la Clasificación:
- SQL: opciones de ordenación por SQL Server (SQL al comienzo de la Clasificación) o Windows (entonces sería solo Latin1_ ...);
- Latin1_General: configuración regional o idioma utilizado;
- CP1 - página de códigos - página de códigos;
- CI - Insensible a mayúsculas y minúsculas - insensible a mayúsculas y minúsculas;
- AS - Acento sensible: teniendo en cuenta los axones o los signos diacríticos, en otras palabras, 'a' no se considera igual a 'ấ'.
Esta intercalación fue una vez la intercalación predeterminada al instalar SQL Server.
¿Qué opciones hay?
- _KS: teniendo en cuenta los caracteres japoneses de hiragana y katakana, si la opción no está seleccionada, SQL Server interpretará los jeroglíficos de hiragana y katakana de la misma manera.
- _WS: teniendo en cuenta el ancho de los caracteres, si el parámetro no está seleccionado, entonces "Texto" y "T ext" se consideran las mismas líneas.
- _VSS: teniendo en cuenta los signos de elegir la opción de ortografía en japonés, apareció a partir de la versión 2017.
- _UTF8: le permite almacenar datos en UTF8.
Todos los campos de texto en la base de datos utilizan el tipo NVARCHAR.
Resulta que, dado que la Clasificación actual ignoró la diferencia en la ortografía de los caracteres japoneses y la diferencia en el ancho de los caracteres, SQL Server no comparó las cadenas de la misma manera que el sistema operativo, lo que causó el problema, es decir. el usuario podría crear carpetas, no podría agregarlas al sistema para sincronización. Lo mismo sucedería más tarde al comparar nombres de archivos.
Comenzamos a pensar en cómo resolver este problema y cambiar la Clasificación.
La clasificación se puede establecer en varios niveles:
- Instancia de SQL Server
- Base de datos
- Mesa
- El campo
Al mismo tiempo, no se recomienda tener una clasificación diferente dentro de la base de datos, ya que cada vez que compare filas con una clasificación diferente, deberá realizar la conversión usando COLLATE, que indica al servidor qué orden de comparación debe usar.
¿Qué opciones hay en una situación en la que está claro que la Clasificación no está seleccionada correctamente?
- Cambiar colación a nivel de base de datos;
- Cambiar colación a nivel de campo (en nuestro caso, no tenía sentido cambiar para toda la tabla);
- Agregue el campo Varbinary, en el cual escribir un duplicado del campo con el nombre de la carpeta, y úselo para comparar;
- Indique a los usuarios que existen restricciones en el soporte de caracteres en los nombres de directorio.
La primera opción, cambiar la Clasificación en el nivel de la base de datos, es la más difícil. En el caso de la base de datos, sería necesario recrear la base de datos y volver a cargar los datos allí. Como el sistema funcionaba 24/7, esta opción fue rechazada de inmediato.
La segunda opción para cambiar el campo: la opción más fácil de implementar es agregar un campo con la Clasificación deseada y transferir los datos allí. Pero entonces será necesario cambiar el código en la base de datos que funciona con este campo, y había mucho código en la base de datos.
La tercera opción nos gustó más, ya que en teoría hizo los menores cambios, ya que el campo principal continuaría existiendo con la Clasificación actual, y no tendríamos problemas con su conversión, mientras que toda la funcionalidad necesaria en forma de contabilidad para el alfabeto japonés o amplia Los personajes funcionarían. La desventaja era que era necesario realizar cambios en la parte del software, pero desde esta parte del servidor, esto se podía hacer.
La cuarta opción era la más simple en este caso, porque el número total de usuarios era de varios millones, y solo 50 tenían un problema. Sin embargo, si la aplicación se usara activamente en Japón, esta solución sería de poca utilidad.
Después de presentar los datos a la administración, se decidió notificar a los usuarios que el software no admite una serie de caracteres, y cuando se usa en nombre de archivos y carpetas sincronizados, el software puede no funcionar correctamente. Esta es una solución temporal, porque con una mayor distribución, aumentará el número de usuarios que enfrentan un problema similar, y será necesario cambiar algo usando las tres primeras opciones.
La mejor opción para elegir Colación se basa en los requisitos de su aplicación. Si desea que SQL Server compare cadenas de la misma manera que el sistema operativo, entonces la Clasificación es definitivamente incorrecta de forma predeterminada. Desafortunadamente, tales matices rara vez son visibles al comienzo de un proyecto cuando se diseña un sistema, pero, con suerte, después de leer el artículo, recordará la situación descrita y no se subirá a ese rastrillo.
Recursos útiles de recopilación:
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/