SQL Server

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.

Uno de los conceptos fundamentales y a los que más atención debemos prestar cuando trabajamos con bases de datos es el de las transacciones. Las transacciones nos permiten agrupar una serie de operaciones en una única unidad de trabajo. Dentro de este contexto, dos comandos clave son COMMIT y ROLLBACK, que nos permiten confirmar o deshacer transacciones respectivamente. Pero también existen otros aspectos fundamentales que debemos conocer como las transacciones implícitas y explícitas y los conceptos BEGIN TRAN, BEGIN TRY y BEGIN CATCH. Si todo esto te suena a chino no te preocupes, acompáñame en este artículo en el que profundicemos en estos conceptos y en cómo la gestión de transacciones puede influir en nuestra estrategia de manejo de datos.

¿Qué son las transacciones?

Una transacción es un conjunto de operaciones SQL que se ejecutan como una unidad indivisible. El principio básico es que todas las operaciones dentro de una transacción deben completarse con éxito; de lo contrario, ninguna de ellas se aplicará. Este comportamiento garantiza la integridad y consistencia de los datos. 

Si echamos la vista atrás, cuando empezamos a estudiar bases de datos relacionales siempre nos dicen que se basan en el concepto ACID, que es un acrónimo que hace referencia a un conjunto de propiedades fundamentales que aseguran que las transacciones se manejen de manera confiable.

Propiedades ACID

Las transacciones en bases de datos relacionales como SQL Server deben cumplir con las propiedades ACID. Pero, ¿qué es ACID? ACID son las iniciales de Atomicidad, Consistencia, Aislamiento (Isolation en inglés) y Durabilidad. Veamos qué significa:

Atomicidad (Atomicity): Una transacción debe ser atómica, es decir, todas las operaciones dentro de una transacción se consideran como una sola unidad. Cuando una transacción tiene varios pasos, o se ejecutan todos o ninguno. Si alguna operación falla, toda la transacción debe fallar y deshacerse.

Consistencia (Consistency): Una transacción debe llevar la base de datos de un estado consistente a otro estado consistente. Esto significa que las reglas y restricciones de la base de datos deben ser respetadas antes y después de la transacción.

Aislamiento (Isolation): Las transacciones concurrentes deben aislarse unas de otras y cada transacción debe ser independiente. Esto asegura que las transacciones no afecten el estado intermedio de otras transacciones en curso.

Durabilidad (Durability): Una vez que una transacción ha sido confirmada (COMMIT), los cambios realizados deben persistir en la base de datos, incluso en caso de fallos del sistema.

Commit: Confirmando transacciones

El comando COMMIT se utiliza para confirmar una transacción, haciendo permanentes todos los cambios realizados desde el inicio de la misma. Una vez que se emite un COMMIT, no hay marcha atrás: los cambios quedan guardados en la base de datos de forma definitiva.

BEGIN TRANSACTION
-- Operaciones SQL como INSERT, UPDATE o DELETE
COMMIT;

En este bloque de código, cualquier operación SQL entre BEGIN TRANSACTION y COMMIT será confirmada y guardada permanentemente al ejecutar el COMMIT.

Rollback: Deshaciendo transacciones

El comando ROLLBACK es el contrario a COMMIT. Nos permite deshacer todas las operaciones realizadas en la transacción actual, volviendo la base de datos a su estado anterior al inicio de la transacción. Esto es esencial para manejar errores y mantener la integridad de los datos.

BEGIN TRANSACTION
-- Operaciones SQL como INSERT, UPDATE o DELETE
ROLLBACK;

Aquí, si algo sale mal o si detectamos algún error en las operaciones SQL, el ROLLBACK deshará todos los cambios desde el BEGIN TRANSACTION, asegurando que la base de datos no quede en un estado inconsistente.

Manejo avanzado de transacciones: BEGIN TRAN, TRY, CATCH

Para un manejo más completo de las transacciones, SQL Server ofrece estructuras adicionales como BEGIN TRANSACTION (o su abreviatura BEGIN TRAN), BEGIN TRY y BEGIN CATCH, que nos permiten manejar errores de manera efectiva dentro de las transacciones.

BEGIN TRAN

El comando BEGIN TRANSACTION o BEGIN TRAN se utiliza para iniciar una nueva transacción. Este comando es esencial cuando queremos agrupar varias operaciones SQL en una sola unidad de trabajo. La sintaxis es la que ya hemos estado viendo en los anteriores ejemplos:

BEGIN TRAN
-- Operaciones SQL como INSERT, UPDATE o DELETE
COMMIT;

TRY y CATCH

Las estructuras TRY y CATCH en SQL Server nos permiten capturar y manejar errores dentro de las transacciones. Utilizando BEGIN TRY y BEGIN CATCH, podemos definir bloques de código que se ejecutan en caso de éxito o fallo de la transacción.

