Por: Atif Shehzad | Actualizado: 2011-08-29 | Comentarios (8) | Relacionado: Más > T-SQL
Seminario web gratuito de MSSQLTips: Mejores prácticas de desarrollo para SQL Server
Asista a este seminario web para aprender sobre las mejores prácticas de desarrollo para SQL Server. Andy Warren compartirá sus muchos años de experiencia para dar algunos consejos sobre lo que ha funcionado mejor para él y cómo se puede utilizar algunos de estos conocimientos.
Problema
He utilizado columnas computadas en escenarios simples y estos están trabajando bien. Sin embargo, en algunos casos nos encontramos con limitaciones al implementar la lógica de negocio a través de columnas computadas. Por ejemplo, se requiere tener diferentes valores basados en una expresión separada para determinar los valores computados. Además, existe la posibilidad de que se produzca un error de división por cero que hay que evitar. También es necesario acceder a columnas fuera de la tabla de columnas calculadas para utilizarlas en la expresión de la columna calculada. En este consejo vemos cómo hacer que las columnas computadas sean más flexibles.
Solución
Las columnas computadas pueden añadir una gran flexibilidad en el diseño de la base de datos. Es posible aplicar condicionalmente la expresión computada, manejando el error dividido por cero y accediendo a cualquier columna fuera de la tabla de la columna computada. Esto hace que la columna computada sea más flexible y práctica para el diseño de su base de datos. La siguiente es una lista de algunos escenarios que vamos a manejar en este consejo.
- Computación condicional de valores
- Error de división por cero
- Acceso a una columna fuera de la tabla de la columna computada
Utilizaremos técnicas simples de T-SQL para resolver los problemas mencionados. Esto demostrará la flexibilidad y la capacidad de las columnas computadas para manejar cualquier problema de este tipo.
Computación condicional de valores
Tenemos una tabla simple con algunas columnas relacionadas con los empleados. También hay una columna computada que calcula la jubilación a los 60 años. Tenemos un nuevo requisito para establecer el límite de edad a 65 años para los gerentes, pero mantenerlo en 60 para todos los demás empleados.
Podemos lograr esto usando una fórmula condicional utilizando una sentencia CASE como se muestra en el siguiente código. Para la columna DORetirement si la designación es «Manager» entonces la jubilación será de 65 sino será de 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
Si ejecuta el código anterior debería obtener una salida como la siguiente. Utilizando una sentencia CASE, podemos definir expresiones separadas en función de los criterios proporcionados.
Error de división por cero
En la expresión computacional, si estamos dividiendo puede haber una posibilidad de tener un cero en el denominador. En tales casos corremos el riesgo de tener un error debido a una división por cero.
Considere un escenario donde tenemos una columna computada con la fórmula AS (numerador/denominador) donde las columnas numerador y denominador se utilizan para la columna computada . En este caso se encontraría un error de división por cero cada vez que la expresión de la columna computada se calcula con un cero en la columna del denominador.
Podemos evitar este error sustituyendo el cero del denominador por un valor NULL utilizando la función NULLIF como se muestra a continuación. El cuarto registro que insertamos en la tabla provocaría un problema de división por cero, pero la función NULLIF lo convierte en un 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
Como no se puede dividir por NULL el valor devuelto es NULL para esta columna para este único registro.
Por defecto una columna computada permitirá valores NULL. Sin embargo, puede especificar explícitamente NOT NULL sólo con columnas computadas persistentes.
Acceso a una columna fuera de la tabla de columnas computadas
Una columna computada no puede acceder directamente a ninguna columna fuera de su tabla. Esta limitación puede ser superada mediante el uso de una función definida por el usuario. Una función definida por el usuario puede utilizarse en la expresión para acceder a cualquier columna fuera de la tabla de columnas computadas.
En el script que se muestra a continuación, se crea una UDF para calcular el saldo de vacaciones del empleado y mostrar cuántos días restantes de vacaciones tiene un empleado más allá del máximo de 20 días. Estos datos provienen de una tabla secundaria llamada 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
Así que a continuación podemos ver el número de días restantes disponibles para cada empleado.
Hay algunas consideraciones relacionadas con los UDFs cuando se va a utilizar una columna computada en un índice. Puede leer más sobre esto en este consejo: Cómo crear índices en columnas computadas en SQL Server.
Siguientes Pasos
Los problemas mencionados y sus soluciones funcionan de la misma manera para columnas computadas persistentes o no persistentes. Utilizar la flexibilidad disponible de las columnas computadas puede mejorar el diseño de su base de datos. Las columnas computadas son una buena opción para implementar la lógica de negocio a través de expresiones.
- Haga clic aquí para leer el consejo sobre los fundamentos del trabajo con columnas computadas
- Haga clic aquí para leer el consejo sobre la creación de índices en columnas computadas
Última actualización: 2011-08-29
Acerca del autor
Atif Shehzad es un apasionado DBA de SQL Server, revisor técnico y autor de artículos.Ver todos mis consejos
- Más consejos para desarrolladores de bases de datos…