tsql menos frecuente desde sql server 2005

#SQSummit

TSQL

Menos Frecuente

Desde SQL Server 2005

Luis José Morán Cuenca

lmoran@solidq.com

Data Platform Architect

TSQL Menos Frecuente Desde SQL Server 2005

• Introducción

• Cross y Outer Apply

• Grouping Sets

• Funciones de Windowing• Funciones de Ranking

• Funciones de Offset

• Funciones de Agregado

• Tablas Temporales de SQL Server 2016

CROSS / OUTER APPLY

• SQL Server 2000• Funciones Escalares

• Lo bueno permiten parametrización

• No son visibles en los planes de ejecución

• El código de la función se interpreta en cada llamada (cada fila)

• Por último y lo más importante de todo: NO ES POSIBLE EL PARALELISMO

De donde venimos….

CROSS / OUTER APPLY

• SQL Server 2000• Lo bueno permiten trabajar con conjuntos

• Vistas• El objetivo inicial de este objeto simplificación:

• Seguridad

• Complejidad del código

• Problemas:

• Anidamientos

• Falta de parametrización

• Si se quiere rendimiento vistas indexadas

De donde venimos….

CROSS / OUTER APPLY

• Ejemplo Real Vistas Anidadas• 67% Escrituras

• 52% Lecturas

De donde venimos….

CROSS / OUTER APPLY

• SQL Server >=2005• Funciones Tipo Tabla

• InLine

• Multiline

• Evolución natural de funciones escalares y vistas

• Ventajas:

• Parametrización

• Tratamiento a nivel de conjuntos

• Rendimiento

Donde llegamos….

CROSS / OUTER APPLY

• Cross / Outer Apply• Elemento que necesitamos para hacer joins con funciones tabla

• También aplicable con selects en lugar de funciones

• Cross Inner join

• Outer Left join

Cross / Outer Apply

Demo

CROSS / OUTER

GROUPING SETS

• Cláusulas que permiten realizar múltiples agregaciones en una única query con las columnas indicadas en la cláusula GROUP BY

• Dos especializaciones• CUBE• ROLLUP

• Posee funciones auxiliares para identificar grupos• Grouping_id

• Bitmap que produce un entero por cada agregación

• Grouping• Devuelve el valor 1 si no se esta agregando por la columna indicada NULL

¿Qué son los Grouping Sets?

Demo

Grouping Sets

GROUPING SETS

• ROLLUP(Categoría, Subcategoría, Producto) equivale a agregar por:• Categoría, Subcategoría, Producto

• Categoría, Subcategoría

• Categoría

• Todos

ROLLUP

GROUPING SETS

• CUBE(Categoría, Subcategoría, Producto) equivale a agregar por:• Categoría, Subcategoría, Producto• Categoría, Subcategoría• Categoría, Producto• Subcategoría, Producto• Categoría• Subcategoría• Producto• Todos

CUBE

Demo

Grouping Sets

Funciones de Windowing

• Son funciones que trabajan con los resultados de una consulta

• Pueden ser:• Ranking

• Offset

• Agregado

• Se apoyan en la clausula Over

• Pueden tener orden

• Se puede realizar particiones

• Se pueden utilizar frames

¿Qué son las Funciones de Windowing?

Funciones de Ranking

• Son funciones que nos permiten la clasificación de resultados• Row_Number(), añade un entero único desde 1 al último registro del

conjunto

• Rank(), registros menores + 1

• Dense_Rank(), valores distintos y menores + 1

• Ntile(Nº Grupos), Realiza tantos grupos de registros como se indique en el numero de grupos• Nº de registro por grupo = Nº de Registros / Nº de grupos

• Si resto no es 0 el nº de registros del ultimo grupo = resto

¿Qué son las Funciones de Ranking?

Demo

Funciones Ranking

Funciones de Offset

• Son funciones que nos devuelven un valor para un offset dado• LAG, devuelve un valor previo (scalar_expression [,offset] [,default])

OVER( [ partition_by_clause ] order_by_clause )• El offset no puede ser negativo

• LEAF, devuelve un valor posterior (scalar_expression [,offset] [,default]) OVER( [ partition_by_clause ] order_by_clause )• El offset no puede ser negativo

• FIRST_VALUE, devuelve del primer valor ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

• LAST_VALUE, devuelve del último valor ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

¿Qué son las Funciones de Offset?

Demo

Funciones Offset

Funciones de Agregado

• Son funciones que realizan una operación sobre un agregado• SUM(expression), suma los valores del rango OVER( [

partition_by_clause ] order_by_clause [Frame] )

• AVG(expression), media de los valores del rango OVER( [ partition_by_clause ] order_by_clause [Frame])

• MIN(expression), valor menor del rango OVER ( [ partition_by_clause ] order_by_clause rows_range_clause [Frame])

• MAX(expression), valor mayor del rango OVER ( [ partition_by_clause] order_by_clause rows_range_clause [Frame])

¿Qué son las Funciones de Agregado?

Funciones de Agregado

• Expresión que limita el rango de valores• ROWS, trabaja con valores físicos cada registro es distinto

• RANGE, trabaja con valores lógicos, puede agrupar varios

• Son expresiones del ANSI SQL pero no todo está implementado

¿Qué son los Frames?

ROWS\RANGE BETWEEN UNBOUNDED

PRECEDING |

<n> PRECEDING |

<n> FOLLOWING |

CURRENT ROW

AND

