Introdução
Colunas Calculadas são apenas colunas virtuais cujo conteúdo é o resultado de uma expressão. Normalmente, elas são usadas para armazenar dados com base no resto das colunas da tabela. A expressão pode conter outras colunas não computadas da tabela, constantes, operadores e funções, mas não é possível especificar uma consulta como uma expressão para uma coluna computada.
Como são colunas “virtuais”, não são armazenadas em disco como o resto da tabela. Na verdade, elas não são armazenadas, mas calculadas toda vez que a coluna é acessada em uma consulta. Como você verá, você pode forçar o SQL Server a armazenar (“persistir”) a coluna na tabela com algumas restrições.
A melhor maneira de entender como funcionam as colunas computadas é usando amostras. No final você encontrará um arquivo contendo todos os scripts usados no artigo, e mostraremos alguns deles no texto para ilustrar as explicações. Para começar, vamos criar duas tabelas: a primeira para guardar a informação das facturas e a outra com as linhas de detalhe dessas facturas. Você também pode encontrar algumas inserções no arquivo de script para criar dados de exemplo.
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));
A maneira de criar uma coluna computada é a mesma que você criaria outras colunas em uma tabela, com uma instrução CREATE TABLE ou ALTER TABLE. Para uma coluna computada, substituímos o tipo de dados da coluna pela expressão que será usada para obter o conteúdo da coluna. A sintaxe será o nome da coluna, seguido pela palavra-chave “as”, e depois a expressão. Vamos criar uma coluna computada na tabela detail_line para armazenar o valor total da linha, que calculamos multiplicando unit_price e quantity.
ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity;
Check for a Computed Column
Há várias maneiras de confirmar que uma coluna é realmente uma coluna computada. Uma delas é usando a função columnproperty() especificando a propriedade “IsComputed”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsComputed')
Outra maneira de obter informações de colunas computadas é através da visão do sistema, sys.computed_columns. Esta vista é uma extensão da vista sys.columns. Isto significa que sys.computed_columns herda todas as colunas da visão sys.columns e também adiciona outras que são específicas para este tipo de coluna. Ao longo do artigo veremos algumas das colunas nesta view, pois vemos diferentes características das colunas computadas. Por enquanto, é suficiente saber que esta view mostra apenas colunas computadas e tem uma coluna, chamada is_computed, que informa se a coluna está computada ou não. Obviamente, todos os registros desta view terão um nesta coluna.
SELECT name, is_computed FROM sys.computed_columns;
Como o conteúdo da coluna é calculado toda vez que a coluna é referenciada em uma consulta, o conteúdo é sempre atualizado. Qualquer alteração nas colunas que são incluídas na expressão, é automaticamente refletida no valor da coluna. Podemos ver isso alterando a quantidade em um registro na tabela detail_lines da amostra e verificar o resultado.
UPDATE detail_lines SET quantity = 4 WHERE product = 'Cup'SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE product = 'Cup'
A Step Forward
O que temos visto até agora com colunas calculadas é muito básico, uma vez que envolve apenas a realização de cálculos com outras colunas. No entanto, a expressão das colunas calculadas pode conter funções, tanto funções padrão T-SQL como funções definidas pelo usuário (UDF). Desta forma, é possível ampliar muito mais a funcionalidade destas colunas.
Vejamos um exemplo disto. Vamos adicionar uma coluna computada à tabela de faturas que calcula o valor total da fatura. Para fazer isto, temos de obter o número da factura e consultar a tabela de linhas_de_detalhes para somar o valor total de cada registo com esse id da factura. A melhor maneira de fazer isso é usar uma função que recebe o id da fatura como um parâmetro e retorna a soma. Depois disso, temos de criar a coluna que utiliza esta função.
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 verificar se esta coluna está a funcionar correctamente adicionando um novo registo na tabela, detail_lines, pelo que o total_bill deve mudar.
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
Alterando a Coluna
Pode haver situações em que tenha de modificar uma coluna calculada. Infelizmente, isto não é possível. Para fazer essa alteração, é necessário apagar a coluna e recriá-la com a nova expressão.
No caso da coluna computada utilizar uma função externa, não nos será permitido modificar esta função. Se tentarmos, recebemos um erro indicando que esta função está ligada à tabela. Para alterar a função, é necessário apagar a coluna, realizar a modificação da função e, finalmente, recriar a coluna com a nova versão da função.
Podemos obter a definição da coluna na coluna “definição” a partir da visão sys.computed_columns.
SEECT name, definition FROM sys.computed_columns
Storing a Computed Column
Como mencionamos anteriormente, estas colunas são “virtuais”, portanto não são armazenadas fisicamente na tabela. No entanto, existe a possibilidade de forçar o cálculo a ser armazenado fisicamente na tabela, que é chamada de “persistente” na coluna. Isto pode melhorar a performance com as instruções SELECT, pois isto evita ter que executar o cálculo da coluna toda vez que ela é referenciada.
Além disso, para persistir a coluna, a expressão utilizada para criar a coluna tem que ser “determinista”. Como podemos ver no site da Microsoft, “funções determinísticas retornam sempre o mesmo resultado sempre que são chamadas com um conjunto específico de valores de entrada e dado o mesmo estado da base de dados”. (https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-2017). Se quisermos saber se o SQL Server considera a expressão de uma coluna computada como determinística ou não, podemos usar a função columnproperty() com a propriedade “IsDeterministic”.
SELECT COLUMNPROPERTY(OBJECT_ID('dbo.detail_lines'),'total_amount','IsDeterministic')SELECT COLUMNPROPERTY(OBJECT_ID('dbo.invoices'),'total_invoice','IsDeterministic')
Se a definição da coluna é uma função definida pelo usuário, também é possível verificar se essa função em si é determinística ou não. Para isso, é necessário usar a função objectproperty() com a propriedade IsDeterministic.
SELECT OBJCETPROPERTY(OBJECT_ID('dbo.fn_total_invoice'),'IsDeterministic')
Como se pode ver pelas consultas, a coluna do primeiro exemplo, na qual calculamos o preço total do detalhe, é considerada determinística. Entretanto, a função que calcula o preço total da conta é considerada não-determinística. Desta forma, apenas a coluna preço_total da tabela de_tabela_de_detalhes pode ser armazenada na tabela.
ALTER TABLE detail_lines DROP COLUMN total_amount;ALTER TABLE detail_lines ADD total_amount AS unit_price * quantity PERSISTED;
Novamente na visão sys.computed_columns pode ver o campo is_persisted, que indicará se a coluna persiste ou não na tabela.
Índices com colunas computadas
É possível utilizar colunas computadas em índices, embora estes devam satisfazer vários requisitos:
- Titularidade: Todas as funções que são utilizadas na definição da coluna computada devem ser propriedade do mesmo usuário da tabela.
- Determinismo: A coluna computada deve ser determinista. Além disso, se a coluna contém expressões CLR, além de ser determinística, a coluna deve ser persistente.
- Accuracy: A expressão da coluna calculada tem de ser precisa. Isto implica que ela não pode ser do tipo de dados “float” ou “real”. Nem pode usar este tipo de dados na sua definição. Esta característica pode ser verificada com a função columnproperty() especificando a propriedade IsPrecise.
- Tipo de dados: A coluna computada não pode ser dos tipos texto, texto ou imagem. Também, se a expressão contém imagem, ntext, texto, varchar (max), nvarchar (max), varbinary (max), ou tipos de dados xml, ela só pode ser usada se o tipo de dados resultante da expressão for permitido em um índice.
Além destas considerações, as conexões usadas para criar a coluna e a usada para criar o índice devem ter certas configurações para realizar estas ações.
A conexão para criar a coluna computada deve ter a opção ANSI_NULLS ativa. Isto pode ser verificado com a função columnproperty(), especificando a propriedade IsAnsiNullsOn.
A conexão para criar o índice é, assim como as conexões para realizar a inserção, atualização e exclusão de registros que influenciam o índice devem ter as opções ANSI_NULLS, ANSI_PADDDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULLL_YIELDS_NULLL, QUOTED_IDENTIFIER ativa. Adicionalmente, a opção NUMERIC_ROUNDABORT deve ser desativada.
Last Considerations
Para finalizar, vamos rever alguns aspectos adicionais que é necessário conhecer para o uso correto das colunas computadas.
Obviamente, as colunas computadas não podem ser atualizadas, nem incluídas na lista de valores de uma ação INSERT. Embora as colunas computadas possam fazer parte da lista de resultados de uma instrução selecionada, elas também podem ser usadas nas cláusulas WHERE, ORDER BY, ou em todas aquelas em que uma expressão pode ser colocada.
SELECT product, unit_price, quantity, total_amount FROM detail_lines WHERE total_amount > 10 ORDER BY total_amount
Apesar do acima exposto, uma coluna computada não pode ser usada na definição da restrição DEFAULT ou FOREIGN KEY. Nem pode ser com uma definição de restrição NOT NULL.
Por outro lado, colunas computadas podem ser usadas como parte das restrições PRIMARY KEY ou UNIQUE. Para isso, a definição de coluna computada deve ser uma expressão determinística.
Conclusão
O uso de colunas computadas pode ser muito útil em algumas situações. Você tem que estudar cuidadosamente onde usá-las, devido às restrições que elas têm, especialmente para criar índices e persistir nelas. Isto é apenas o começo. Sinta-se livre para experimentar coisas novas e experimentar com colunas computadas para encontrar novas possibilidades.