Las columnas calculadas en SQL Server pueden ser una herramienta poderosa para realizar cálculos y transformaciones en los datos dentro de una tabla. Sin embargo, el costo de calcular estas columnas en el momento de la consulta puede ser significativo, especialmente para conjuntos de datos grandes. Una forma de optimizar el rendimiento es convertir las columnas calculadas en persistidas, lo que traslada el costo de cálculo al momento de la inserción o actualización.

En este artículo, exploraremos cómo convertir columnas calculadas en persistidas en SQL Server y discutiremos algunas consideraciones importantes.

Convertir columnas calculadas en persistidas

El proceso de convertir columnas calculadas en persistidas es relativamente simple. Puede utilizar la declaración ALTER TABLE con la cláusula ALTER COLUMN para agregar la palabra clave PERSISTED a la definición de la columna. Aquí hay un ejemplo:

ALTER TABLE dbo.tablename 
  ALTER COLUMN columnname
  ADD PERSISTED;

Vale la pena señalar que puede ejecutar esta declaración varias veces sin ningún problema. SQL Server responderá con “El comando se completó correctamente” incluso si la columna ya está marcada como persistida.

Consideraciones para convertir columnas calculadas en persistidas

Antes de intentar convertir columnas calculadas en persistidas, hay algunas consideraciones importantes a tener en cuenta:

Columnas calculadas determinísticas

Solo se pueden marcar como persistidas las columnas calculadas determinísticas. Una función determinística siempre devuelve la misma salida para la misma entrada. Por ejemplo, la función GETDATE() no es determinística porque puede devolver un valor diferente cada vez que se llama. Por lo tanto, una columna calculada que utiliza GETDATE() no se puede marcar como persistida.

Puede utilizar la función COLUMNPROPERTY para determinar si una columna calculada es determinística. Aquí hay un ejemplo:

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.table1'), N'Now', N'IsDeterministic');

Si el resultado es 1, la columna es determinística y potencialmente se puede marcar como persistida.

Funciones definidas por el usuario

Las columnas calculadas que utilizan funciones definidas por el usuario pueden no estar marcadas como persistidas de forma predeterminada. SQL Server considera que las funciones definidas por el usuario no son determinísticas a menos que se marquen explícitamente con la opción WITH SCHEMABINDING. Esta opción le indica a SQL Server que la función es determinística y se puede utilizar en columnas calculadas persistidas.

Aquí hay un ejemplo de una función definida por el usuario con y sin la opción WITH SCHEMABINDING:

CREATE FUNCTION dbo.udf1(@id INT)
RETURNS INT 
AS
BEGIN
  RETURN (SELECT @id + 1);
END

CREATE FUNCTION dbo.udf2(@id INT)
RETURNS INT 
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT @id + 1);
END

Solo la segunda función, dbo.udf2, se puede utilizar en una columna calculada persistida.

Configuración ANSI_NULLS

La configuración ANSI_NULLS también puede afectar la capacidad de marcar columnas calculadas como persistidas. Si una tabla se creó con ANSI_NULLS OFF, es posible que encuentre problemas al intentar persistir columnas calculadas. Para evitar esto, vuelva a crear la tabla con ANSI_NULLS ON o evite persistir columnas calculadas en tablas con ANSI_NULLS OFF.

Conclusión

La conversión de columnas calculadas en persistidas en SQL Server puede mejorar significativamente el rendimiento de las consultas al trasladar el costo de cálculo al momento de la inserción o actualización. Sin embargo, hay varias consideraciones a tener en cuenta, como la determinismo de las columnas calculadas y el uso de funciones definidas por el usuario. Al comprender estas consideraciones y utilizar las técnicas adecuadas, puede optimizar el rendimiento de su base de datos de SQL Server.

En un artículo futuro, exploraremos el impacto de hacer que una columna sea persistida en tablas grandes y cuando la definición de la columna hace referencia a funciones definidas por el usuario costosas. También discutiremos cómo determinar si una función es no determinística o si se necesita la opción WITH SCHEMABINDING.