Collation en SQL

La collation (intercalación) es uno de esos conceptos cruciales de SQL Server y Azure SQL sin embargo, a menudo se pasa por alto o se confunde con otros términos como idioma o codificación. Dado que la collation impacta directamente en la forma en que se procesan las consultas de texto, su correcta configuración es vital para evitar problemas de rendimiento y coherencia en nuestras aplicaciones. En resumen, la collation define cómo se ordenan y comparan los datos de texto en una base de datos, incluyendo reglas sobre la sensibilidad a mayúsculas, acentos y otros caracteres especiales. Es de esas configuraciones de las que uno solo se acuerda cuando tiene un error y ya es tarde para solucionarlo.

¿Qué es la Collation?

La collation en SQL Server es una configuración que define las reglas para comparar y ordenar cadenas de texto. Estas reglas determinan, por ejemplo, si las comparaciones deben ser sensibles a mayúsculas o minúsculas (case-sensitive o case-insensitive), si deben distinguir entre caracteres con y sin acento (accent-sensitive o accent-insensitive), y cómo se ordenan los caracteres según un conjunto de reglas culturales o técnicas específicas.

Una confusión común es suponer que al establecer una collation se está configurando el idioma de la base de datos o la codificación de los datos. Esto no es correcto. Es importante entender que la collation no es lo mismo que el idioma o la codificación de los caracteres.

Diferencias entre intercalación, idioma y codificación

Mientras que la collation puede estar basada en un conjunto de reglas lingüísticas propias de un idioma (por ejemplo, cómo se ordenan las letras en español), no impone que los datos en la base de datos estén escritos en ese idioma.

El idioma, por definición, se refiere al conjunto de reglas gramaticales y de escritura utilizadas en un texto. La codificación, por su parte, la podemos definir como la manera de representar los caracteres en bytes (por ejemplo, UTF-8 o ASCII). Aunque la collation puede estar influenciada por el idioma, ya que define las reglas de ordenación basadas en ese idioma, no determina el idioma del texto en sí.

Por otro lado, la codificación se encarga de la representación física de los caracteres, es decir, cómo los caracteres son almacenados y transmitidos. SQL Server usa la collation para interpretar estas representaciones y aplicar las reglas correctas durante las comparaciones y ordenaciones, independientemente de la codificación subyacente.

Por ejemplo, si seleccionamos una collation basada en inglés, como SQL_Latin1_General_CP1_CI_AS, esta permitirá que las comparaciones de texto sean insensibles a mayúsculas y acentos, de acuerdo con las reglas del idioma inglés. Sin embargo, esto no impide que los datos almacenados estén en otro idioma, como español o alemán; simplemente determina cómo se manejarán y ordenarán esos datos.

En cuanto a la codificación, ésta se refiere a cómo los datos se almacenan y se interpretan a nivel de bytes. SQL Server maneja esto automáticamente, y la collation se encarga de aplicar las reglas adecuadas durante las operaciones con texto, como ORDER BY o WHERE.

Niveles de Intercalación

SQL Server está preparado para manejar distintas collation a nivel de instancia , base de datos e incluso columna. Cada una de estas intercalaciones tiene un alcance distinto y, por tanto, si deseamos modificarlas el proceso será diferente. Lo que sí os puedo asegurar es que en todos los casos cambiar una collation es peor que un dolor de muelas. Además, una discrepancia de collation puede ser incompatible con operaciones de comparación de datos o de lectura en un objeto y escritura en otro, existen maneras de salvarlo pero, nuevamente, será un suplicio para el desarrollador.

Collation a nivel de instancia en SQL Server

La collation a nivel de instancia se configura durante la instalación de SQL Server y define el comportamiento predeterminado para todas las bases de datos y objetos creados dentro de esa instancia. Esta collation será la de las bases de datos de datos de sistema y es clave en el futuro comportamiento de SQL Server. Además, si no especificamos una collation al crear después una base de datos de usuario o un objeto, heredará la de la instancia.

Esta collation de instancia influye en la forma en que se gestionan las comparaciones y ordenaciones en todas las bases de datos del sistema, afectando aspectos como la sensibilidad a mayúsculas y acentos. Es esencial elegir la collation correcta durante la instalación, ya que cambiarla posteriormente puede ser complicado, requiriendo a veces la reinstalación del servidor o la migración de las bases de datos.

