El problema
Un amigo me preguntó la causa de que sus procedimientos almacenados no funcionaran bien con las comparaciones de cadenas a partir de instalar su base de datos con cierta intercalación (collation) de SQL Server 2008.
Su base de datos tiene varios años de evolución y maduración y se ha migrado desde SQL Server 2000. Originalmente la base de datos se había creado con una intercalación de SQL Server (la famosa SQL_Latin1_General_CP1_CI_AS). Cuando se migró a SQL Server 2008 se decidió actualizar la intercalación a una moderna de Windows llamada Latin1_General_100_CI_AI (Unicode no sensible ni a mayúsculas ni a los acentos).
A partir de entonces las comparaciones simples de variables de cadena en los procedimientos almacenados no funcionaban y esta consulta literal tan simple de comparación mediante LIKE, devolvía un resultado contrario al esperado, es decir devuelve “No” en vez de “Yes”:
IF '/SYSTEM_ROOT/DEVELOPMENT_COMPANY/DEVELOPMENT' LIKE'%/DEVELOPMENT'
SELECT 'Yes' asIsLikeLiteral
ELSE
SELECT 'No' asIsLikeLiteral
Lógicamente aquí era de esperar que la cadena “[lo que sea antes]/DEVELOPMENT” sí se encuentre en la primera cadena literal.
El primer problema es que estos literales son interpretados por el motor como cadenas con una intercalación no Unicode entonces si el servidor está configurado (instalado) con una intercalación Unicode como Latin1_General_100_CI_AI, los caracteres no serán interpretados de la manera que esperamos al realizar la comparación y la ordenación.
Lo más simple para ver esto es colocar el indicador de literales Unicode (N) al frente de las cadenas e inmediatamente se obtendrá el resultado correcto esperado, es decir “Yes” (ver y o ejecutar los ejemplos de más abajo en una ventana de su SQL Server Management Studio 2008, si es otra versión previa comente la intercalación de tipo “_100_”, ya que s nueva en SQL Server 2008).
El echo de que los procedimientos almacenados o funciones no realicen bien las comparaciones se debe a que todos los valores de las variables de cadena de la base de datos fueron originalmente definidas como “varchar” y almacenados con la intercalación antigua de SQL_Latin1_General_CP1_CI_AS (estas intercalaciones de SQL se mantienen por compatibilidad con el pasado y se recomienda utilizar las de Windows o bien actualizarlas).
La solución
Lo más adecuado es tomar su base de datos y hacer lo siguiente:
- Transformar su base de datos (los datos almacenados) a la intercalación del servidor mediante este script maravilloso de Igor F. Kovalenko.
- Actualice todas sus tablas para que utilicen tipos de datos Unicode en las cadenas (nchar, nvarchar, ntext).
- Actualice todos sus procedimientos almacenados y funciones y cambie las declaraciones de variables de tipo “varchar” a “nvarchar” (vea el último ejemplo de la serie de consultas más abajo para que vea por qué).
- Si tuvo la necesidad en el pasado de declarar algo como “varchar(8000)” y espera cadenas de ese largo ya no puede utilizar nvarchar con ese tamaño porque nvarchar está limitado a 4000 caracteres cuando quiere especificar la longitud. Entonces, si requiere más de 4000 caracteres, declare las variables como “nvarchar(max)” que soporta hasta 2 GB.
Fíjese que si los valores no están almacenados en la intercalación correcta aunque utilice COLLECT no obtendrá el resultado adecuado, por eso debe realizar la transformación de datos.
Para sus nuevos desarrollo, no lo dude, utilice una intercalación de Windows Unicode como la indicada aquí, que acepta un amplia variedad de idiomas humanos.
Demostrador
-------------------------------------------------
-- Comparación literal
-------------------------------------------------
IF '/SYSTEM_ROOT/DEVELOPMENT_COMPANY/DEVELOPMENT' LIKE'%/DEVELOPMENT'
SELECT 'Yes' asIsLikeLiteral
ELSE
SELECT 'No' asIsLikeLiteral
-------------------------------------------------
-- Comparación literal explícita Unicode
-------------------------------------------------
IF N'/SYSTEM_ROOT/DEVELOPMENT_COMPANY/DEVELOPMENT' LIKEN'%/DEVELOPMENT'
SELECT 'Yes' asIsLikeUnicode
ELSE
SELECT 'No' asIsLikeUnicode
-------------------------------------------------
-- Conversión a una intercalación determinada
-------------------------------------------------
IF '/SYSTEM_ROOT/DEVELOPMENT_COMPANY/DEVELOPMENT' COLLATE Latin1_General_CI_AI LIKE '%/DEVELOPMENT' COLLATE Latin1_General_CI_AI
SELECT 'Yes' asIsLikeCollation
ELSE
SELECT 'No' asIsLikeCollation
-------------------------------------------------
-- Conversión a una intercalación determinada (100)
-------------------------------------------------
IF '/SYSTEM_ROOT/DEVELOPMENT_COMPANY/DEVELOPMENT' COLLATE Latin1_General_100_CI_AI LIKE '%/DEVELOPMENT' COLLATE Latin1_General_100_CI_AI
SELECT 'Yes' asIsLikeCollation100A
ELSE
SELECT 'No' asIsLikeCollation100A
-------------------------------------------------
-- Conversión a una intercalación determinada (100)
-------------------------------------------------
IF N'/SYSTEM_ROOT/DEVELOPMENT_COMPANY/DEVELOPMENT' COLLATE Latin1_General_100_CI_AI LIKE N'%/DEVELOPMENT' COLLATE Latin1_General_100_CI_AI
SELECT 'Yes' asIsLikeCollation100B
ELSE
SELECT 'No' asIsLikeCollation100B
-------------------------------------------------
-- Comparación con varchar (cadena no unicode)
-------------------------------------------------
DECLARE @varCharInput1 varchar(500)
DECLARE @varCharInput2 varchar(500)
SET @varCharInput1 ='/SYSTEM_ROOT/DEVELOPMENT_COMPANY/DEVELOPMENT'
SET @varCharInput2 ='%/DEVELOPMENT'
IF @varCharInput1 LIKE@varCharInput2
SELECT 'Yes' asIsLikeVarchar
ELSE
SELECT 'No' asIsLikeVarchar
-------------------------------------------------
-- Finalmente este es el punto.
-- Comparación con nvarchar (cadena unicode)
-------------------------------------------------
DECLARE @nVarCharInput1 nvarchar(500)
DECLARE @nVarCharInput2 nvarchar(500)
SET @nVarCharInput1 =N'/SYSTEM_ROOT/DEVELOPMENT_COMPANY/DEVELOPMENT'
SET @nVarCharInput2 =N'%/DEVELOPMENT'
IF @nVarCharInput1 LIKE@nVarCharInput2
SELECT 'Yes' asIsLikeNVarchar
ELSE
SELECT 'No' as IsLikeNVarchar
Los resultados
---(FIN)---
