Introducción
Las columnas computadas no son más que columnas virtuales cuyo contenido es el resultado de una expresión. Normalmente, se utilizan para contener datos basados en el resto de las columnas de la tabla. La expresión puede contener otras columnas no computadas de la tabla, constantes, operadores y funciones, pero no se puede especificar una consulta como expresión para una columna computada.
Al ser columnas «virtuales», no se almacenan en disco como el resto de la tabla. De hecho, no se almacenan sino que se calculan cada vez que se accede a la columna en una consulta. Como verás, puedes forzar a SQL Server a almacenar («persistir») la columna en la tabla con algunas restricciones.
La mejor manera de entender cómo funcionan las columnas computadas es utilizando ejemplos. Al final encontrarás un archivo que contiene todos los scripts utilizados en el artículo, y te mostraremos algunos de ellos en el texto para ilustrar las explicaciones. Para empezar, vamos a crear dos tablas: la primera para contener la información de las facturas y la otra con las líneas de detalle de esas facturas. También puedes encontrar algunas inserciones en el archivo de script para crear datos de ejemplo.
CREATE TABLE invoices( id_invoice INT PRIMARY KEY IDENTITY , customer_name VARCHAR(25));CREATE TABLE detail_lines( id_detail INT PRIMARY KEY IDENTITY , id_invoice_detail INT , product VARCHAR(30) , unit_price MONEY , quantity INT , FOREIGN KEY (id_invoice_detail) REFERENCES invoices (id_invoice));
La forma de crear una columna computada es la misma que crearías otras columnas en una tabla, con una sentencia CREATE TABLE o ALTER TABLE. Para una columna computada, sustituimos el tipo de datos de la columna por la expresión que se utilizará para obtener el contenido de la columna. La sintaxis será el nombre de la columna, seguido de la palabra clave «as», y luego la expresión. Vamos a crear una columna computada en la tabla línea_detallada para almacenar el importe total de la línea, que calculamos multiplicando precio_unitario y cantidad.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Comprobar si se trata de una columna computada
Hay varias formas de confirmar que una columna es realmente una columna computada. Una de ellas es utilizando la función columnproperty() especificando la propiedad «IsComputed».
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Otra forma de obtener información de las columnas computadas es a través de la vista del sistema, sys.computed_columns. Esta vista es una extensión de la vista sys.columns. Esto significa que sys.computed_columns hereda todas las columnas de la vista sys.columns y además añade otras que son específicas de este tipo de columnas. A lo largo del artículo iremos viendo algunas de las columnas de esta vista, a medida que vayamos viendo diferentes características de las columnas computadas. Por ahora, basta con saber que esta vista sólo muestra columnas computadas y que tiene una columna, llamada is_computed, que indica si la columna es computada o no. Obviamente, todos los registros de esta vista tendrán un uno en esta columna.
SELECT name, is_computed FROM sys.computed_columns;
Como el contenido de la columna se calcula cada vez que la columna es referenciada en una consulta, el contenido se actualiza siempre. Cualquier cambio en las columnas que se incluyen en la expresión, se refleja automáticamente en el valor de la columna. Podemos ver esto cambiando la cantidad en un registro de la tabla detail_lines de ejemplo y comprobar el resultado.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
Un paso adelante
Lo que hemos visto hasta ahora con las columnas calculadas es muy básico ya que sólo implica realizar cálculos con otras columnas. Sin embargo, la expresión de las columnas calculadas puede contener funciones, tanto funciones estándar T-SQL como funciones definidas por el usuario (UDF). De esta forma, es posible ampliar mucho más la funcionalidad de estas columnas.
Veamos un ejemplo de ello. Vamos a añadir a la tabla de facturas una columna computada que calcule el importe total de la factura. Para ello, tenemos que obtener el número de factura y consultar la tabla líneas_detalladas para sumar el importe_total de cada registro con ese id de factura. La mejor manera de hacerlo es utilizando una función que reciba el id de la factura como parámetro y devuelva la suma. Después, tenemos que crear la columna que utiliza esta función.
CREATE FUNCTION fn_total_invoice (@invoice_number INT)RETURNS MONEYASBEGIN DECLARE @total MONEY SELECT @total=SUM(total_amount) FROM detail_lines WHERE id_invoice_detail = @invoice_number RETURN @totalENDALTER TABLE invoices ADD total_invoice AS dbo.fn_total_invoice(id_invoice)
Podemos comprobar que esta columna funciona correctamente añadiendo un nuevo registro en la tabla, detalle_líneas, por lo que el total_factura debería cambiar.
INSERT INTO detail_lines (id_invoice_detail,product,unit_price, quantity) VALUES (2,'Cup',9.90,1)SELECT id_invoice, customer_name, total_invoice FROM invoices WHERE id_invoice=2
Alterar la columna
Puede haber situaciones en las que tengas que modificar una columna computada. Lamentablemente, esto no es posible. Para hacer ese cambio, es necesario borrar la columna y volver a crearla con la nueva expresión.
En el caso de que la columna computada utilice una función externa, no se nos permitirá modificar esta función. Si lo intentamos, recibiremos un error indicando que esta función está vinculada a la tabla. Para cambiar la función, es necesario borrar la columna, realizar la modificación de la función y, finalmente, volver a crear la columna con la nueva versión de la función.
Podemos obtener la definición de la columna en la columna «definition» de la vista sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Almacenamiento de una columna computada
Como hemos comentado anteriormente, estas columnas son «virtuales» por lo que no se almacenan físicamente en la tabla. Sin embargo, existe la posibilidad de forzar que el cálculo se almacene físicamente en la tabla, lo que se llama «persistir» la columna. Esto puede mejorar el rendimiento con las sentencias SELECT ya que evita tener que realizar el cálculo de la columna cada vez que se hace referencia a ella.
Además, para persistir la columna, la expresión utilizada para crearla tiene que ser «determinista». Como podemos ver en el sitio web de Microsoft, «las funciones deterministas siempre devuelven el mismo resultado cada vez que se llaman con un conjunto específico de valores de entrada y dado el mismo estado de la base de datos.» (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Si queremos saber si SQL Server considera la expresión de una columna computada como determinista o no, podemos utilizar la función columnproperty() con la propiedad «IsDeterministic».
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Si la definición de la columna es una función definida por el usuario, también se puede verificar si esa función en sí es determinista o no. Para ello, debes utilizar la función objectproperty() con la propiedad IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Como puedes ver en las consultas, la columna del primer ejemplo, en la que calculamos el precio total del detalle, se considera determinista. Sin embargo, la función que calcula el precio total de la factura se considera no determinista. De esta forma, sólo la columna precio_total de la tabla detalle_tabla puede ser almacenada en la tabla.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
De nuevo en la vista sys.computed_columns podemos ver el campo is_persisted, que nos indicará si la columna es persistente o no en la tabla.
Índices con columnas computadas
Es posible utilizar columnas computadas en los índices, aunque deben cumplir varios requisitos:
- Propiedad: Todas las funciones que se utilizan en la definición de la columna computada deben ser propiedad del mismo usuario que la tabla.
- Determinismo: La columna computada debe ser determinista. Asimismo, si la columna contiene expresiones CLR, además de ser determinista, la columna debe ser persistente.
- Precisión: La expresión de la columna calculada debe ser precisa. Esto implica que no puede ser del tipo de datos «float» o «real». Tampoco puede utilizar este tipo de datos en su definición. Esta característica se puede verificar con la función columnproperty() especificando la propiedad IsPrecise.
- Tipo de datos: La columna computada no puede ser de los tipos text, ntext o image. Además, si la expresión contiene tipos de datos image, ntext, text, varchar (max), nvarchar (max), varbinary (max) o xml, sólo puede utilizarse si el tipo de datos resultante de la expresión está permitido en un índice.
Además de estas consideraciones, las conexiones utilizadas para crear la columna y la utilizada para crear el índice deben tener ciertas configuraciones para poder realizar estas acciones.
La conexión para crear la columna computada debe tener activa la opción ANSI_NULLS. Esto se puede comprobar con la función columnproperty(), especificando la propiedad IsAnsiNullsOn.
La conexión para crear el índice es, así como las conexiones para realizar la inserción, actualización y borrado de registros que influyen en el índice deben tener activas las opciones ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER. Además, la opción NUMERIC_ROUNDABORT debe estar desactivada.
Últimas consideraciones
Para finalizar, vamos a repasar algunos aspectos adicionales que es necesario conocer para el correcto uso de las columnas computadas.
Obviamente, las columnas computadas no pueden ser actualizadas, ni incluidas en la lista de valores de una acción INSERT. Aunque, las columnas computadas pueden formar parte de la lista de resultados de una sentencia select, también pueden ser utilizadas en las cláusulas WHERE, ORDER BY, o en todas aquellas en las que se pueda poner una expresión.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
A pesar de lo anterior, una columna computada no puede ser utilizada en la definición de restricciones DEFAULT o FOREIGN KEY. Tampoco puede ser con una definición de restricción NOT NULL.
Por otro lado, las columnas computadas pueden ser utilizadas como parte de las restricciones PRIMARY KEY o UNIQUE. Para ello, la definición de columna computada debe ser una expresión determinista.
Conclusión
El uso de columnas computadas puede ser muy útil en algunas situaciones. Hay que estudiar bien dónde utilizarlas, por las restricciones que tienen, especialmente para crear índices y persistirlas. Esto es sólo el principio. Siéntase libre de probar cosas nuevas y experimentar con las columnas computadas para encontrar nuevas posibilidades.