Last Updated on 22 agosto, 2017 by Pablo Ariel Di Loreto
Los Administradores de IT recurrentemente debemos trabajar con bases de datos SQL Server y MySQL Server, ya sea para instalar algún software (como System Center, SharePoint, etc) o por requerimientos de desarrolladores que deben montar allí base de datos para sus aplicaciones.
Un componente habitual en la instalación y configuración de los motores de base de datos son las Intercalaciones (o conocidas como «Collations» en inglés). La misma es de vital importancia, dado que define aspectos de búsquedas, comparación y ordenamiento que pueden impactar, directamente, en el éxito o fracaso de una implementación.
En esta publicación vamos a realizar una recorrida conceptual sobre las Intercalaciones (Collations), poniendo foco en SQL Server pero también comparándola (por lo menos teóricamente) con otros motores como MySQL Server.
Tabla de Contenidos
- 1 Introducción
- 2 Desarrollo
- 3 Conclusiones
- 4 Referencias y Links
- 5 Publicaciones Relacionadas
- 6 Acerca del Autor
Introducción
Objetivo y Alcance
Esta publicación tiene como objetivo realizar una introducción teórica sobre Intercalaciones (Collations) en motores de nase de datos. La misma está principalmente orientada a Administradores IT (IT Pros) y, en segunda instancia, para Desarrolladores de nivel inicial.
El alcance de esta publicación es realizar una introducción teórica básica sobre Intercalaciones (Collations) en motores de base de datos, realizar una comparativa teórica en alto nivel entre SQL Server y MySQL Server para que, por último, podamos introducirnos con un poco más de profundidad en características de las Collations en SQL Server como motor de base de datos.
Audiencia
Este documento está dirigido a Consultores, Profesionales IT y personas que desarrollan tareas de Consultoría, Administración y Soporte o que simplemente están interesados en leer e investigar sobre la tecnología alcanzada por esta publicación.
Comentarios y Corrección de Errores
Hemos realizado nuestro mejor esfuerzo para no cometer errores, pero al fin y al cabo somos seres humanos. Si deseás reportar algún error o darnos feedback de qué te pareció esta publicación, por favor no dejes de comunicarte con nosotros a través de correo electrónico a la siguiente dirección: info@tectimes.net.
Desarrollo
¿Qué es una Intercalación (Collation)?
Vamos a comenzar a introducirnos en el tema de Intercalaciones en motores de base de datos, arrancando por su definición teórica general, comparando este concepto en dos motores de base de datos (SQL Server y MySQL Server) y, por último, identificando su importancia.
Definición Conceptual
Desde lo conceptual, la Intercalación (Collation en su término en inglés) hace referencia al patrón de bits que es utilizado dentro de una base de datos para representar y almacenar cada uno de los caracteres en campos de texto, y en consecuencia también se refiere a las reglas utilizadas para ordenar y comparar estos caracteres. En sí mismo la Intercalación asocia un valor único a cada letra dependiendo del idioma seleccionado.
La Intercalación (Collation) es utilizado como término en bases de datos, por ejemplo SQL Server y MySQL Server, y se refieren básicamente a lo mismo. En ambos motores los datos de texto deben tener un patrón de bits para representarse y almacenarse.
Ejemplos de Collations en SQL Server son los siguientes:
- Japanese_Bushu_Kakusu_100_CS_AS_KS_WS.
- Latin1_General_CS_AI.
- SQL_Latin1_General_CP1_CI_AS.
Qué significa cada uno de estos fragmentos los veremos más adelante. Por el momento es sumamente importante entender conceptualmente que es una Collation.
Juego de Caracteres (Character Set) e Intercalación (Collation)
Es muy común que, dependiendo el origen de la bibliografía que leamos, encontremos diferenciados los términos de Charset ó Character Set (Juego de Caracteres) y Collation (Intercalación).
En bibliografía de MySQL Server normalmente encontraremos definido al Charset (Juego de Caracteres) a cómo se guarda internamente el dato y Collation (Intercalación) es la manera de decirle al motor cómo debe comparar el texto y/o ordenarlo. Cuando se selecciona un Charset, MySQL le asignará de forma automática un Collation asociado a este Charset. Si hay varias Collations asociadas, elegirá la predeterminada. Vayamos a un ejemplo:
- Si se selecciona solo el Character Set «Latin1» (que es lo mismo que decirle al motor «–character-set-server=latin1») pero no se selecciona una Collation, el motor, el motor MySQL trabajará con la collation Latin1_swedish_ci porque ésta es la intercalación predeterminada para Latin1.
Si estudiamos bibliografía de SQL Server, las Intercalaciones (Collations) que se utilizan con tipos de datos de caracteres (como pueden ser «char» y «varchar») dictan la página de códigos y los caracteres correspondientes que se pueden representar para ese tipo de datos (es decir el Juego de Caracteres). Esto significa que al seleccionar una Intercalación (Collection) estamos seleccionando un Juego de Caracteres (Charset).
¿Por qué es importante la Intercalación (Collation)?
Sin lugar a dudas, la Intercalación es sumamente importante a la hora de ordenar y comparar datos de texto en una base de datos. Dependiendo de cuál elijamos, puede resultar que la palabra Chapa aparezca ordenada antes que la palabra Colina, o viceversa. ¿Por qué? Porque en Español la «Ch» es una letra.
Además de este ejemplo muy simple, la collation nos puede indicar si la letra «A» es igual a la letra «a», o diferenciarlas. Todo depende si la Intercalación elegida diferencia mayúsculas de minúsculas. Así también podemos encontrar diferencias entre palabras acentuadas.
Para Administradores de IT (perfil «ITPro») que desarrolla actividades de instalación y configuración de motores de base de datos, y puntualmente SQL Server, la correcta elección de la Collation y su compatibilidad con el software que estemos implementando es vital para que todas las funcionalidades de dicho software funcionen como esperamos.
Conjuntos de Intercalaciones (Collations) en SQL SErver
Vamos a conocer los llamados «conjuntos de intercalaciones» disponibles en SQL Server, los cuales fundamentalmente son tres.
Intercalaciones (Collations) de Windows
Definen reglas para almacenar los datos de caracteres que se basan en una configuración regional del sistema operativo asociado.
Para listar las intercalaciones de Windows que admite una instancia de SQL Server se puede ejecutar la siguiente consulta:
SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE 'SQL%';
Intercalaciones (Collations) Binarias
Ordenan los datos según la secuencia de valores codificados definidos por la configuración regional y el tipo de datos. Distinguen entre mayúsculas y minúsculas. Una intercalación binaria de SQL Server define la configuración regional y la página de códigos ANSI que se van a usar. Esto exige un criterio de ordenación binario. Dado que son relativamente simples, las intercalaciones binarias ayudan a mejorar el rendimiento de la aplicación.
Existen dos tipos de intercalaciones binarias en SQL Server; las intercalaciones más antiguas BIN y las más recientes BIN2. En una intercalación BIN2 todos los caracteres se ordenan de acuerdo a sus puntos de código. En una intercalación BIN solamente el primer carácter se ordena de acuerdo al punto de código y el resto de ellos se ordenan según sus valores de byte.
Para listar las intercalaciones Binarias que admite una instancia de SQL Server se puede ejecutar la siguiente consulta:
SELECT * FROM sys.fn_helpcollations() WHERE name LIKE '%BIN%';
Intercalaciones (Collations) de SQL Server
Son compatibles en cuanto a criterio de ordenación con las versiones anteriores de SQL Server.
Para listar las intercalaciones de SQL que admite una instancia de SQL Server se puede ejecutar la siguiente consulta:
SELECT * FROM sys.fn_helpcollations() WHERE name LIKE 'SQL%';
Tipo de Intercalaciones (Collations) en SQL Server
Habiendo identificado en forma introductoria los conjuntos de intercalaciones, ahora vamos a conocer los tipos de intercalaciones existentes para un motor de base de datos SQL Server. Los mismos identifican niveles y se configuran en diversos «momentos»: al instalar el motor, al crear una base de datos, o ya habiendo definido tablas. ¡Avancemos!
Intercalaciones (Collations) a nivel Servidor
Las mismas se establecen durante la instalación del motor SQL Server y se convierten en la intercalación predeterminada de las bases de datos de sistema y de usuario.
Una vez que se seleccionó, no se pueden cambiar excepto exportando todos los objetos y datos de la base de datos, recompilando la base de datos master e importando todos los objetos y datos nuevamente.
Intercalaciones (Collations) a nivel Base de Datos
Cuando se crea una base de datos, se puede especificar una intercalación para la misma. En el caso de no especificar ninguna, se utilizará la del servidor.
No puede cambiar la intercalación de base de datos del sistema excepto cambiando la intercalación del servidor.
La intercalación de base de datos se usa para todos los metadatos de la base de datos, y es la predeterminada para todas las columnas de cadena, los objetos temporales, los nombres de variable, y cualquier otra cadena usada en la base de datos. Cuando se cambia la intercalación de una base de datos de usuario, pueden producirse conflictos de intercalación cuando las consultas en la base de datos tienen acceso a tablas temporales.
Intercalaciones (Collations) a nivel Columna
Cuando cree o altere una tabla, puede especificar intercalaciones para cada columna de cadena de caracteres. Si no se especifica una intercalación, a la columna se le asigna la intercalación predeterminada de la base de datos.
Intercalaciones (Collations) a nivel Expresión
Las intercalaciones de nivel de expresión se establecen cuando se ejecuta una instrucción y afectan al modo en que se devuelve un conjunto de resultados. Esto permite que los resultados de la ordenación ORDER BY sean específicos de la configuración regional.
Parámetros de Intercalaciones (Collations)
Las Intercalaciones en SQL Server tienen opciones asociadas, las cuales indican al fin y al cabo cómo se comportará un Character Set en relación a identificación de mayúsculas y minúsculas, acentos y otros aspectos importantes.
Las opciones asociadas a una Intercalación son las siguientes:
- Case-sensitive (_CL): Distingue entre mayúsculas y minúsculas.
- Case-insensitive (_CI): No distingue entre mayúscula y minúscula en forma explícita.
- Accent-sensitive (_AS): Distingue entre caracteres acentuados y no acentuados. N
- Accent-insensitive (_AI): No distingue entre caracteres acentuados y no acentuados en forma explícita.
- Kana-sensitive (_KS): Distingue entre dos tipos de caracteres kana japoneses: Hiragana y Katakana. No tiene omisión explícita.
- Width-sensitive (_WS): Distingue entre caracteres de ancho total y ancho medio. No tiene omisión explícita.
Por ejemplo la Intercalación Latin1_General_CS_AI es una intercalación con distinción de mayúsculas y minúsculas y sin distinción de acentos (especificada en forma explícita). En este caso las versiones acentuadas y no acentuadas de una palabra serán consideradas por SQL Server como caracteres idénticas para la ordenación.
Ejemplos Prácticos utilizando Queries
Ver Intercalación (Collation) de una Instancia de SQL Server
Se podrá utilizar el siguiente script de SQL:
SELECT CONVERT (varchar, SERVERPROPERTY('collation'));
El resultado será similar al siguiente:
Ilustración 1 – Resultado del script SELECT CONVERT (varchar, SERVERPROPERTY(‘collation’)); para conocer la Collation de una instancia de SQL Server.
Alternativamente se podrá utilizar el siguiente procedimiento almacenado (stored procedure):
EXECUTE sp_helpsort;
El resultado será el siguiente:
Ilustración 2 – Identificación de Collation utilizando el SP EXECUTE sp_helpsort;
Ver todas las Intercalaciones (Collations) soportadas para una Instancia de SQL Server
Se podrá utilizar el siguiente script de SQL:
SELECT name, description FROM sys.fn_helpcollations();
El resultado será similar al siguiente:
Ilustración 3 – Todas las Colletions soportadas por SQL Server (Instancia) utilizando el script SELECT name, description FROM sys.fn_helpcollations();
Ver Intercalación (Collation) de las Bases de Datos de SQL Server
Se podrá utilizar el siguiente script de SQL para conocer la collation de todas las bases de datos de la instancia:
SELECT name, collation_name FROM sys.databases;
La salida será similar a la siguiente:
Ilustración 4 – Listado de Collations de todas las bases de datos de la Instancia de SQL Server utilizando el script SELECT name, collation_name FROM sys.databases;
Conclusiones
Las Intercalaciones (Collations) en SQL Server (y en cualquier motor de base de datos, al fin y al cabo, como por ejemplo MySQL) representan una configuración muy importante que determinará cómo éste se comportará en términos de ordenamiento y comparación.
Para los desarrolladores, la elección correcta de una Collation para la instancia del motor, o la propia base de datos, es de suma importancia dado que el software que desarrollen mostrará y hará uso de esos datos almacenados.
Para los IT Pros, muchas veces las collations pasan más «desapercibidas», no obstante en software como System Center y/o SharePoint representan un aspecto de configuración esencial, que determinará si diversas funcionalidades y/o informes funcionan o no.
Esta publicación ha intentado tratar un tema a veces desconocido para Administradores IT, o que (basado en la experiencia de seguidas charlas con otros profesionales) tiene un «misterio» especial. Esperamos que el mismo sea de ayuda para fortalecer los conocimientos relacionados a este tema, y por supuesto dejamos los comentarios abiertos para cualquier duda / comentario adicional.
¡Nos leemos pronto!
Referencias y Links
- Collation and Unicode Support: https://msdn.microsoft.com/en-us/library/ms143726(v=sql.120).aspx
- Windows Collation Name (Transact-SQL): https://msdn.microsoft.com/en-us/library/ms188046.aspx
- SQL Server Collation Name (Transact-SQL): https://msdn.microsoft.com/en-us/library/ms180175.aspx
- View Collation Information: https://msdn.microsoft.com/en-us/library/hh230914.aspx
- Set or Change the Server Collation: https://msdn.microsoft.com/en-us/library/ms179254(v=sql.120).aspx
Acerca del Autor
Mis lazos con la informática comenzaron en el año 1998, cuando tenía 16 años, y comencé a aprender como administrar servidores bajo la plataforma Microsoft (Windows Server, Exchange Server, IIS, y otros) y a realizar desarrollos de software con tecnologías ASP y PHP. Un tiempo más tarde comencé a trabajar ya en forma productiva en la administración de plataformas y desarrollo de software.
Desde hace varios años he tenido una intensa actividad en las comunidades profesionales, a través de eventos, webcasts, foros y publicaciones diversas. En Abril de 2014 fui reconocido como Most Valuable Professional (MVP) por parte de Microsoft, estando hoy reconocido en las especialidades "Windows and Devices for IT" y "Microsoft Azure".
En la actualidad me desarrollo como Service Delivery Manager en Algeiba (http://www.algeiba.com), una compañía que brinda soluciones y servicios tecnológicos, dirigiendo un Equipo de Trabajo de más de 20 personas. También soy el Director de TecTimes, un Portal de Tecnología que nació en el año 2012 como parte de un proyecto personal de contribución a la comunidad tecnológica en español.
- [Evento] Microsoft Azure | Tendencias Digitales 2019 – 26/03/2019 - 30 marzo, 2019
- [Evento] Microsoft Azure | Tendencias Digitales 2019 – 06/02/2019 - 12 febrero, 2019
- [Evento] Microsoft Azure | Modernización de Apps con la Nube de Azure – 20/12/2018 - 22 diciembre, 2018
