Receta T-SQL No. 3-6: Forzar la Unicidad de una Columna con NULL

Índice

1. Introducción
2. Palabras Clave
3. Problmea
4. Solución
5. Discusión de la Solución
5.1 Índice único -unique index-
6. Práctica: Código T-SQL
7. Conclusiones
8. Literatura & Enlaces

1. Introducción

Esta nueva receta Transact-SQL enseña a utilizar un mecanismo para asegurar la unicidad de una columna que admita NULL. Se demuestra en primera instancia que un índice único -unique index- evita duplicados tanto de valores concretos para un tipo de dato específico como de valores desconocidos NULL. Esto es posible a través de la creación de un índice único nonclustered, el cual permite la unicidad de valores concretos y multiplicidad o asignación de NULL a múltiples registros para el mismo campo.

2. Palabras Clave

• Índice
• Índice único
• Multiplicidad
• Unicidad
• Nonclustered index

3. Problema

Permitir que una columna de una tabla pueda contener valores concretos únicos de un tipo de dato específico. Además que se permitan múltiples valores NULL.

4. Solución

Usar un índice no-agrupado o nonclustered para el campo que debe cumplir el requerimiento de unicidad para valores concretos y multiplicidad para valores no conocidos o NULL.

5. Discusión de la Solución

5.1 Índice único -unique index-

Un índice único (o unique index) es un tipo de índice que garantiza la unicidad o no duplicado de valores para un campo o columna llave ("Create Unique Indexes", 2015). Además, este tipo de índice puede involucrar varias columnas. Por ejemplo se puede crear un índice para un número de teléfono para un requerimiento de negocio que especifique que cada cliente debe tener uno o más números de teléfono distintos a cualquiera de los demás clientes.

Lo anterior también aplica para una combinación de columnas como PrimerNombre, SegundoNombre, Apellidos. En el índice único no puede existir más de un registro que tenga la misma combinación de primer nombre, segundo nombre y apellidos.

Otro propósito interesante de este clase de índice es garantizar la integridad de datos sobre la o las columnas que forman parte del índice. Además, como se manifiesta en "Create Unique Indexes" (2015), un índice único facilita información extra para el optimizador de consultas de SQL Server.

6. Práctica: Código T-SQL

Creación de tabla Producto:

CREATE TABLE Producto

(

IdProducto INT NOT NULL

CONSTRAINT PK_Producto PRIMARY KEY CLUSTERED,

NombreProducto NVARCHAR(50) NOT NULL,

NombreClave NVARCHAR(50)

);

A coninuación se ha de crear el índice único nonclustered:

CREATE UNIQUE INDEX UX_Producto_NombreClave ON Producto(NombreClave);

A partir de aquí se prueba con la inserción de registros en la misma tabla:

INSERT INTO Producto (IdProducto, NombreProducto, NombreClave)

VALUES (1, 'Producto #1', 'Adven');

INSERT INTO Producto (IdProducto, NombreProducto, NombreClave)

VALUES (2, 'Producto #2', 'Fomich');

INSERT INTO Producto (IdProducto, NombreProducto, NombreClave)

VALUES (3, 'Producto #3', NULL);

INSERT INTO Producto (IdProducto, NombreProducto, NombreClave)

VALUES (4, 'Producto #4', NULL);

Al intentar ejecutar esas sentencias de inserción de registros se genera un mensaje de error para la última:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 2601, Level 14, State 1, Line ...

Cannot insert duplicate key row in object 'dbo.Producto' with unique index 'UX_Producto_NombreClave'. The duplicate key value is ().

The statement has been terminated.

La causa del error se debe esencialmente a que el tipo de índice creado no permite la inserción de múltiples valores no conocidos NULL.

Para resolver esta limitación, SQL Server cuenta con la función de creación de índices filtrados; esto es, la especificación de una condición para un subconjunto determinados valores del dominio de un tipo de dato (Brimhall et al, 2015).

Proceso de la solución:

Eliminación del índice creado anteriormente:

DROP INDEX Producto.UX_Producto_NombreClave;

Creación del nuevo índice único filtrado:

CREATE UNIQUE INDEX UX_Producto_NombreClave ON Producto(NombreClave) WHERE NombreClave IS NOT NULL;

Nótese la especificación del filtro a través de WHERE NombreClave IS NOT NULL.: con esto se está indiciado que el campo NombreClave solo debe conservar la unicidad sobre valores distintos a NULL.

A continuación se han de insertar otros registros

INSERT INTO Producto (IdProducto, NombreProducto, NombreClave)

