Columnas calculadas en SQL Server

Resumen: en este tutorial, aprenderá a usar las columnas calculadas de SQL Server para reutilizar la lógica de cálculo en varias consultas.

Introducción a las columnas calculadas de SQL Server

Vamos a crear una nueva tabla con el nombre de persons para ver los ejemplos:

CREATE TABLE persons
(
    person_id  INT PRIMARY KEY IDENTITY,
    first_name NVARCHAR(100) NOT NULL,
    last_name  NVARCHAR(100) NOT NULL,
    dob        DATE
);

Ahora inserta dos filas en la tabla de persons

INSERT INTO
    persons(first_name, last_name, dob)
VALUES
    ('Estrada','Web Group','1990-05-01'),
    ('Estrada','Web Group','1995-03-01');

Para consultar los nombres completos de las personas en la tabla persons, normalmente usa la función CONCAT() o el operador + de la siguiente manera:

SELECT
    person_id,
    first_name + ' ' + last_name AS full_name,
    dob
FROM
    persons
ORDER BY
    full_name;

Agregar la expresión full_name con first_name + ' ' + last_name en cada consulta no es conveniente.

Afortunadamente, SQL Server nos brinda una función llamada columnas calculadas que permite agregar una nueva columna a una tabla con el valor derivado de los valores de otras columnas en la misma tabla.

Por ejemplo, puedes agregar la columna full_name a la tabla de personas usando la columna ALTER TABLE ADD de la siguiente manera:

ALTER TABLE persons
ADD full_name AS (first_name + ' ' + last_name);

Cada vez que consultas datos de la tabla de persons, SQL Server calcula el valor de la columna full_name en función de la expresión first_name + ' ' + last_name y devuelve el resultado.

Aquí está la nueva consulta, que es más compacta

SELECT
    person_id,
    full_name,
    dob
FROM
    persons
ORDER BY
    full_name;

Si examinas la tabla de personas, puedes ver que aparece la nueva columna full_name en la lista de columnas:

Columnas calculadas persistentes

Las columnas calculadas se pueden conservar. Significa que SQL Server almacena físicamente los datos de las columnas calculadas en el disco.

Cuando cambian los datos en la tabla, SQL Server calcula el resultado en función de la expresión de las columnas calculadas y almacena físicamente los resultados en estas columnas persistentes. Cuando consulta los datos de las columnas calculadas persistentes, SQL Server solo necesita recuperar los datos sin realizar ningún cálculo. Esto evita la sobrecarga de cálculo con el costo del almacenamiento adicional.

Considera el siguiente ejemplo.

Primero, elimina la columna full_name de la tabla de personas:

ALTER TABLE persons
DROP COLUMN full_name;

Luego, agrega la nueva columna full_name a la tabla de personas con la propiedad PERSISTED:

ALTER TABLE persons
ADD full_name AS (first_name + ' ' + last_name) PERSISTED;

Ten en cuenta que una columna calculada se conserva solo si su expresión es determinista. Significa que, para un conjunto de entradas, la expresión siempre devuelve el mismo resultado.

Por ejemplo, la expresión first_name + ' ' + last_name es determinista. Sin embargo, la función GETDATE() es una función no determinista porque devuelve un valor diferente en un día diferente.

Esta fórmula devuelve la edad en años según la fecha de nacimiento y la fecha actual:

(CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000

Podemos usar esta expresión para definir la edad en la columna calculada del año.

La siguiente declaración intenta definir la columna age_in_yearcomputed como una columna calculada persistente:

ALTER TABLE persons
ADD age_in_years
    AS (CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000
PERSISTED;

El servidor SQL emite el siguiente error:

Computed column 'age_in_years' in table 'persons' cannot be persisted because the column is non-deterministic.

Si eliminas la propiedad PERSISTED, debería funcionar:

ALTER TABLE persons
ADD age_in_years
    AS (CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000;

Ahora, puedes consultar la edad en años de las personas en la tabla de persons de la siguiente manera:

SELECT
    person_id,
    full_name,
    age_in_years
FROM
    persons
ORDER BY
    age_in_years DESC;

Resultado:

La sintaxis para agregar columnas calculadas a una tabla

Para agregar una nueva columna calculada a una tabla existente, utiliza la siguiente sintaxis:

ALTER TABLE table_name
ADD column_name AS expression [PERSISTED];

En esta sintaxis:

• Primero, especifique el nombre de la tabla a la que deseas agregar la columna calculada.
• En segundo lugar, especifica el nombre de la columna calculada con la expresión que devuelve los valores de la columna.
• En tercer lugar, si la expresión es determinista y deseas almacenar físicamente los datos de la columna calculada, puedes utilizar la propiedad PERSISTED.

Ten en cuenta que puedes crear un índice en una columna calculada persistente para mejorar la velocidad de recuperación de datos de la columna calculada. Es una buena solución alternativa para índices basados en funciones de Oracle o índices en expresiones de PostgreSQL.

La sintaxis para definir columnas calculadas al crear una nueva tabla

Para definir una columna calculada cuando creas una tabla, utiliza la siguiente sintaxis:

CREATE TABLE table_name(
    ...,
    column_name AS expression [PERSISTED],
    ...
);

En este tutorial, aprendiste a usar las columnas calculadas de SQL Server para reutilizar la lógica de cálculo en varias consultas.