UNBOUNDED FOLLOWING |

<n> PRECEDING |

<n> FOLLOWING |

CURRENT ROW

RANGE BETWEEN INTERVAL '2' MONTH

PRECEDING AND CURRENT ROW

Funciones de Agregado

• Agregados Acumulativos

• Agregados Deslizantes en el Futuro

• Porcentajes

¿Para que sirven los Frames?

Demo

Funciones Agregado

Tablas Temporales SQL Server 2016

• Nuevo tipo de tabla para:• Mantener historia completa de los cambios

• Permitir de una manera fácil su análisis en el tiempo

• La solución consta de:• Cada registro tiene un rango temporal de validez

• El rango validez es gestionado por el sistema

• Consta de 2 columnas llamadas columnas de periodo de tipo datetime2

• Requiere PK

• No usa triggers

• Una tabla adicional con el mismo esquema para almacenar las versiones antiguas• A esta tabla se denomina tabla histórica

• Se la proporcionamos nosotros o la crea SQL Server

¿Qué son?

Tablas Temporales SQL Server 2016

• ¿Para que sirven?• Auditar todos los cambios

• Reconstrucción del estado de los datos en cualquier momento del pasado

• Calculo de tendencias en el tiempo

• Mantenimiento columnas Slowly Changing Dimension para BI

• Recuperar datos en caso errores por procesos

¿Qué son? / ¿Para qué sirven?

Tablas Temporales SQL Server 2016

• TSQL CREATE TABLE• GENERATED ALWAYS AS ROW { START | END } [ HIDDEN ] [ NOT NULL ]

• HIDDEN Aparecerán ocultos en la select

• No permiten nulos aunque en la clausula sea opcional si no se cita los pone no nulos

• Los campos de vigencia no pueden ser utilizados en (insert, update, merge, delete)

• SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = schema_name . history_table_name [, DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ]

• La tabla principal y la historica no se pueden borrar sino se quita la vinculación entre ellas

Creación

Tablas Temporales SQL Server 2016

• TSQL CREATE TABLE• Los nombres, numero y tipos de columna son iguales en ambas tablas• No se pasan a la tabla histórica:

• Estadísticas• Checks• Triggers• Configuración Particionamiento• Permisos• Row-level security predicates

• No hay restricciones de tabla (Pk,Fk) o columna en la tabla historica pero se permite valor por defecto para las columnas

• La tabla actual tiene PK y la historica NO, pero crea un índice cluster• No hay columnas identities en la tabla histórica• No se permiten trigger en la tabla histórica• No se permiten FK’s en la tabla histórica• La tabla histórica no puede estar en un filegroup de solo lectura• La tabla histórica no puede estar configurada para CDC o CT

Creación

Tablas Temporales SQL Server 2016

• Por defecto las tablas histórica tienen compresión de página

• Las tablas actuales pueden tener tipos (MAX) pero puede incrementar mucho el tamaño

• No estan permitidas operaciones ONLINE (reconstrucción de índices)

• No se puede modificar/borrar los datos de la tabla histórica

• En la tabla actual no se permiten en los triggers:• INSTEAD OF

• ON DELETE CASCADE/ON UPDATE CASCADE

Consideraciones ( I )

Tablas Temporales SQL Server 2016

• Compatibilidad• AO Full

• Replicacion:• Snapshot y Transaccional, debe montarse en el suscriptor y cuando le lleguen

cambios versionará esa información

• Merge, no soportada

• Indexación, MS aconseja:• Indice cluster columnstore / B-tree en la tabla actual

• Indice cluster columnstore en tabla histórica

Consideraciones ( II )

Tablas Temporales SQL Server 2016Creación

Tablas Temporales SQL Server 2016

• Se puede hacer en la tabla histórica o en la tabla principal

• Para la tabla principal se han añadido nuevas cláusulas TSQL• FOR SYSTEM_TIME obligatoria para poder utilizar las siguientes

• AS OF <fx> Start <= fx and End > fx

Consultando Cambios

Tablas Temporales SQL Server 2016

• FROM <fxini> TO <fxfin> Start < fxfin AND End > fxini

Consultando Cambios

Tablas Temporales SQL Server 2016

• BETWEEN <fxini> AND <fxfin> Start <= fxfin AND End >= fxini• Como el caso anterior pero los límites están incorporados

Consultando Cambios

Tablas Temporales SQL Server 2016

• Contained IN (<fxini>,<fxfin>) Start >= fxini AND End <= fxfin

Consultando Cambios

Tablas Temporales SQL Server 2016

• ALL

Consultando Cambios

También puedes preguntar tus dudas con el

hashtag #SQSummit en Twitter

ADAPTIVE BI FRAMEWORK

Te ayudaremos a mejorar la velocidad de desarrollo de tu plataforma de analítica de negocio basada en nuestra experiencia:

•Diseña antes de construir

•Automatización de procesos por ETL

•Servicios de mentoring para ayudarte a conseguir mejores prácticas para la construcción de procesos específicos y plataformas de analítica de negocio

•Muy fácil de mantener

SOLIDQ FLEX SERVICES

Con SolidQ Flex Services evitarás sustos, consiguiendo que tus sistemas sean estables. Desde una solución sencilla de monitorización, hasta un servicio de atención de incidencias 24/7, mantenimiento proactivo, resolución de problemas y línea de soporte.

Todo con un coste fijo mensual… y tú dedica el tiempo a las cosas importantes.