VALUES (4, 'Producto #4', NULL);

INSERT INTO Producto (IdProducto, NombreProducto, NombreClave)

VALUES (5, 'Producto #5', NULL);

Ahora ya es permitido la inserción de múltiples valores NULL:

(1 row(s) affected)

(1 row(s) affected)

Contenido de la tabla Producto:

Figura 1. Contenido de la tabla Producto.

7. Conclusiones

Se ha demostrado cómo a través de un índice único filtrado es posible establecer una condición sobre la unicidad para los valores de una columna o varias columnas que pertenezcan al índice. En otras palabras: este tipo de operación es de inmensa utilidad para el programador de bases de datos, pues le permitirá aplicar una condición de unicidad para un subconjunto de datos que pertenezcan a un índice único. La próxima receta T-SQL explica cómo forzar la integridad referencial sobre columnas que permiten valores NULL.

8. Literatura & Enlaces

Brimhall, J., Dye, D., Gennick, J., Roberts, A., Sheffield, W. (2012). SQL Server 2012 T-SQL Recipes - A Problem-Solucion Approach. United States: Apress.
Create Unique Indexes (2015, diciembre 4). Recuperado desde: https://msdn.microsoft.com/en-us/library/ms187019.aspx.
Create Nonclustered Indexes (2015, diciembre 4). Recuperado desde: https://msdn.microsoft.com/en-us/library/ms189280.aspx.

V

Receta T-SQL No. 3-5: Remoción de Valores en un Agregado

Índice

1. Introducción
2. Palabras Clave
3. Problema
4. Solución
5. Discusión de la Solución
5.1 La función NULLIF
5.2 Equivalencia a CASE
6. Práctica: Código T-SQL
7. Conclusiones
8. Literatura & Enlaces

1. Introducción

En esta receta T-SQL se estudia el proceso de remoción de valores en una función de agregación. Este proceso es importante para cálculos que requieran descartar o discriminar ciertos valores que cumplan una determinada condición. En particular que se descarten a aquellos valores o expresiones que generan como resultado NULL. Las funciones de agregación, como se demuestra, omiten este tipo de valor para efectuar el cálculo que corresponda: promedio, conteo, máximo, mínimo entre otros.

2. Palabras Clave

• Agregado
• Función de agregación
• NULL
• Varianza

3. Problema

Se requiere omitir valores en una función de agregado que cumpla con una determinada condición.

4. Solución

Transact-SQL cuenta con la función NULLIF para asignar NULL a una expresión que cumpla una determinada expresión.

5. Discusión de la Solución

5.1 La función NULLIF

La función NULLIF ("NULLIF (Transact-SQL)", 2015) retorna el valor no definido NULL en el caso que dos expresiones sean iguales.

Sintaxis: 

NULLIF (expresion, expresion)

Los argumentos de NULLIF, expresion, corresponden a cualquier expresión válida en T-SQL. Los valores de retorno posible son:

• Si las expresiones no son iguales, NULLIF retorna la primera expresión.
• Si las expresiones son iguales, NULLIF retorna NULL.

5.2 Equivalencia a CASE

NULLIF es semánticamente equivalente a CASE ("CASE Transact-SQL", 2015). Sin embargo, CASE, a nivel sintáctico, requiere de elementos adicionales para construir una expresión equivalente a NULLIF.

Ejemplo con NULLIF:

SELECT ProductID,

MakeFlag,

FinishedGoodsFlag,

NULLIF(MakeFlag, FinishedGoodsFlag) AS 'NULL sin son iguales'

FROM Production.Product

WHERE ProductID < 10;

Ejemplo con CASE:

SELECT ProductID,

MakeFlag,

FinishedGoodsFlag,

'NULL sin son iguales' =

CASE

WHEN MakeFlag = FinishedGoodsFlag THEN NULL

ELSE MakeFlag

END

FROM Production.Product

WHERE ProductID < 10;

6. Práctica: Código T-SQL

Se recurre al problema planteado por Brimhall et al (2012) para ejemplificar la utilidad de NULLIF:

Determinar la varianza de los retrasos en producción entre la fecha efectiva de inicio y la fecha de inicio programada de lotes de producción. 

En particular: 

• ¿Cuál es la varianza de todas las operaciones?
• ¿Cuál es la varianza de todas las operaciones donde la varianza no es cero?

(Para saber más acerca del concepto de varianza, se recomienda la lectura de Varianza y desviación estándar.)

Archivo R0305.sql [Enlace alternativo][Enlace alternativo]: 

