Solução:
Para esses cenários onde os valores calculados são necessárias ou valores são gerados através de manipulação em outras colunas, temos um poderoso recurso previsto no SQL Server. Este recurso é o de "Colunas computadas".
Uma coluna computada é calculada a partir de uma expressão que pode usar outra coluna ou colunas na mesma tabela. Funções, variáveis, constantes, nomes de colunas não computadas ou qualquer combinação de todos estes podem ser usados junto com as operadoras para criar uma coluna computada.
Nesta dica, vamos percorrer um exemplo de implementação de uma coluna computada.
Vamos testar scripts em SQL Server 2005 e SQL Server 2008. No script a seguir será criada uma tabela chamada CCtest no banco de dados AdventureWorks com três colunas [empNumb], [DOBirth], [DORetirement].
Anexando o AdventureWorks baixado da Microsoft
USE [master]
GO
CREATE DATABASE [AdventureWorks2012] ON
( FILENAME = N'D:\MSSQL\Data\AdventureWorks2012_Data.mdf' ),
( FILENAME = N'D:\MSSQL\Data\AdventureWorks2012_log.ldf' )
FOR ATTACH
GO
USE [AdventureWorks2012]
GO
CREATE TABLE [dbo].[CCtest]
(
[empNumb] [int] NULL,
[DOBirth] [datetime] NULL,
[DORetirement] AS (dateadd(year,(60),[DOBirth])-(1)) PERSISTED
)
GO
Existe a possibilidade de criar a mesma tabela utilizando o SSMS, mas é muito bom manter em mente os códigos. Vamos prosseguir desta forma.
Agora temos o nosso CCtest tabela com uma coluna computada. Da mesma forma, podemos adicionar uma coluna computada a qualquer tabela existente usando o "ALTER TABLE" comando ou abrir a tabela no modo de design usando SSMS e fazer as alterações.
Vamos inserir alguns dados e executar uma consulta para testar a funcionalidade da coluna computada.
USE AdventureWorks2012
GO
INSERT INTO CCTest (empNumb, DOBirth)
SELECT 30, '1985-12-13' UNION ALL
SELECT 25, '1980-11-18 ' UNION ALL
SELECT 21, '1978-01-19 ' UNION ALL
SELECT 7, '1985-12-13 ' UNION ALL
SELECT 5, '1975-07-23 '
GO
SELECT * FROM dbo.CCTest
GO
Para verificar se a coluna computada será atualizado para quaisquer atualizações, vamos atualizar [DOBirth] para [empNumb] 25.
UPDATE CCtest
SET DOBirth = '1960-03-25'
WHERE empnumb = 25
GO
SELECT * FROM dbo.CCTest
WHERE Empnumb = 25
GO
Persistência (PERSISTED)
Você deve ter notado que nós também usamos a propriedade "persisted" para a nossa coluna computada. Esta propriedade para colunas computadas foi introduzido no SQL Server 2005 e em diante. É importante para qualquer coluna computada, pois muitas características adicionais dependem dele. Para ser capaz de fazer uma coluna computada como persistentes que tem que ser determinista.
Aqui estão algumas regras:
- Se a propriedade PERSISTED não foi configurada então coluna calculada será apenas uma coluna virtual. Não há dados para esta coluna serão armazenados no disco e os valores serão calculados a cada momento que for referenciado em um script. Se esta propriedade é definida como ativa então os dados de coluna computada serão armazenados no disco, aumenta-se o espaço em disco, porém diminui a necessidade de processamento do servidor.
- Qualquer atualização na coluna referenciada serão sincronizadas automaticamente na coluna computada se é ela persistente.
- Junto com algumas outras condições em PERSISTED são necessárias para criar um índice na coluna computada.
Nulidade(Nullibility)
Nullibility para um valor de coluna calculada será determinada pelo Engine do SQL em si. O resultado de uma coluna NOT NULL referenciado pode ser NULL em determinadas condições para evitar possíveis erros de excessão ou sobrecargas. É possível proporcionar um valor alternativo para NULL utilizando o ISNULL (check_expression, constante), se necessário.
Algumas limitações
- Para o SQL Server 2000 você não pode criar uma coluna computada persistente.
- Você não pode fazer referência a colunas de outras tabelas para uma expressão de coluna computada.
- Você não pode inserir ou atualizar declarações em colunas computadas.
- Se você estiver combinando operadores de dois tipos diferentes de dados em sua expressão então o operador de menor precedência será convertido para o de maior precedência. Se a conversão implícita não for possível, então erro será gerado.
- Uma subconsulta não pode ser usado como uma expressão para criar uma coluna computada.
- As colunas computadas podem ser utilizadas em SELECT, WHERE ou ORDER BY e como expressões regulares, mas para usar uma coluna computada como chave CHECK, estrangeiros ou não restrições NULL você tem que configurá-lo para PERSISTED.
- Para usar uma coluna computada como PRIMARY ou UNIQUE KEYdeve ser definido por uma expressão determinística e tipo de dados de expressão de coluna computada deve ser indexável.
Próximos Passos
- Durante o planejamento para qualquer coluna computada, por favor, tenha em mente que, apesar de uma coluna computada persistente irá reduzir a sobrecarga para os cálculos em tempo de execução ele vai consumir mais espaço em disco.
- Para obter nulidade de qualquer coluna computada em uma tabela, use a função COLUMNPROPERTY com a propriedade AllowsNull.
- Criando índices em colunas computadas requer certas condições para ser cumprida. Para mais detalhes sobre essas condições visite BOL (Books On Line)
Como sempre, não quero esgotar o assunto aqui, mas comentários e sugestões são bem vindas.
Até o próximo post!
Nenhum comentário:
Postar um comentário