BEGIN TRY
    BEGIN TRANSACTION
    -- Operaciones SQL como INSERT, UPDATE o DELETE
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK
    – Operaciones de manejo de errores como escribir un log
    PRINT ERROR_MESSAGE()
END CATCH;

En este ejemplo, si alguna operación dentro del bloque TRY falla, el control pasa al bloque CATCH, donde podemos realizar un ROLLBACK y manejar el error de manera apropiada.

Transacciones implícitas en SSMS

En SQL Server Management Studio, las transacciones por defecto son automáticas, es decir, no es necesario declarar el BEGIN TRANSACTION al inicio ni el COMMIT al terminar, se hará automáticamente. Sin embargo, pueden ser configuradas para ser implícitas y que debamos ser nosotros quienes elijamos si hacer COMMIT o ROLLBACK. Esto significa que cada operación individual se convierte automáticamente en una transacción. Aunque puede parecer ventajoso, esta configuración tiene tanto ventajas como inconvenientes. Si la transacción es abierta manualmente con un BEGIN TRANSACTION entonces hablamos de transacciones explícitas.

En resumen, el comportamiento por defecto son las transacciones automáticas y cada operación es una transacción individual que se confirma (COMMIT) automáticamente. Podemos definir de manera explícita una transacción con BEGIN TRANSACTION y perdurará hasta que hagamos un COMMIT o ROLLBACK manualmente. Si lo que queremos es anular el COMMIT automático en las operaciones individuales podemos configurar las transacciones implícitas.

Ventajas de las transacciones implícitas

Una de las principales ventajas de las transacciones implícitas es que no necesitamos preocuparnos por envolver nuestras operaciones en bloques de transacción explícitos. Esto puede simplificar el código y reducir errores por omisión de transacciones. Además, en escenarios de producción puede reducir el riesgo de errores por ejecución de código no deseado.

Inconvenientes de las transacciones implícitas

Sin embargo, las transacciones implícitas también tienen importantes desventajas. La principal es el riesgo de olvidar confirmar o deshacer una transacción, lo que puede llevar a bloqueos o a un uso excesivo de recursos. Otro de los problemas comunes es que mientras una transacción esté abierta el log no se va a vaciar, pudiendo crecer descontroladamente. Por último, al no ser automáticas ni explícitas, es más difícil llevar un control riguroso de cuándo se inician y terminan las transacciones, lo cual puede complicar la depuración y el mantenimiento del código. Además, la automatización de transacciones puede generar una sobrecarga innecesaria en operaciones simples que no requieren aislamiento transaccional.

Configuración de transacciones implícitas en SSMS

Para habilitar las transacciones implícitas en SSMS, debemos modificar una configuración específica. En SSMS, vamos a Tools > Options > Query Execution > SQL Server > ANSI, y activamos la opción «SET IMPLICIT_TRANSACTIONS». Esto cambia el comportamiento predeterminado de las consultas ejecutadas en el editor.

Una vez activada esta opción, cada instrucción SQL iniciará automáticamente una transacción que deberá ser confirmada con COMMIT o deshecha con ROLLBACK. Es crucial recordar en todo momento que hemos activado este comportamiento para evitar los problemas que acabamos de describir hace un momento.

Por otro lado, independientemente de la configuración general de nuestro SSMS o cuando estamos trabajando con otras herramientas es posible editar este comportamiento para una sesión en concreto. Esto lo haremos con el comando SET IMPLICIT_TRANSACTION seguido de un valor ON u OFF en función de lo que deseemos.

Buenas Prácticas en la gestión de transacciones

Para maximizar el beneficio de controlar manualmente las transacciones y minimizar los riesgos, es esencial seguir algunas buenas prácticas. Primero, debemos asegurar que las transacciones sean de un tamaño aceptable. Como norma general una transacción que afecte a varios registros será más eficiente que muchas transacciones individuales pero hasta cierto punto. Llega un momento que el tamaño de la transacción puede acarrear problemas. Existe una recomendación aproximada de no superar los 10.000 registros por transacción pero depende en gran medida de la complejidad de la misma y del volumen de los datos. Lo que sí está claro es que esto nos va a reducir la probabilidad de sufrir bloqueos y a mejorar el rendimiento general de nuestro servidor. También es recomendable incluir manejo de errores dentro de nuestras transacciones para poder realizar un ROLLBACK automático en caso de fallos.

Conclusión

El manejo adecuado de transacciones en SQL Server mediante los comandos COMMIT y ROLLBACK es fundamental para asegurar la integridad y consistencia de nuestros datos. Además, las transacciones implícitas nos pueden ofrecer ventajas en términos de simplicidad y rapidez de desarrollo, pero siempre prestando atención, pues tienen riesgos que debemos gestionar con cuidado. Entender estos conceptos y aplicar buenas prácticas nos permitirá desarrollar sistemas más robustos y eficientes.

 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.