En la línea 2 se calcula la varianza de todas las operaciones, considerando incluso los valores de la diferencia de las columnas ScheduledStartDate y ActualStartDate igual a cero; mientras que en la línea 3 se calcula la varianza sólo para las operaciones que tienen diferencia de fechas distinto de cero.

Figura 1. Varianza y varianza ajustada.

7. Conclusiones

Se demostró el uso de la función NULLIF; la cual es útil para retornar NULL para aquellas expresiones iguales. Se destacó la similitud de NULLIF y CASE: la sintaxis declarativa de NULLIF es más simple, sin embargo con CASE se puede construir expresiones de selección más complejas. En el ejemplo de la sección práctica se uso NULLIF para destacar aquellas operaciones que habían iniciado en distintos días. En la próximo T-SQL se estudiará cómo forzar la unicidad con NULL.

8. Literatura & Enlaces

Brimhall, J., Dye, D., Gennick, J., Roberts, A., Sheffield, W. (2012). SQL Server 2012 T-SQL Recipes - A Problem-Solucion Approach. United States: Apress.
NULLIF (Transact-SQL) (2015, diciembre 1). Recuperado desde: https://msdn.microsoft.com/en-us/library/ms177562.aspx.
CASE (Transact-SQL) (2015, diciembre 1). Recuperado desde: https://msdn.microsoft.com/en-us/library/ms181765.aspx.
Varianza y desviación estándar (2015, diciembre 1). Recuperado desde: http://www.disfrutalasmatematicas.com/datos/desviacion-estandar.html.

V

Receta T-SQL No. 3-4: Búsqueda de Valores Desconocidos -NULL- en un Tabla

Índice

1. Introducción
2. Palabras Clave
3. Problema
4. Solución
5. Discusión de la Solución
5.1 NULL
5.2 Operadores relacionales
5.3 Evaluación de expresiones aritméticas y lógicas
5.4 Los operadores IS NULL y IS NOT NULL
5.5 Ejemplos de uso
6. Práctica: Código T-SQL
7. Conclusiones
8. Literatura & Enlaces

1. Introducción

En esta nueva receta T-SQL se explora el mecanismo de búsqueda de valores desconocidos -o NULL- en una tabla. Así mismo, se estudia cómo el motor de base de datos lleva a cabo esta búsqueda. Se estudia, por otro lado, las diferencias entre la función ISNULL y los operadores IS NULL e IS NOT NULL. A través de un plan de ejecución se demuestra el modo en que SQL Server ejecuta una sentencia SQL con el propósito de distinguir el rendimiento entre la función ISNULL y los operadores IS NULL y IS NOT NULL. Algunos ejemplos prácticos demuestran esta forma de proceder del motor de base de datos y que el programador debe tener en cuenta a la hora de componer sus sentencias de búsqueda de valores NULL.

2. Palabras Clave

• Base de datos
• Lenguaje de programación
• NULL
• Plan de ejecución
• Sentencia
• T-SQL

3. Problema

Se requiere implementar un mecanismo óptimo para la búsqueda de valores desconocidos, es decir NULL, en una tabla de una base de datos.

4. Solución

T-SQL provee dos operadores útiles, además de óptimos, para la búsqueda de valores desconocidos. Se trata de los operadores unarios:

• IS NULL, y 
• IS NOT NULL

5. Discusión de la Solución

5.1 NULL

Vale insistir que NULL no es un valor distinto a cualquier valor de los tipos de dato disponibles en T-SQL. Sencillamente, NULL, es un valor desconocido o no-existente.

5.2 Operadores relacionales

Los operadores relacionales no deben ser usados para comparar o igualar un valor conocido -un entero, una fecha, un booleano, etc.- con el valor desconocido NULL. Es decir, que expresiones como 

• WHERE NombreColumna <> NULL, 
• WHERE NombreColumna = NULL

no tienen la lógica esperada de un lenguaje de programación como C#, Java, Python. En un lenguaje declarativo como T-SQL no mantiene la misma lógica de evaluación de expresiones booleanas.

5.3 Evaluación de expresiones aritméticas y lógicas

Para afianzar la diferencia NULL respecto a un valor conocido, se ha de considerar preguntas como:

• ¿Cuál es el resultado de evaluar NULL + 1? ¿NULL?
• ¿Qué se obtiene de la siguiente expresión aritmética NULL * 5? ¿NULL?
• ¿Qué se obtiene con NULL = 1? ¿NULL?
• ¿Y al comparar NULL <> 1? ¿NULL?

