Especificar colunas computadas em uma tabela

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Uma coluna computada é uma coluna virtual que não está armazenada fisicamente na tabela, a menos que a coluna esteja marcada como PERSISTED. Uma expressão de coluna computada pode usar dados de outras colunas para calcular um valor para a coluna à qual pertence. Você pode especificar uma expressão para uma coluna computada no SQL Server usando o SSMS (SQL Server Management Studio) ou o T-SQL (Transact-SQL).

Limitações

  • Não é possível usar uma coluna computada como uma definição de restrição DEFAULT ou FOREIGN KEY ou com uma definição de restrição NOT NULL. Entretanto, se o valor da coluna computada for definido por uma expressão determinística e o tipo de dados do resultado for permitido em colunas de índice, uma coluna computada poderá ser usada como uma coluna de chave em um índice ou como parte de qualquer restrição PRIMARY KEY ou UNIQUE.

    Por exemplo, se a tabela tiver as colunas de inteiros a e b, uma coluna computada definida comoa + b poderá ser indexada, mas não a coluna computada a + DATEPART(dd, GETDATE()), pois o valor poderá ser alterado nas próximas chamadas.

  • Uma coluna computada não pode ser o destino de uma instrução INSERT ou UPDATE.

  • SET QUOTED_IDENTIFIER precisará ser ON ao criar ou alterar índices em colunas computadas ou exibições indexadas. Para obter mais informações, confira SET QUOTED_IDENTIFIER (Transact-SQL).

Permissões

Exige a permissão ALTER na tabela.

Usar o SQL Server Management Studio

Adicione uma nova coluna computada

  1. No Pesquisador de Objetos, expanda a tabela na qual você deseja adicionar a nova coluna computada. Clique com o botão direito do mouse em Colunas e selecione Nova Coluna.

  2. Insira o nome da coluna e aceite o tipo de dados padrão (nchar(10)). O Mecanismo de Banco de Dados determina o tipo de dados da coluna computada, aplicando as regras de precedência de tipos de dados às expressões especificadas na fórmula. Por exemplo, se a fórmula referenciar uma coluna de tipo money e uma coluna de tipo int, a coluna computada será do tipo money porque esse tipo de dados tem maior precedência. Para obter mais informações, confira Precedência de tipo de dados (Transact-SQL).

  3. Na guia Propriedades da Coluna , expanda a propriedade Especificação de Coluna Computada .

  4. Na propriedade filho (Fórmula) , insira a expressão para essa coluna na célula de grade à direita. Por exemplo, em uma coluna SalesTotal , a fórmula que você insere pode ser SubTotal+TaxAmt+Freight, que associa o valor nessas colunas a cada linha na tabela.

    Importante

    Quando uma fórmula combina duas expressões de tipos de dados diferentes, as regras de precedência do tipo de dados especificam que o tipo de dados com menor precedência é convertido no tipo de dados de maior precedência. Se a conversão não for uma conversão implícita com suporte, o erro Error validating the formula for column column_name. será retornado. Use a função CAST ou CONVERT para resolver o conflito de tipo de dados. Por exemplo, se uma coluna de tipo nvarchar é combinada com uma coluna de tipo int, o tipo inteiro deve ser convertido em nvarchar , conforme mostrado nesta fórmula ('Prod'+CONVERT(nvarchar(23),ProductID)). Para obter mais informações, veja CAST e CONVERT (Transact-SQL).

  5. Indique se os dados são persistentes escolhendo Sim ou Não na lista suspensa da propriedade filho Is Persisted.

  6. No menu Arquivo, selecione Salvar nome da tabela.

Adicionar uma definição de coluna computada em uma coluna existente

  1. No Pesquisador de Objetos, clique com o botão direito do mouse na tabela com a coluna que você deseja alterar e expanda a pasta Colunas .
  2. Clique com o botão direito do mouse na coluna para a qual você deseja especificar uma fórmula de coluna computada e selecione Excluir. Selecione OK.
  3. Adicione uma nova coluna e especifique a fórmula de coluna computada seguindo o procedimento anterior para adicionar uma nova coluna computada.

Usar o Transact-SQL

Adicionar uma coluna computada ao criar uma tabela

O exemplo a seguir cria uma tabela com uma coluna computada que multiplica o valor da coluna QtyAvailable pelo valor da coluna 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;

Adicionar uma nova coluna computada em uma tabela existente

O exemplo a seguir adiciona uma nova coluna à tabela criada no exemplo anterior.

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

Opcionalmente, adicione o argumento PERSISTED para armazenar fisicamente os valores computados na tabela:

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

Transformar uma coluna existente em coluna computada

O exemplo a seguir modifica a coluna adicionada no exemplo anterior.

ALTER TABLE dbo.Products DROP COLUMN RetailValue;
GO

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

Conteúdo relacionado