Por: Atif Shehzad | Actualizado: 2011-08-29 | Comentários (8) | Related: Mais > T-SQL
Webinar de Dicas MSSQLTips gratuitas: Melhores Práticas de Desenvolvimento para SQL Server
Apresente este webinar para aprender sobre as melhores práticas de desenvolvimento para SQL Server. Andy Warren vai compartilhar seus muitos anos de experiência para dar algumas dicas sobre o que tem funcionado melhor para ele e como você pode utilizar alguns desses conhecimentos.
Problema
Eu tenho usado colunas computadas em cenários simples e estas estão funcionando bem. No entanto, em alguns casos estamos enfrentando limitações enquanto implementamos a lógica de negócios através de colunas computadas. Por exemplo, somos obrigados a ter valores diferentes baseados em uma expressão separada para determinar os valores computados. Além disso, talvez haja a chance de uma divisão por erro zero que precisa ser evitada. Também somos obrigados a acessar colunas fora da tabela de colunas computadas para uso na expressão da coluna computada. Nesta dica vamos ver como tornar as colunas computadas mais flexíveis.
Solução
Colunas computadas podem adicionar grande flexibilidade no design da base de dados. É possível aplicar condicionalmente a expressão computacional, manipulando dividida por erro zero e acessando qualquer coluna fora da tabela da coluna computada. Isto tornaria a coluna computada mais flexível e útil para o design da sua base de dados. A seguir, uma lista de alguns cenários que vamos tratar nesta dica.
- Cálculo condicional de valores
- Dividido por erro zero
- Acesso a uma coluna fora da tabela de colunas computadas
Usaremos técnicas simples de T-SQL para resolver os problemas acima mencionados. Isto demonstrará a flexibilidade e capacidade das colunas computadas para lidar com tais problemas.
Computação condicional de valores
Temos uma tabela simples com algumas colunas relacionadas aos empregados. Há também uma coluna computada que calcula a aposentadoria aos 60 anos de idade. Temos um novo requisito para definir o limite de idade de 65 anos para os gestores, mas mantê-lo nos 60 anos para todos os outros empregados.
Podemos fazer isto usando uma fórmula condicional usando uma declaração CASE, como mostrado no código a seguir. Para a coluna DORetirement se a designação for “Manager” então a aposentadoria será de 65 anos ou 60,
-- Script# 1: Computed column with conditional formula-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE .GO CREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, DATETIME NOT NULL, AS CASE WHEN designation = 'Manager' THEN (DATEADD(YEAR,(65),)) ELSE (DATEADD(YEAR,(60),)) END)GO --Insert sample data INSERT INTO CCTest (empNumb, Designation, DOBirth) SELECT 84, 'DBA', '1985-12-13' UNION ALLSELECT 85, 'DBA', '1980-11-18' UNION ALLSELECT 86, 'Manager', '1978-01-19' UNION ALLSELECT 88, 'Manager', '1985-12-13' UNION ALLSELECT 90, 'Developer', '1975-07-23' GO -- Check the required functionality in resultSELECT Designation, datediff(yy,dobirth,doretirement ) AgeLimit, DOBirth, DORetirement FROM CCTestGO
Se você executar o código acima, você deve obter output como o seguinte. Usando uma declaração CASE, podemos definir expressões separadas, dependendo do critério fornecido.
Dividido por Erro Zero
Em expressão computacional, se estamos dividindo então pode haver uma chance de ter um zero no denominador. Nesses casos corremos o risco de ter um erro devido a uma divisão por zero.
Cenário onde temos uma coluna computada com a fórmula AS (numerador/denominador) onde as colunas numerador e denominador são usadas para a coluna computada . Neste caso, uma divisão por erro zero seria encontrada sempre que a expressão da coluna computada fosse calculada com um zero na coluna do denominador.
Podemos evitar este erro substituindo o zero no denominador por um valor NULLIF, usando a função NULLIF, como mostrado abaixo. O quarto registro que inserimos na tabela causaria uma divisão por emissão de zero, mas a função NULLIF converte isto para um valor NULL.
-- Script# 2: Avoiding divided by zero error-- Use sample databaseUSE GO -- Create Table with computed columnIF OBJECT_ID('CCtest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( int NOT NULL, int NOT NULL, AS (Numerator/NULLIF(Denominator,0)) )GO--Insert sample dataINSERT INTO CCTest (Numerator, Denominator) SELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 3 UNION ALLSELECT 88, 0 UNION ALLSELECT 90, 15GO-- Check the resultSELECT * from CCTestGO
Desde que não se possa dividir por NULL o valor retornado é NULL para esta coluna para este único registo.
Por defeito uma coluna calculada permitirá valores NULL. No entanto, você pode especificar explicitamente NOT NULL apenas com colunas computadorizadas persistentes.
Acessando uma coluna fora da tabela de colunas computadas
Uma coluna computada não pode acessar diretamente qualquer coluna fora de sua tabela. Esta limitação pode ser superada usando uma Função Definida pelo Usuário. Uma UDF pode ser usada na expressão para acessar qualquer coluna fora da tabela de colunas computadas.
No script abaixo, um UDF é criado para calcular o saldo de férias do empregado para mostrar quantos dias restantes de férias um empregado tem além do máximo de 20 dias. Estes dados vêm de uma tabela secundária chamada LeaveBalance.
--Script # 3: Use UDF to access column in other table-- Use sample databaseUSE GO -- Create Table to reference in UDFIF OBJECT_ID('LeaveBalance', 'U') IS NOT NULL DROP TABLE LeaveBalanceGOCREATE TABLE . ( INT NOT NULL, TINYINT NOT NULL, )GO--Insert sample dataINSERT INTO LeaveBalanceSELECT 840, 12 UNION ALLSELECT 805, 6 UNION ALLSELECT 846, 13 UNION ALLSELECT 88, 7 UNION ALLSELECT 90, 15GO-- Create UDF to get leave balanceIF OBJECT_ID('UDF_GetLeaveBalance', 'FN') IS NOT NULL DROP FUNCTION UDF_GetLeaveBalanceGO-- Create UDF to use in computed columnCREATE FUNCTION UDF_GetLeaveBalance (@EmpNumb int)RETURNS TINYINTASBEGIN DECLARE @LeaveBalance TINYINT SELECT @LeaveBalance = (20 - LeavesAvailed) FROM LeaveBalance WHERE EmpNumb = @empnumb RETURN @LeaveBalanceENDGO-- Create Table to use computed columnIF OBJECT_ID('CCTest', 'U') IS NOT NULL DROP TABLE CCtestGOCREATE TABLE . ( INT NOT NULL, VARCHAR(50) NOT NULL, AS (.UDF_GetLeaveBalance(EmpNumb)) )GO--Insert sample dataINSERT INTO CCTest (EmpNumb, Designation) SELECT 840, 'DBA' UNION ALLSELECT 805, 'DBA' UNION ALLSELECT 846, 'Manager' UNION ALLSELECT 88, 'Manager' UNION ALLSELECT 90, 'Developer' GO-- Check the resultSELECT * from CCTestGO
Então abaixo podemos ver o número de dias restantes disponíveis para cada empregado.
Há algumas considerações relacionadas aos UDFs quando uma coluna computada é para ser usada em um índice. Você pode ler mais sobre isso nesta dica: Como criar índices em colunas computadas no SQL Server.
Passos seguintes
Os problemas acima mencionados e suas soluções funcionam da mesma forma para colunas computadas persistentes ou não persistentes. A utilização da flexibilidade disponível das colunas computorizadas pode melhorar o design da sua base de dados. As colunas computadas são uma boa opção para usar para implementar lógica de negócio através de expressões.
- Clique aqui para ler dicas sobre o básico do trabalho com colunas computadas
- Clique aqui para ler dicas sobre como criar índices em colunas computadas
Última Atualização: 2011-08-29
>
>
Sobre o autor
Atif Shehzad é um apaixonado DBA SQL Server, revisor técnico e autor do artigo.Ver todas as minhas dicas
- Mais Dicas para Desenvolvedores de Banco de Dados…