Collation a nivel de base de datos

Cada base de datos en SQL Server puede tener su propia collation, independientemente de la collation de la instancia. Esto es útil cuando necesitamos manejar datos en diferentes idiomas o con diferentes requisitos de comparación. Por ejemplo, una base de datos que maneje datos en japonés puede necesitar una collation distinta de otra que gestione datos en francés no por el idioma o por el juego de caracteres sino porque los criterios de ordenación de los caracteres cambia.

Al crear una base de datos, podemos especificar la collation utilizando la cláusula COLLATE. Si necesitamos cambiar la collation de una base de datos existente, esto se puede hacer con el comando ALTER DATABASE, aunque el cambio no afectará automáticamente a las tablas y columnas ya existentes. Por lo tanto, si cambiamos la collation de una base de datos, debemos asegurarnos de revisar y actualizar también las collations de las columnas si es necesario.

Collation a nivel de columna

El nivel más granular de collation se encuentra en las columnas de las tablas. Esto nos permite tener columnas con diferentes collations dentro de la misma tabla, lo cual es especialmente útil en escenarios donde necesitamos manejar datos de diferentes orígenes o con distintos requisitos culturales.

Para definir la collation de una columna al momento de crear una tabla, utilizamos la cláusula COLLATE después del tipo de dato de la columna. Si necesitamos cambiar la collation de una columna existente, podemos hacerlo con el comando ALTER TABLE ALTER COLUMN, aunque este proceso puede ser costoso en términos de rendimiento, especialmente si la tabla contiene grandes volúmenes de datos.

Cómo trabajar con datos con distinta Collation

Trabajar con datos que tienen diferentes collations requiere precaución, especialmente al realizar comparaciones o uniones entre columnas. SQL Server permite especificar la collation en las consultas utilizando la cláusula COLLATE en las expresiones de texto, lo que nos permite unificar la collation de los datos en una operación específica. Esta es la única forma de poder actuar cuando tenemos distintas collations entre objetos, por ejemplo, entre bases de datos e instancia que, como hemos visto antes afecta a las bases de datos de sistema y por tanto a las tablas temporales en TempDB.

Otro ejemplo, si tenemos dos columnas con diferentes collations y necesitamos compararlas, podemos utilizar COLLATE para especificar la collation a usar durante la comparación, evitando así errores cómo «Cannot resolve the collation conflict«. De esta manera, podemos trabajar con datos multilingües o de diferentes fuentes de manera más segura y eficiente.

Cambiar la Collation: Impacto y consideraciones

Cambiar la collation en SQL Server, ya sea a nivel de instancia, base de datos o columna, no es una tarea trivial y debemos realizarla con precaución. Los cambios a nivel de instancia son los más delicados, ya que afectan a todas las bases de datos y objetos de esa instancia. Esto implica una reinstalación de facto de la instancia y, aunque no perderemos los datos, sí que pueden verse afectados los objetos de instancia como logins, credenciales, etc…

A nivel de base de datos, es fundamental considerar que las tablas y columnas existentes no cambiarán automáticamente, lo que podría llevar a una mezcla de collations dentro de la misma base de datos. Este tipo de situaciones complican la gestión y pueden dar lugar a problemas de rendimiento o coherencia de datos. Por ello, es crucial planificar estos cambios con antelación y realizar pruebas exhaustivas en un entorno controlado antes de aplicarlos en producción.

Conclusión

La correcta gestión de la collation en SQL Server y Azure SQL es esencial para garantizar la integridad y el rendimiento de nuestras bases de datos. Entender las diferencias entre collation, idioma y codificación nos permite tomar decisiones más informadas y evitar errores comunes que pueden surgir al trabajar con datos multilingües o de diferentes fuentes. Al aplicar las mejores prácticas en la configuración y el manejo de collations, podemos optimizar nuestras bases de datos para un rendimiento y consistencia óptimos, asegurando que nuestras aplicaciones manejen datos de texto de manera eficaz y sin sorpresas inesperadas.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.