Mejoras del lenguaje T-SQL 2012 (parte 1)
Enrique Catalá Bañuls
REL300003
Mentor – Relational engine MAP 2012 – Microsoft Technical Ranger – Microsoft Certified Trainer
[email protected] @enriquecatala
Mejoras del lenguaje T-SQL 2012 (parte 1)
Especificación robusta de metadatos
TRY-CATCH
Nuevas funciones
Mejoras en formateo de datos
Paginación
Secuencias
Window functions
Podemos definir “contratos” de conjuntos de resultado Número de resultsets devueltos
Tipos de datos y columnas
Nueva cláusula WITH RESULT SETS permitida: T-SQL Dinámico
Cláusulas OPENROWSET
Procedimientos almacenados
No permitida por ahora en INSERT…EXEC
Especificacion robusta de metadatos
WITH RESULT SETS
exec sp_who2 WITH RESULT SETS ( ( spid int, estado varchar(255), login sysname, nombre_de_host sysname, bloueado_por_spid sysname, nombre_bbdd sysname, comando nvarchar(max), tiempo_cpu int, entrada_salida int, ultima_ejecucion sysname, nombre_de_programa sysname, tralari int, tralara int ) )
Incluye nuevas DMV para análisis de metadatos
Sys.dm_exec_describe_first_result_set
Sp_describe_first_result_set
Sys.dm_exec_describe_first_result_set_for_object
Sp_describe_first_undeclared_parameters
Se analizan correctamente metadatos de salida (no como SET FMTONLY)
Especificacion robusta de metadatos
Análisis de metadatos dinámico
DEMO DEMO Especificación robusta de metadatos
Mejoras del lenguaje T-SQL 2012 (parte 1)
Especificación robusta de metadatos
TRY-CATCH
Nuevas funciones
Mejoras en formateo de datos
Paginación
Secuencias
Window functions
Por fin disponemos de operación THROW
Si un THROW no lleva catch asociado, la sesión finaliza
Se permite relanzar hacia arriba en CATCH o ejecución aislada
ADIÓS AL GOTO!!
TRY-CATCH
Por fin cláusula THROW
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable }
]
[ ; ]
RAISERROR THROW
Los msg_id que se usan en
RAISEERROR deben existir en
sys.messages
El número de error no tiene por qué
estar definido en sys.messages.
Se aceptan formateos de printf No se aceptan formateos printf
Se puede especificar la severidad del
error.
La severidad es siempre 16. No se
puede cambiar
Mejoras del lenguaje T-SQL 2012 (parte 1)
Especificación robusta de metadatos
TRY-CATCH
Nuevas funciones
Mejoras en formateo de datos
Paginación
Secuencias
Window functions
EOMONTH(date [, months_to_add])
DATEFROMPARTS(year, month, day)
TIMEFROMPARTS(hour, minutes, seconds, fractions, scale)
DATETIME2FROMPARTS(year, month, day, hour, minutes, seconds, fractions, scale)
DATETIMEFROMPARTS(year, month, day,hour, minutes, seconds, milliseconds)
SMALLDATETIMEFROMPARTS(year, month, day,hour, minutes)
Nuevas funciones
(1/2)
TRY_CONVERT( data_type [(lenght)], expression [, style])
FORMAT( value, format [, culture] )
PARSE( string_value as data_type [USING culture] )
TRY_PARSE( string_value as data_type [,USING culture])
CONCAT(val1, val2…[, valN])
Una función mejorada LOG() ahora acepta como parámetro la base de logaritmo
Nuevas funciones
(2/2)
Permiten abreviar sentencias que antes solo se podian realizar mediante CASE
IIF( boolean expr, true_value, false_value)
Cuando se trata de un IF-ELSE
CHOOSE( index, val1, val2 [, valN])
Cuando queremos compactar un CASE grande
Nuevas funciones
Mejoras en bifurcadores
DEMO DEMO TRY-CATCH-THROW
Nuevas funciones
Mejoras del lenguaje T-SQL 2012 (parte 1)
Especificación robusta de metadatos
TRY-CATCH
Nuevas funciones
Mejoras en formateo de datos
Paginación
Secuencias
Window functions
Sintaxis ANSI que puede servir a fines idénticos a TOP(n)
Muchísima mas potente
Filtro aplicado sobre la cláusula ORDER BY
OFFSET indica cuantas filas hay que saltarse
FETCH indica cuantas filas se deben devolver tras el OFFSET
*Por ahora, mismo plan de ejecución
Paginación
Ahora
Clara y concisa
Paginación
Sintaxis
Mejoras del lenguaje T-SQL 2012 (parte 1)
Especificación robusta de metadatos
TRY-CATCH
Nuevas funciones
Mejoras en formateo de datos
Full-text search
spatial
Paginación
Secuencias
Window functions
Una secuencia es un objeto usado para autogenerar números basados en criterios flexibles
Es la evolución de IDENTITY
Muy flexible y con posibilidad de optimizar su rendimiento
Solo se soporta nombre de dos partes Almacenado a nivel de base de datos
La equivalencia lógica con IDENTITY
Secuencias
¿Qué son?
Sirven para crear valores de clave en inserciones Permiten incluso almacenar dicho valor en variable
NEXT VALUE FOR Función para obtener el siguiente valor de la secuencia
No guardan relación con los números generados. Pueden borrarse incluso después de haber asignado valores en uso
Se deben quitar las constraints previamente
Secuencias
¿Para qué sirven?
Secuencias
vs identity
Option Identity Sequences
Obtain value before use No Yes
Table-Independent No Yes
Use in UPDATE No Yes
Used in SELECT No Yes
Control order Not in SELECT INTO
Yes in INSERT SELECT Yes
Effected by rollback No No
Can associate/disassociate
with an existing column No Yes
Can define minimum and
maximum values No Yes
Can cycle No Yes
Can change increment No Yes
Supports defining caching No Yes
Obtain range of values No Yes
DEMO DEMO Paginación
Secuencias
Mejoras del lenguaje T-SQL 2012 (parte 1)
Especificación robusta de metadatos
TRY-CATCH
Nuevas funciones
Mejoras en formateo de datos
Full-text search
spatial
Paginación
Secuencias
Window functions
Window function Función que aplicada a un conjunto de filas definidas por un
window descriptor, devuelve un valor único por cada fila de la consulta
Window descriptor Define a qué filas se les aplicará la función.
Window functions
Definición
Versión 2000 SQL Server 2000 no provee de sintaxis alguna para soportar window
functions
Versión 2005 SQL Server 2005 introduce una implementación parcial de las
window functions utilizando la clausula OVER
Versión 2012 SQL Server 2012 tiene una implementación casi completa de las
window functions
Window functions
Pasado, presente y futuro
SQL Server 2005 introdujo Operador OVER (parcialmente implementado)
Nuevas window functions
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()
SQL Server 2008/2008R2 no añadieron ninguna nueva implementación en esta área específica.
Window funtions
SQL 2005-2008R2
SQL Server 2012 está casi cerca de ofrecernos la implementación completa de window funtions
SQL Serve 2012 introduce: Reimplementación de cláusula OVER
Casi completa
ORDER BY
Windows Frame
8 nuevas window functions
LAG(), LEAD()
FIRST_VALUE(), LAST_VALUE()
CUME_DIST(),PERCENT_RANK()
PERCENTILE_DISC(), PERCENTILE_COUNT()
Window functions
SQL 2012
SQL Server 2005/2008/R2
Window functions
Cláusula OVER
SQL Server 2012
Qué pasa si queremos obtener la suma y el valor de dicha columna que lo provoca? (agrupación y detalle)
Window functions
¿Por qué son necesarias las window functions?
select id_table, value,
sum(value) as [sum(value)]
from table1 group by id_table
Msg 8120, Level 16, State 1, Line 1
Column 'table1.value' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
id_table value
1 1
2 1
2 2
3 1
3 2
3 3
SUM(value)
1
3
6
id_table value sum(value)
1 1 1
2 1 3
2 2 3
3 1 6
3 2 6
3 3 6
Select sum(value) as [sum(value)]
from table1 group by id_table
Si estamos en SQL Server 2005/2008/R2… ¿estoy en lo cierto con esta aproximación?
Ahora que ya sabemos que la cláusula OVER existe…
Esta debería ser nuestra solución
Window functions
select id_table,
value,
sum(value) over(partition by id_table)
from table1
id_table value sum(value)
1 1 1
2 1 3
2 2 3
3 1 6
3 2 6
3 3 6
Window functions
Conceptos clave: Todos juntos Partition
UNBOUNDED
FOLLOWING
UNBOUNDED
PRECEDING
CURRENT
ROW
Window functions
Sintaxis
Partitioning
Ordering
Slicing/framing
Window functions
Conceptos clave
Una partición es como un grupo de filas con “características” similares dentro del mismo conjunto de datos
Window functions
Conceptos clave: Particionado
Una partición es como un grupo de filas con “características” similares dentro del mismo conjunto de datos
Window functions
Conceptos clave: Particionado
RANGE/ROWS ROWS | RANGE BETWEEN <B1> AND <B2>
ROWS | RANGE <B1>
Window functions
Conceptos clave: Slicing/Framing
B1 and B2 can be UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
For “ROWS” clause only
<scalar expression> PRECEDING
<sclara expression> FOLLOWING
Note B1 <= B2 or NULL will be returned
Except in COUNT() that 0 will be returned
Window functions
Conceptos clave: Slicing/Framing
Window functions
Conceptos clave: Todos juntos Partition
UNBOUNDED
FOLLOWING
UNBOUNDED
PRECEDING
CURRENT
ROW
De offset LAG()
LEAD()
FIRST_VALUE()
LAST_VALUE()
De distribución PERCENT_RANK()
CUME_DIST()
PERCENTILE_CONT()
PERCENTILE_DIST()
Window functions
Nuevas funciones analíticas en SQL 2012
DEMO DEMO Nuevas funciones y sintaxis
Implementación para definición de frames ROWS: Completa
RANGE: Parcial. Solo soporta
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW
EXCLUDE: No implementado
Funciones de ranking no soportan framing ROW_NUMBER(), RANK(), NTILE(), DENSE_RANK(),
PERCENTILE_DIST(), PERCENTILE_CONT()
No implementado Window aliases
Clausulas: NULLs FIRST y NULLs LAST
Window functions
¿por qué implementación casi completa?
Mejoras del lenguaje T-SQL 2012 (parte 1)
Especificación robusta de metadatos
TRY-CATCH
Nuevas funciones
Mejoras en formateo de datos
Paginación
Secuencias
Window functions
Si quieres disfrutar de las mejores sesiones de
nuestros mentores de España y Latino América,
ésta es tu oportunidad.
http://summit.solidq.com/madrid/
Síguenos: