SQL Server Homologar intercalación en una base de datos

Introducción

Muchas veces cuando estamos desarrollando o bien administrando un servidor de base de datos se presenta la necesidad de adquirir una base de datos que fue generada en un servidor diferente, ya sea por la vía de una restauración o por agregar los archivo correspondientes en el directorio seleccionado, es común que esta base de datos adquirida se presenta con una intercalación diferente a la que se tiene definida en el servidor. 

Intercalación de la instancia de Microsoft SQL Server

Para obtener la intercalación que se tiene en la instancia de Microsoft SQL Server del servidor, se sabe que es la que está definida en la base de datos master, de tal forma que es posible obtener esta información utilizando la consulta siguiente:

SELECT collation_name FROM sys.databases where name = 'master'

Como una mejor práctica de operación, se sabe que es importante que la intercalación de las bases de datos sea la misma que la que se tiene en la configuración de la instancia de Microsoft SQL Server en el servidor para mejorar el rendimiento, ya que en caso contrario será necesario que en las consultas se haga referencia a la intercalación adecuada.

Cambiar la intercalación de la base de datos

El cambio de la intercalación de una base de datos es sencillo realizarlo con las sentencias de Microsoft SQL Server, de tal forma que para cambiar la intercalación utilizamos la siguiente sentencia:

ALTER DATABASE [DatabaseName] COLLATE [CollateName]

Si bien esta sentencia cambia la intercalación de la base de datos, no necesariamente cambia la intercalación de todas las columnas de las tablas de la base de datos. Entonces, qué hacer para ese caso, una solución es abrir la definición de cada tabla y cambiar manualmente la intercalación, o utilizar la sentencia ALTER TABLE para cambiar la intercalación de cada columna, esto puede ser una tarea muy lenta.

Que intercalación usan las columnas de las tablas de la base de datos

Cuando ocurre esta situación hace falta una herramienta o bien un script que nos ayude a cambiar la intercalación de todas las columnas de tipo char, varchar, nchar, nvarchar (en versiones previas de Microsoft SQL Server se utilizaban los tipos de datos ntext o text) que haya en las distintas tablas de la base de datos. Es entonces útil saber que toda la información de la base de datos se encuentra definida en las tablas del sistema, más específicamente la tabla INFORMATION_SCHEMA.COLUMNS contiene todas las columnas definidas de las tablas de la base de datos, por lo que la siguiente consulta nos arrojara la información de todas las columnas de tipo char, varchar, nchar y nvarchar (text y ntext en el caso de las versiones anteriores) de las tablas de la base de datos:

USE DatabaseName

SELECT Table_Schema+'.'+Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='BASE TABLE')

  AND (Data_Type = 'char' OR Data_Type = 'varchar' OR Data_Type = 'nchar' OR Data_Type ='nvarchar' OR Data_Type = 'text' OR Data_Type = 'ntext')

ORDER BY Table_Schema,Table_Name

Esto nos dará como resultado las tablas, con sus respectivos esquemas, en nombre de la columna, el tipo y la longitud de la columna, existe una consideración para los tipos de datos varchar(max) y nvarchar(max) que a partir de la versión 2005 se incluye para sustituir los tipos text y ntext, que el valor para la longitud aparece como -1.

Cambiar la intercalación de una columna de la tabla

Para cambiar la intercalación de una columna de los tipos de datos indicados, se debe utilizar el siguiente comando de T-SQL:

ALTER TABLE TableName ALTER COLUMN ColumnName DataType(MaxLength) COLLATE CollateName

Donde; 

• DataType es uno de los tipos char, varchar, nchar, nvarchar, ntext o text
• MaxLength es la longitud de la columna, no se especifica en el caso de text o ntext.
• En una base de datos con 50 tablas en la que participe, se localizaron aproximadamente 210 columnas que contenían los tipos de datos especificados anteriormente. Imagínense que se hiciera la siguiente instrucción por cada una de las columnas identificadas, ello tomaría demasiado tiempo en realizarlo, ademas de lo monótono que se convertiría esa tarea.

Script para realizar el cambio de intercalación en una base de datos

Es por ello que he desarrollado un script que utiliza un cursor para llevar a cabo el cambio en las columnas de todas las tablas por la intercalación especificada, como se muestra a continuación:

/*********************************************************************************************

** Author:  Julio J. Bueyes (jjbueyes@computer.org)

** Date:     Abril, 2009

** Purpose: Standardize the collation of a database and table columns with the collation defined on the server

** DISCLAIMER. Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. 

THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, 

INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. 

**********************************************************************************************/

--- Process variables definition

DECLARE @TableName AS varchar(100)              -- Set the schema and table name 

DECLARE @ColumnName AS varchar(60)             -- Set the column name

DECLARE @DataType AS varchar(10)         -- Set the column data type

DECLARE @MaxLength AS varchar(5)         -- Set the column length

DECLARE @Command AS varchar(1000) -- Set the Sql Command to execute

DECLARE @UseDB  AS varchar(1000)         -- Set the database to use

--- Variables used as constants definition 

DECLARE @Collate AS varchar(50)                 -- Set Collation value

DECLARE @Database AS varchar(50) -- Set Database name

---**** Setting the constant values *****

--- Setting the server collation

SET @collate = (SELECT Collation_name FROM Sys.Databases WHERE Name = 'master')

PRINT 'Collate actually used in Server: ' + @collate;

---******* CHANGE THE DATABASE NAME before execute *******

--- Set the database name to be homologated 

SET @Database = 'DatabaseName';

--- Validate the Database Name

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @Database)

PRINT 'Must specify a valid database name, look at CHANGE THE DATABASE NAME Section'

ELSE

BEGIN

PRINT 'Database to be standardized: ' + @Database;

--- Set the database collation getting from server 

SET @Command = 'ALTER DATABASE ' + @Database + ' COLLATE ' + @Collate;

EXEC (@Command)

--- **** Initiate process *****

--- Set the database to use

SET @Command = 'USE ' + @Database + CHAR(13);

PRINT @Command

EXEC (@Command);

--- Declaring a cursor with the information of the columns of all tables in the database

DECLARE CurTables CURSOR LOCAL FORWARD_ONLY READ_ONLY 

FOR 

SELECT Table_Schema+'.'+Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH 

FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

  AND (Data_Type = 'char' OR Data_Type = 'varchar' OR Data_Type = 'nchar' OR Data_Type = 'nvarchar' OR Data_Type = 'text' OR Data_Type = 'ntext')

ORDER BY Table_Schema,Table_Name;

-- Open the cursor and fetch the first record

OPEN CurTables

FETCH NEXT FROM CurTables INTO @TableName, @ColumnName, @DataType, @MaxLength

-- While there are records in the cursor change columns collation in the database's tables

WHILE @@FETCH_STATUS = 0

BEGIN

       -- Identify text or another data type

       IF @DataType = 'char' OR @DataType = 'varchar' OR @DataType = 'nchar' OR @DataType = 'nvarchar'

           SET @Command = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + '(' + CASE @MaxLength WHEN -1 THEN 'max' ELSE @MaxLength END + ') COLLATE ' + @Collate

       ELSE

           SET @Command = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + ' COLLATE ' + @Collate

       -- Log the command before execute

       PRINT @Command

       -- Execute the command

       EXEC (@Command)

       -- Fetch next record from cursor

       FETCH NEXT FROM CurTables INTO @TableName, @ColumnName, @DataType, @MaxLength

END

-- Close and deallocate cursor

CLOSE CurTables

DEALLOCATE CurTables

--- Finishing Process

PRINT 'The columns of the tables in the specified database have been approved with the server collation'; 

END

Conclusión

Como se ha podido observar, con el anterior script que estoy ofreciendo, se obtiene la intercalación del servidor y se aplica a la base de datos y a las columnas de tipo char, nchar, varchar, nvarchar (y en caso de versiones anteriores los tipos de datos text y ntext), con el objetivo de mejorar el desempeño de las consultas y evitar los usos de la especificación de la intercalación en las mismas.

Mucho he de agradecer sus comentarios sobre el uso de este script, con el fin de llevar a cabo las mejoras correspondientes y que pueda servir en futuras versiones de Microsoft SQL Server.