Especificar columnas calculadas en una tabla

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Una columna calculada es una columna virtual que no está almacenada físicamente en la tabla, a menos que la columna esté marcada con PERSISTED. Las expresiones de columnas calculadas pueden utilizar datos de otras columnas al calcular un valor para la columna a la que pertenecen. Puede especificar una expresión para una columna calculada en SQL Server mediante SQL Server Management Studio (SSMS) o Transact-SQL (T-SQL).

Limitaciones

  • Una columna calculada no puede utilizarse como definición de restricción DEFAULT o FOREIGN KEY ni como NOT NULL. No obstante, si el valor de columna calculada lo define una expresión determinista y se permite el tipo de datos del resultado en columnas de índice, se puede utilizar una columna calculada como columna de clave en un índice o como parte de cualquier restricción PRIMARY KEY o UNIQUE.

    Por ejemplo, si la tabla contiene las columnas de enteros a y b, una columna calculada definida como a + b puede estar indexada, pero la columna calculada definida como a + DATEPART(dd, GETDATE()) no puede indexarse porque el valor puede cambiar en las siguientes invocaciones.

  • Una columna calculada no puede ser el destino de una instrucción INSERT o UPDATE.

  • SET QUOTED_IDENTIFIER debe ser ON al crear o cambiar índices en columnas calculadas o vista indexadas. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

Permisos

Requiere el permiso ALTER en la tabla.

Uso de SQL Server Management Studio

Agregar una nueva columna calculada

  1. En el Explorador de objetos, expanda la tabla para la que desea agregar la nueva columna calculada. Haga clic con el botón derecho en Columnas y seleccione Nueva columna.

  2. Escriba el nombre de columna y acepte el tipo de datos predeterminado (nchar(10)). El motor de base de datos determina el tipo de datos de la columna calculada aplicando las reglas de prioridad de tipo de datos a las expresiones especificadas en la fórmula. Por ejemplo, si la fórmula hace referencia a una columna de tipo money y una columna de tipo int, la columna calculada será de tipo money porque ese tipo de datos tiene mayor prioridad. Para obtener más información, vea Prioridad de tipo de datos (Transact-SQL).

  3. En la pestaña Propiedades de columna , expanda la propiedad Especificación de columna calculada .

  4. En la propiedad secundaria (Fórmula) , escriba la expresión de esta columna en la celda de la cuadrícula situada a la derecha. Por ejemplo, en una columna SalesTotal , la fórmula que escribe puede ser SubTotal+TaxAmt+Freight, que suma el valor de estas columnas para cada fila de la tabla.

    Importante

    Cuando una fórmula combina dos expresiones de tipos de datos distintos, las reglas de prioridad de tipo de datos especifican que el tipo de datos con la prioridad menor se convierta al tipo de datos con la prioridad mayor. Si la conversión no es una conversión implícita admitida, se devuelve el error Error validating the formula for column column_name.. Use la función CAST o CONVERT para resolver el conflicto de tipos de datos. Por ejemplo, si una columna de tipo nvarchar se combina con una de tipo int, el tipo entero debe convertirse a nvarchar como se muestra en esta fórmula: ('Prod'+CONVERT(nvarchar(23),ProductID)). Para más información, vea CAST y CONVERT (Transact-SQL).

  5. Indique si los datos se van a conservar; para ello, elija Sí o No en el menú desplegable de la propiedad secundaria Es persistente.

  6. En el menú Archivo, seleccione Guardar nombre de tabla.

Agregar una definición de columna calculada a una columna existente

  1. En el Explorador de objetos, haga clic con el botón derecho en la tabla que contenga la columna que quiera cambiar y expanda la carpeta Columnas .
  2. Haga clic con el botón derecho en la columna para la que quiera especificar una fórmula de columna calculada y seleccione Eliminar. Seleccione Aceptar.
  3. Agregue una nueva columna y especifique la fórmula de columna calculada siguiendo el procedimiento anterior para agregar una nueva columna calculada.

Uso de Transact-SQL

Agregar una columna calculada al crear una tabla

En el ejemplo siguiente se crea una tabla con una columna calculada que multiplica el valor de la columna QtyAvailable tantas veces como indique el valor de la columna UnitPrice.

CREATE TABLE dbo.Products
   (
      ProductID int IDENTITY (1,1) NOT NULL
      , QtyAvailable smallint
      , UnitPrice money
      , InventoryValue AS QtyAvailable * UnitPrice
    );

-- Insert values into the table.
INSERT INTO dbo.Products (QtyAvailable, UnitPrice)
   VALUES (25, 2.00), (10, 1.5);

-- Display the rows in the table.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;

-- Update values in the table.
UPDATE dbo.Products 
SET UnitPrice = 2.5
WHERE ProductID = 1;

-- Display the rows in the table, and the new values for UnitPrice and InventoryValue.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;

Agregar una nueva columna calculada a una tabla existente

En el ejemplo siguiente se agrega una columna nueva a la tabla creada en el ejemplo anterior.

ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);

Si lo desea, agregue el argumento PERSISTED para almacenar físicamente los valores calculados en la tabla:

ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5) PERSISTED;

Cambiar una columna existente a una columna calculada

En el ejemplo siguiente se modifica la columna agregada en el ejemplo anterior.

ALTER TABLE dbo.Products DROP COLUMN RetailValue;
GO

ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);
GO

Contenido relacionado