quarta-feira, 4 de maio de 2011

Utilizando Colunas Computadas no SQL Server

Colunas computadas são colunas cujo valor é calculado dinamicamente através de uma expressão que pode ou não  referenciar valores de outras colunas da mesma tabela. Assim, não é possível definir valores para estas colunas durante operações de Insert e Update.

Colunas computadas podem ser utilizadas normalmente em filtros, ordenações e agrupamentos. Entretanto, para utilizá-las como chave estrangeira (foreign keys) e aplicar índices, deve-se utilizar a propriedade PERSISTED, detalhada no final deste artigo.

Ao criar uma tabela com coluna computada, omite-se o tipo de dados dessas colunas e adicona-se a cláusula AS seguida da expressão que define o valor da coluna. Veja abaixo:

CREATE TABLE Tabela
(
      colunaA INT
    , colunaB INT
    , colunaC AS colunaA + colunaB
)

Para exemplificar, utilizaremos uma tabela que armazena informações sobre pessoas. As informações armazenadas serão:

Utilizando essas informações, criaremos duas colunas calculadas para armazenar:
  • idade
  • IMC - índice de massa corporal =  peso / altura²

O script abaixo cria a tabela conforme especificado acima:

CREATE TABLE Pessoa
(
      id INT IDENTITY(1,1) PRIMARY KEY
    , nome VARCHAR(50)
    , dataNascimento DATE
    , idade AS (DATEDIFF(DAY,dataNascimento,getdate())/365)
    , altura FLOAT
    , peso FLOAT
    , imc AS CAST ((peso / (altura * altura)) AS DECIMAl(4,1))
)

Para inserir registros, omite-se as colunas computadas. O script abaixo insere duas linhas em nossa tabela:

INSERT INTO Pessoa VALUES ('João','1947-07-22',1.87,82)
INSERT INTO Pessoa VALUES ('Ricardo','1973-03-08',1.65,77)

Vamos agora selecionar os valores inseridos utilizando o comando abaixo:

SELECT * From Pessoa

Veja o resultado:


Observe que o valor das colunas Idade e IMC foi preenchido automaticamente, de acordo com a expressão definida.

Cuidado!

O SQL Server não permite a inserção ou atualização de valores em colunas computadas, o comando abaixo tenta realizar esta operação inserindo o valor 7 para a coluna IMC:

INSERT INTO Pessoa (nome,dataNascimento,altura,peso,imc) VALUES ('Ricardo','1973-03-08',1.65,77,7)

O comando acima resulta na seguinte mensagem de erro:

Msg 271, Level 16, State 1, Line 1
The column "imc" cannot be modified because it is either a computed column or is the result of a UNION operator.

Além disso, procure utilizar validações na tabela (triggers e contraints) e também nas expressões (ISNULL(), COALESCE(), etc.) para evitar erros de cálculo ao calcular o valor de uma coluna computada. Se a expressão resultar em erro em alguma das linhas do retorno uma constulta, o processamento da consulta é imediatamente encerrado e o erro é lançado. Se inserirmos uma linha com valor 0 para a coluna altura em nossa tabela, causaremos uma divisão por zero. A inserção será realizada, mas ao consultar os dados da tabela, o seguinte erro é lançado:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

A Propriedade PERSISTED
O cálculo da expressão de uma coluna computada pode impactar no desempenho das consultas em uma tabela, principalmente se a expressão realizar operações complexas do ponto de vista computacional.

A proprierdade PERSISTED pode evitar esta perde de desempenho, pois as colunas computadas com esta propriedade armazenam fisicamente o valor no momento em que o registro é inserido ou alterado. Assim, não é necessário efetuar o cálculo novamente.

Mas lembre-se, para utilizar esta propriedade, a expressão que define o valor da coluna deve ser determinística.

10 comentários:

Alessandro H. Garbiati disse...

Muito interessante, desconhecia desta técnica, ganhou meu +

Anônimo disse...

Explicação bem didática. Parabéns.

Anônimo disse...

Certo mas e se eu tivesse uma tabela A com 2 campos e outra tabela B com um campo sendo calculado, como fazer?

Evandro Junqueira Ramos disse...

Desculpe, não entendi a sua pergunta, você poderia ser mais claro?

Se você pretende calcular o valor do campo B utilizando valores da tabela A no cálculo, o SQL Server não permitirá, pois o cálculo pode envolver apenas valores da própria linha combinados em expressões.

Anônimo disse...

Certo, valeu pela ajuda

Evandro Junqueira Ramos disse...

Você pode utilizar uma Trigger para resolver este problema.

A Trigger permitirá seleção em outra tabela e atualização do campo

Anônimo disse...

Bom, colocarei o problema para que outras pessoas possão ter como ajuda, já que no google não encontra respaldo sobre este problema.

Create table HORARIOS(
HorariosId int not null identity,
HorariosTotHoras int not null,
HorariosEntrada datetime not null,
HorariosAlmoco datetime not null,
HorariosRetorno datetime not null,
HorarioSaida datetime not null,
Constraint pk_hrs primary key(HorariosId),
Constraint fk_th foreign key (HorariosTotHoras) references TotHoras(TotHorasId))

Create table TotHoras(
TotHorasId int not null identity,

--Problema ocorre na linha abaixo.

TotHorasTrabalhada as (([Horarios].[HorariosAlmoco] - [Horarios].[HorariosEntrada])
+ ([Horarios].[HorariosSaida] - [Horarios].[HorariosRetorno]))

--A linha a baixo já aceita. (não dá para explicar o porque.)
TotHoraExtra as (TotHorasTrabalhada - 8),

Constraint pk_thrs primary key(TotHorasId)
)


Realmente o SQLSERVER ele não aceita este tipo de colunas calculadas?

Valeu por ajudar-me.

Bom me chamo Emerson, é que não conssegui fazer o bendito log in.

Se alguém poder ajudar-me?

Evandro Junqueira Ramos disse...

Olá Emerson,

Desculpe pela demora na resposta.

Neste caso, você precisará utilizar triggers ou rever a modelagem e a real necessidade deste campo.

Se precisar de maior ajuda, entre em contato, podemos discutir melhor este caso.

Caso não consiga efetuar o login para comentar, pode utilizar o formulário de contato (Contact me) visível à esquerda desta página.

Abs

Rodrigo disse...

Evandro, as colunas computadas existem fisicamente? Quero dizer, se preciso sempre de um mesmo cálculo, é melhor já ter a coluna computada ou fazer os cálculos dinamicamente no momento necessário do mesmo?

Evandro Junqueira Ramos disse...

Rodrigo, por padrão, as colunas computadas não são persistidas no banco de dados. Assim, seu valor é computado sempre que uma consulta acessar a coluna.

Entretanto, existe a possibilidade de persistir estas colunas no banco de dados, principalmente se houver a necessidade de criar índices que contenham colunas computadas (os índices só podem ser criados se estas forem persistidas). Como expliquei no artigo, o atributo PERSISTED define este comportamento.

Ainda respondendo a sua pergunta, existem casos em que não é possível armazenar o valor final do cálculo, principalmente os que envolvem a data atual. Uma coluna que armazene a idade de uma pessoa depende da data atual e seu resultado será alterado com o tempo.

Para cálculos complexos, deve-se avaliar a real necessidade da utilização de colunas computadas, pois realmente existe a possibilidade de queda de performance.

Espero ter ajudado!

Postar um comentário