Módulo 12

1. Módulo 12 Transact-SQL Avanzado

2. TEMARIO • Common Table Expression (CTE) • PIVOT y UNPIVOT • APPLY • CROSS APPLY • OUTER APPLY • Referencia integral Declarativa (DRI) • Manejo de Errores • OUTPUT • READPAST • WAITFOR • DLL Triggers • Notificación de Eventos

3. Common Table Expression (CTE) • Una tabla temporal nominada, la cual deriva de un query simple y definido dentro del alcance de la ejecución del comando SELECT, INSERT, UPDATE, o DELETE

4. Common Table Expression (CTE) • Ejemplo:

5. Common Table Expression (CTE) • Tipos de CTE • No recursivo • CTEs que no estan autoreferenciados • Recursivo • CTE’s que tienen una referencia a si mismos • Uno o mas querys • Un query debe ser no recursivo Anchor Member (AM) • Otros querys son recursivos Recursive Members (RM) • UNION ALL join • Requiere que todos los miembros se referencien exactamente a la misma columna

6. Common Table Expression (CTE) • Single-Parent Scenario

7. PIVOT y UNPIVOT • Pre- SQL 2005 • Sentencia CASE– código extenso y complejo • PIVOT y UNPIVOT • PIVOT • Convierte filas en columnas • UNPIVOT • Convierte columnas en filas

8. PIVOT

9. Resultados de Pivot

10. UNPIVOT • Revierte la operación de PIVOT • Normaliza datos pre-pivoted • Inserta columnas en filas • Elimina NULLs en las columnas resultantes

11. Datos Unpivot

12. APPLY • APPLY facilita las operaciones de join con funciones del tipo de valor tabla. • Permite la invocación de una función del tipo valor tabla para cada fila devuelta por una expresión outer table de la consulta • La función del tipo de valor tabla actúa como el right input y la expresión outer table actua como el left input • El right input es evaluado para cada fila desde el left input y las filas creadas son combinadas por el output final • La lista de columnas creadas por APPLY es el set de columnas definidos en el left input seguido por la lista de columnas devueltas por el right input

13. CROSS APPLY • CROSS APPLY devuelve solamente las filas desde la outer table producida por una función del tipo de valor tabla

14. CROSS APPLY

15. OUTER APPLY • OUTER APPLY • Retorna tanto las filas que producen un resultado como las que no. • Retorna valores NULL en las columnas resultantes de una función del tipo de valor tabla • Cross Apply (Ejemplo): • El departamento de Logística no aparece en el resultado • Usando Outer Join, Logística aparecerá • Se mostrara Nulls will en las columnas que correspondan a una función del tipo tabla

16. OUTER APPLY

17. Mejoras en referencia integral Declarativa (DRI) • SET NULL • SET DEFAULT

18. Mejoras en referencia integral Declarativa (DRI) • CREATE TABLE Customers • ( customerid CHAR(5) NOT NULL, • /* other columns */ • CONSTRAINT PK_Customers PRIMARY KEY(customerid) • ) • INSERT INTO Customers VALUES('DUMMY') • INSERT INTO Customers VALUES('FRODO') • INSERT INTO Customers VALUES('GNDLF') • INSERT INTO Customers VALUES('BILBO')

19. Mejoras en referencia integral Declarativa (DRI) • CREATE TABLE Orders • ( orderid INT NOT NULL, customerid CHAR(5) NULL DEFAULT('DUMMY'), orderdate DATETIME NOT NULL, • CONSTRAINT PK_Orders PRIMARY KEY(orderid), • CONSTRAINT FK_Orders_Customers FOREIGN KEY(customerid) REFERENCES Customers(customerid) • ON DELETE SET NULL ON UPDATE SET DEFAULT ) • INSERT INTO Orders VALUES(10001, 'FRODO', '20040101') • INSERT INTO Orders VALUES(10002, 'FRODO', '20040102') • INSERT INTO Orders VALUES(10003, 'BILBO', '20040101') • INSERT INTO Orders VALUES(10004, 'BILBO', '20040103') • INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104') • INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105')

20. Manejo de Errores • Versiones anteriores: • El manejo de errores es requerido en código complejo y que sea pasible de errores. • Para centralizar el manejo se debían utilizar las sentencias GOTO y RETURN • Errores como la conversión de tipos provocaba la terminación de la ejecución; en consecuencia, estos no podían ser atrapados dentro del Transact-SQL • SQL Server 2005: • TRY/CATCH

21. TRY/CATCH (Manejo de Errores) • Example • USE AdventureWorks • GO • BEGIN TRANSACTION • GO • BEGIN TRY • -- Generate a constraint violation error. • DELETE FROM Production.Product WHERE ProductID = 980; • END TRY • BEGIN CATCH • SELECT ERROR_NUMBER() AS ErrorNumber, • ERROR_SEVERITY() AS ErrorSeverity, • ERROR_STATE() as ErrorState, • ERROR_MESSAGE() as ErrorMessage; • END CATCH • GO • ROLLBACK TRANSACTION; • GO

22. Mejoras en TOP • Limitaciones en versiones anteriores • La opción TOP en las versiones anteriores de SQL Server, requería un numero o porcentaje de filas como una constante • TOP no estaba disponible para comandos INSERT, UPDATE y DELETE • En SQL Server 2005 se agregaron las siguientes mejoras • Variables y Sub-Queries pueden ser utilizadas opcionalmente como una expresión numérica retornando el numero o porcentaje de filas que serán afectadas por el query • TOP esta disponible para comandos DELETE, UPDATE e INSERT

23. DML con Resultados (Clausula OUTPUT) • La nueva cláusula OUTPUT que facilita el retorno de datos referenciando datos insertados o borrados de una tabla • En sentencias INSERT, refiriendo a INSERTED para obtener las columnas desde la ultima inserción • En sentencias DELETE refiriendo a DELETED para obtener las columnas eliminadas en la ultima acción • En sentencias UPDATE, referir a DELETED para obtener el ultimo estado de los datos, y a INSERTED para ver el estado actual.

24. READPAST • En versiones anteriores de SQL Server, READPAST podía ser utilizado únicamente en sentencias SELECT. • En SQL Server 2005, READPAST puede ser utilizado con sentencias UPDATE y DELETE. • Permite múltiples procesos paralelos manejando filas non-locked, y salteando filas lockeadas que están siendo utilizadas por otra sesión. • READPAST especifica al motor que no lea filas y paginas que están lockeadas por otra transacción. • READPAST solamente puede ser especificado en transacciones operando con el nivel de aislamiento de READ COMMITTED o REPEATABLE READ.

25. WAITFOR • WAITFOR bloquea la ejecución de un batch, stored procedure, o transacción hasta que un tiempo o intervalo se haya cumplido, o una sentencia especificada modifique al menos una fila. • Nuevas características • Timeout • WAITFOR valid RECEIVE statement (SQL Server Service Broker) • Los Cursores no pueden ser abiertos en una sentencia WAITFOR. • Vistas no pueden ser definidas WAITFOR.

26. DDL Triggers • SQL Server 2005 permite la declaración de triggers en eventos DDL • Los triggers DDL se ejecutan en el contexto de la transacción desde la cual fueron disparados. • Un ROLLBACK puede suceder para deshacer eventos que han disparado el trigger.

27. Notificación de Eventos Event Notifications Posted SQL Server Service Broker (SSB) Service Event Occurance Event Consumer Message Queue • Notificaciones de Eventos se ejecutan en respuesta a: • DDL • DML statements • Otros eventos a seguir.