5.4 Los operadores IS NULL y IS NOT NULL

Los operadores IS NULL y IS NOT NULL ("IS [NOT] NULL", 2015) determinan si una expresión es NULL. La sintaxis de este operador es como sigue:

expresion IS [ NOT ] NULL

Sus argumentos comprende:

• expresion: un valor literal, variable, valor de columna, retorno de una función, o cualquier otra expresión T-SQL válida.
• NOT: invierte la expresión lógica con el operador lógico NO.

5.5 Ejemplos de uso

En este primer ejemplo se demuestra el uso incorrecto y correcto de NULL en la sentencia de control CASE WHEN:

DECLARE @valor INT = NULL;

SELECT CASE WHEN @valor = NULL THEN 1

    WHEN @valor <> NULL THEN 2

    WHEN @valor IS NULL THEN 3

    ELSE 4

END;

A la variable @valor se le asigna NULL. Luego, a través de la cláusula de control CASE WHEN se evalúa el valor de esta variable en las expresiones:

• @valor = NULL, 
• @valor <> NULL, y 
• @valor IS NULL

Solo la última expresión es valida; por lo tanto el valor que se escoge es 3.

Ahora, en un caso más concreto se quiere obtener el listado de 5 personas que no han suministrado su segundo nombre en el registro:

SELECT TOP 5

    FirstName AS 'Primer Nombre', LastName AS 'Apellido', MiddleName AS 'Segundo Nombre'

    FROM Person.Person

    WHERE MiddleName IS NULL;

Al ejecutar este código T-SQL se obtiene:

Figura 1. Uso operador IS NULL.

6. Práctica: Código T-SQL

Se aprovechará esta sección práctica para demostrar las diferencias de rendimiento al usar la función ISNULL y el operador IS NULL.

Problema: Obtener los datos -ID de candidato e ID de entidad- de un candidato que está asociado con una entidad.

Solución (usando ISNULL):

SET SHOWPLAN_TEXT ON;

GO


SELECT JobCandidateID AS 'ID Candidato', BusinessEntityID AS 'ID Entidad'
    FROM HumanResources.JobCandidate
    WHERE ISNULL(BusinessEntityID, 1) <> 1;
GO


SET SHOWPLAN_TEXT OFF;

El plan de ejecución resultante es:

  |--Index Scan(OBJECT:([AdventureWorks2012].[HumanResources].[JobCandidate].[IX_JobCandidate_BusinessEntityID]),  WHERE:(isnull([AdventureWorks2012].[HumanResources].[JobCandidate].[BusinessEntityID],(1))<>(1)))

Nótese que el plan de ejecución indica que se ha usado un escaneo -Scan-, es decir de verificar registro por registro el índice asociado a la tabla JobCandidate para realizar la búsqueda valores no NULL.

Solución (usando el operador IS NULL):

SET SHOWPLAN_TEXT ON;

GO

SELECT JobCandidateID AS 'ID Candidato', BusinessEntityID AS 'ID Entidad'

    FROM HumanResources.JobCandidate

    WHERE BusinessEntityID IS NOT NULL;

GO

SET SHOWPLAN_TEXT OFF;

El resultado del plan de ejecución es:

  |--Index Seek(OBJECT:([AdventureWorks2012].[HumanResources].[JobCandidate].[IX_JobCandidate_BusinessEntityID]), SEEK:([AdventureWorks2012].[HumanResources].[JobCandidate].[BusinessEntityID] IsNotNull) ORDERED FORWARD)

Aquí es donde el operador IS NULL destaca por rendimiento: se usa el índice como sistema de búsqueda eficiente para encontrar aquellos valores no NULL de la tabla JobCandidate.

7. Conclusiones

Se ha comprendido como en SQL Server se puede buscar valores NULL usando construcciones óptimas: los operadores IS NULL y IS NOT NULL. A través de ejemplos se demostró que el uso de funciones deteriora el desempeño de búsqueda, debido a que las funciones -ISNULL, por ejemplo- recurren al escaneo total de la tabla objetivo. La próxima receta T-SQL comprenderá cómo remover valores de un agregado.

8. Literatura & Enlaces

Brimhall, J., Dye, D., Gennick, J., Roberts, A., Sheffield, W. (2012). SQL Server 2012 T-SQL Recipes - A Problem-Solucion Approach. United States: Apress.
IS [NOT] NULL (Transact-SQL) (2015, noviembre 27). Recuperado desde: https://msdn.microsoft.com/es-es/library/ms188795(v=sql.120).aspx.

V