T-SQL Tuesday #104 – Code You Would Hate To Live Without

sqltuesdayThis post is part of the T-SQL Tuesday blog party. This time around hosted by Bert Wagner (b|t). Each month there is a new topic everyone is invited to talk about, this occasion is about the code that you would hate to live without. It is well known that all database people have our own set of scripts or swiss tools to manage our databases and make our lives much easier. Sigue leyendo “T-SQL Tuesday #104 – Code You Would Hate To Live Without”

Para que sirve el ANSI_PADDING

22effbMuchas veces me he encontrado con scripts que tenían entre sus lineas iniciales la configuración explicita del ANSI_PADDING, y siempre la ignoraba al no saber que implicaba tenerla e ignorar los subsecuentes efectos que podría causar el configurar dicha opción en ON u OFF. Sin embargo hace poco tuve un caso algo extraño para mi en un cliente, el cual reportaba que su data parecía cortada al final. Luego de revisar los procedimientos almacenados que guardaban la información en esta tabla y confirmar que estos no estaban haciendo algo raro al momento de grabar la información, me percate que la tabla al que el cliente hacia referencia fue creada con la opción ANSI_PADDING en OFF.

Esta es una opción de configuración de la sesión que básicamente controla la forma en la que SQL Server almacena los espacios en blanco o ceros situados al final del valor de la columna. Los tipos de datos que son afectados por esta opción de configuración son: char, varchar, binary, varbinary. Es importante notar que NO están considerados los tipos de datos UNICODE, es decir los siguientes: nchar y nvarchar. Finalmente hay que indicar que esta opción solo afecta al momento de crear una tabla, agregar o modificar una columna, y si ya se tienen registros en la tabla, estos NO son afectados con el cambio.

Cuando se crea o se altera una columna y se tiene la opción de ANSI_PADDING ON, el cual es el valor por defecto, las columnas mantendrán los espacios en blanco o ceros al que se coloquen al final del valor de la columna, en el caso particular de los char se rellanaran con espacios en blanco hasta alcanzar el valor máximo de caracteres definidos para la columna. Caso contrario, cuando se crea o altera una columna y la sesión tiene la opción de ANSI_PADDING OFF, SQL Server eliminara los espacios en blanco o ceros situados al final del valor de la columna. Sin embargo si hubieran espacios en blanco o ceros al inicio del valor de la columna, estos se mantienen y no son afectados.

Microsoft recomienda siempre mantener el valor por defecto ANSI_PADDING ON en la documentación oficial

Para poder comprobar esto vamos a crear dos tablas, ambas con columnas char y varchar, pero la diferencia entre ellas sera el valor de la opción ANSI_PADDING que tendrá al momento de crear la tabla.

SET ANSI_PADDING ON
GO
CREATE TABLE TablaConPadding
  (col1 char(50)
  ,col2 varchar(50))
GO

SET ANSI_PADDING OFF
GO
CREATE TABLE TablaSinPadding
  (col1 char(50)
  ,col2 varchar(50))
GO

Una vez creadas vamos a verificar si efectivamente la opción de ANSI_PADDING esta colocada a nivel de las columnas creadas:

select
  t.object_id
  ,t.name as table_name
  ,c.column_id
  ,c.name as column_name
  , c.is_ansi_padded
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name IN ('TablaConPadding','TablaSinPadding')

Ssms_2018-01-09_14-29-46

Ahora veremos su comportamiento insertando algunos valores sin espacios para ver cuanto es en realidad lo que se ha guardado del valor completo, para esto utilizaremos la función DATALENGHT la cual retorna el numero de bytes que ocupa un dato.

-- insertamos los valores sin espacios
insert into TablaConPadding values ('Mundo','Mundo');
insert into TablaSinPadding values ('Mundo','Mundo');

-- revisamos la cantidad de bytes guardados
select 'TablaConPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaConPadding
union all
select 'TablaSinPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaSinPadding

Ssms_2018-01-09_15-08-41

Como se puede observar, en la tabla que se creo con la opción ANSI_PADDING ON, la columna “col1”, la cual es CHAR(50), se relleno con espacios en blanco a la derecha hasta llegar a los 50 bytes de limite que tiene. Mientras que en la tabla que se creo con la opción de ANSI_PADDING OFF, la columna “col1” se comporta como si fuera un VARCHAR ya que no considera todos los espacios en blanco que se colocarían en la derecha como se hizo con la primera tabla.

Ahora ingresaremos valores con tres espacios al inicio y al final, luego verificaremos cuanto espacio ocupan estos datos nuevos dentro de nuestras dos tablas:

-- insertamos los valores
insert into TablaConPadding values ('   Hola   ','   Hola   ');
insert into TablaSinPadding values ('   Hola   ','   Hola   ');

-- revisamos la cantidad de bytes guardados
select 'TablaConPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaConPadding
union all
select 'TablaSinPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaSinPadding

select col1, col2, LEN(col1),LEN(col2) from TablaConPadding
select col1, col2, LEN(col1),LEN(col2) from TablaSinPadding

2018-01-09_16-03-18

Ahora, si notamos la tabla que se creo con ANSI_PADDING ON, si considero los caracteres en blanco tanto a la derecha como a la izquierda, tanto en el caso de los CHAR como el de los VARCHAR, repitiendose el comportamiento de los CHAR de llenar todo el espacio disponible. Finalmente la tabla que se creo con ANSI_PADDING OFF se puede verificar que mantuvo los caracteres en blanco de la izquierda, pero elimino los de la derecha, eso era el comporamiento esperado.

Finalmente actualizaremos la tabla que se creo con ANSI_PADDING OFF para que su columna “col2” funcione con ANSI_PADDING ON y comprobaremos que los registros existentes no son afectados y la nueva configuración entra en funcionamiento desde ese punto en adelante.

-- cambiamos la col2
SET ANSI_PADDING ON
GO
ALTER TABLE TablaSinPadding
  ALTER COLUMN col2 varchar(50)
GO

-- insertamos los valores
insert into TablaSinPadding values ('   Dummy   ','   Dummy   ');

-- revisamos la cantidad de bytes guardados
select 'TablaSinPadding' as TableName
  ,col1, col2
  ,DATALENGTH(col1) BytesCharCol
  ,DATALENGTH(col2) BytesVarcharCol
from TablaSinPadding

2018-01-09_16-44-29

Como vemos en los resultados, luego de agregar el valor “Dummy” con tres espacios al inicio y tres al final, la columna “col2” contabilizo 11 bytes correspondientes a los 3 espacios en blanco iniciales y 3 finales, más los 5 caracteres de la palabra “Dummy”. Los registros existentes se mantienen con sus valores anteriores.