Especificar columnas calculadas en una tabla

Una columna calculada es una columna virtual que no se almacena físicamente en la tabla, a menos que la columna esté marcada como 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 2014 mediante SQL Server Management Studio o Transact-SQL.

En este tema

• Antes de comenzar:

  limitaciones y restricciones

  Seguridad

• Para especificar una columna calculada mediante:

  SQL Server Management Studio

  Transact-SQL

Antes de empezar

Limitaciones y restricciones

• Una columna calculada no se puede usar como definición de restricción DEFAULT o FOREIGN KEY o con una definición de restricción NOT NULL. Sin embargo, si el valor de la columna calculada se define mediante una expresión determinista y el tipo de datos del resultado se permite en columnas de índice, se puede usar 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 tiene columnas enteras a y b, la columna calculada a + b se puede indexar, pero la columna calculada a + DATEPART(dd, GETDATE()) no se puede indexar, ya que el valor puede cambiar en invocaciones posteriores.

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

Seguridad

Permisos

Requiere el permiso ALTER en la tabla.

Uso de SQL Server Management Studio

Para 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 la prioridad más alta. 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 del tipo de datos. Por ejemplo, si una columna de tipo nvarchar se combina con una columna de tipo int, el tipo entero debe convertirse en 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 conservan eligiendo Sí o No en la lista desplegable de la propiedad secundaria Is Persisted .

6. En el menú Archivo, haga clic en Guardarnombre de tabla.

Para 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 desea especificar una fórmula de columna calculada y haga clic en Eliminar. Haz clic en 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

Para agregar una columna calculada al crear una tabla

1. Conéctese al motor de base de datos.

2. En la barra Estándar, haga clic en Nueva consulta.

3. Copie y pegue el ejemplo siguiente en la ventana de consulta y, a continuación, haga clic en Ejecutar. En el ejemplo se crea una tabla con una columna calculada que multiplica el valor de la columna QtyAvailable por 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;  
   
   

Para agregar una nueva columna calculada a una tabla existente

1. Conéctese al motor de base de datos.

2. En la barra Estándar, haga clic en Nueva consulta.

3. Copie y pegue el ejemplo siguiente en la ventana de consulta y, a continuación, haga clic en Ejecutar. 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.35);  
   
   

Para cambiar una columna existente a una columna calculada

1. Conéctese al motor de base de datos.

2. En la barra Estándar, haga clic en Nueva consulta.

3. Para cambiar una columna existente a una columna calculada, debe quitar y volver a crear la columna calculada. Copie y pegue el ejemplo siguiente en la ventana de consulta y, a continuación, haga clic en Ejecutar. 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);  
   
   

   Para obtener más información, consulte ALTER TABLE (Transact-SQL)