Este post está dedicado a mostrar qué novedades T-SQL vienen de la mano de SQL Server 2012 code name “Denali”
Advertencia: El contenido del siguiente post se ha realizado utilizando la beta pública CTP3 de SQL12

Especificación robusta de metadatos para resultsets

Como sabemos, en SQL Server se pueden especificar de forma robusta los parámetros de entrada. Si pensamos en la definición de un procedimiento almacenado, nos daremos cuenta que en su definición debemos especificar el tipo de datos y la dirección de los parámetros, así como valores por defecto.

Gracias a ello, lo que hacemos es definir un “contrato” para los parámetros, de forma que si no se cumple, se nos indique en forma de error.

A partir de SQL Server Denali, vamos a poder hacer lo mismo con los resultsets (con los resultados) de forma que podremos crear un “contrato” para:

  • Numero de resultsets devueltos
  • Tipo de datos de las columnas que devolverán los resultsets

Esto será gracias a la cláusula WITH RESULT SETS, que será posible especificarla para:

  • T-SQL dinámico
  • Cláusulas OPENROWSET
  • Procedimientos almacenados

¿Para qué nos será útil?

  • Forzar el collation de un resultado
  • Asegurarnos que el nº de columnas es el correcto
  • Asegurarnos que los tipos de datos se ajustan a lo que la aplicación espera
  • Asegurarnos que los nombres de las columnas se ajustan a lo que la aplicación espera
  • Asegurarnos que el nº de resultsets devueltos es el correcto

Ejemplos prácticos de utilización son los siguientes:

Posibilidad de realizar cambios de collation “al vuelo”

*NOTA: En la imagen anterior se muestra el tipo de datos salida y en un segundo resultado, la ejecución de la consulta @sql

Y obviamente con todo lo que queramos, como por ejemplo si no nos gustan los nombres de columna de procedimientos almacenados existentes:

Esto puede resultar muy útil puesto que como vemos se puede realizar en tiempo de ejecución y la propia aplicación puede forzar la comprobación de que la consulta es correcta, o asegurarse de que el collation es el que le toca…asegurarse el nombre de columnas, asegurarse que el nº de columnas devuelto es el esperado…

¿qué ocurre si no coincide el conjunto resultado esperado con el real?

Pues que obviamente tal como nos gustaría, nos generará un error indicando que no se ha validado el “contrato” que hemos establecido con los metadatos de resultado.

Otra aplicación todavía más útil si cabe es forzar el conjunto de resultado de procedimientos almacenados, para asegurarnos que siempre se van a comportar igual…por si acaso 🙂

Además de la cláusula WITH RESULT SETS descrita anteriormente, se proporcionan una serie de DMVs y procedimientos almacenados para analizar 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

Ventajas:

Se analizan correctamente los metadatos de salida, de forma que de forma correcta, se nos avisa que el resultset no es válido debido al flujo de control que implica diferentes conjuntos de resultado

Hasta ahora solo podíamos utilizar SET FMTONLY, que como vemos, no tenia en cuenta por ejemplo los flujos de control:

Como vemos, se mete por el primer if y se lanza el set fmtonly off que produce que se devuelvan resultados…lo cual está horriblemente mal y  no hace ni por asomo lo que debería (como menos debería devolver error…o al menos los metadatos! )

 

Captura y tratamiento de errores mejorada

Por fin tenemos THROW en bloques try-catch 🙂

THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ]

Su ejecución implica lanzar hacia arriba a un bloque CATCH la excepción lanzada. Precaución, porque si un THROW no lleva asociado un CATCH que pueda capturarlo, la sesión se finalizará.

Puede ser lanzado desde un bloque CATCH en cuyo caso no será necesario especificar código de error, o desde un flujo válido de ejecución, lo cual implicará que se asigne manualmente un código de error.

De esta forma, podremos realizar estos dos tipos de ejecución:

Ejecucion en bloque CATCH
  1. USE tempdb;
  2. GO
  3. CREATE TABLE dbo.TestRethrow
  4.     (ID INT PRIMARY KEY
  5. );
  6. BEGIN TRY
  7.     INSERT dbo.TestRethrow(ID) VALUES(1);
  8. —  Force error 2627, Violation of PRIMARY KEY constraint to be raised.
  9.     INSERT dbo.TestRethrow(ID) VALUES(1);
  10. END TRY
  11. BEGIN CATCH
  12.     PRINT ‘In catch block.’;
  13.     THROW;
  14. END CATCH;
Ejecucion aislada
  1. THROW 51000, ‘El valor no existe’, 1;

Para más información: http://msdn.microsoft.com/en-us/library/ee677615(v=sql.110).aspx

UTF-16

En SQL11 estará el soporte nativo UTF-16 de forma nativa, lo cual implicará que aquellos caracteres especiales que necesitan UTF-16 serán almacenados como un único valor char.

Probad a realizar esto en SQL Server anterior a Denali 🙂

Para más información: http://msdn.microsoft.com/en-us/library/ms180175(v=sql.110).aspx

Paginación

A partir de SQL Server Denali ya está presente como parte del lenguaje T-SQL, la paginación. La sintaxis como se puede observar es una extensión de la cláusula ORDER BY en la que se indican varios tipos de parámetros:

[ ORDER BY <order_by_list>
[ OFFSET <offset_value> { ROW | ROWS }
[ FETCH { FIRST | NEXT } <fetch_value> { ROW | ROWS } ONLY ] ] ]

Siendo:

  • offset_value el nº de filas a descartar (con esto podemos especificar el nº de página a devolver)
  • fetch_value el nº de filas a devolver (o tamaño de página)

De este modo, es muy fácil y claro especificar la paginación siguiendo este claro y sencillo patrón de acceso:

Snippet paginacion en Denali
  1. DECLARE
  2.   @pagenumber AS INT = 6,
  3.   @pagesize   AS INT = 10;
  4. SELECT columna1,columna2,…columnaN
  5. from TABLAS
  6. ORDER BY lo_que_sea ASC/DESC
  7. OFFSET (@pagenumber 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

No vamos a discutir este apartado en detalle en este post por tratarse de algo ampliamente discutido desde siempre y porque requeriría un post independiente. Se trata de una mejora expresiva, pero el rendimiento es similar al que se puede obtener mediante la paginación en SQL Server 2005 con las cláusulas ROW_NUMBER() y CTE

Para más información: http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx

Secuencias

SQL Server 11 Denali introduce por primera vez el concepto de Secuencias en SQL Server. Una secuencia es un objeto usado para autogenerar números para diferentes propósitos (generalmente para claves). Pero las secuencias son algo mas útiles y flexibles que lo que es IDENTITY (que se convierte en un subconjunto ahora de secuencia).

Para comprender las secuencias, hay que tener en cuenta que se trata de objetos independientes que residen a nivel de base de datos y que como tal, soportan cláusulas DDL como son: CREATE SEQUENCE, ALTER SEQUENCE y DROP SEQUENCE.

Su sintaxis queda definida de la siguiente forma:

Sintaxis SEQUENCE
  1. CREATE SEQUENCE [schema_name . ] sequence_name
  2.         [ <sequence_property_assignment> [ n ] ]
  3.     [ ; ]
  4. <sequence_property_assignment>::=
  5. {
  6.     [ AS { built_in_integer_type | user-defined_integer_type } ]
  7.     | START WITH <constant>
  8.         | INCREMENT BY <constant>
  9.         | { MINVALUE <constant> | NO MINVALUE }
  10.         | { MAXVALUE <constant> | NO MAXVALUE }
  11.         | { CYCLE | NO CYCLE }
  12.         | { CACHE [<constant> ] | NO CACHE }
  13. }

Además, existe una cláusula especial denominada NEXT VALUE FOR, que es indicada para obtener el siguiente valor de la secuencia y un procedimiento almacenado denominado sp_sequence_get_range que obtiene un rango de valores de la secuencia consecutivos.

De esta forma, crear una secuencia es tan fácil como:

  1. CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;

Lo cual crea una secuencia llamada dbo.Seq1 que comienza en 1 y que se incrementa de 1 en 1. La diferencia con IDENTITY ya se hace patente en este momento, ya que no está ligada a ningún objeto todavía, lo cual implica que podemos reutilizarla.

Es más, podemos obtener valores de ella, sin ni siquiera vincularla a ningún objeto:

  1. SELECT NEXT VALUE FOR dbo.Seq1 AS newval;

Y obviamente alterarla para cambiar su propia definición:

  1. ALTER SEQUENCE dbo.Seq1
  2.   RESTART WITH 2147483648
  3.   CYCLE;
  4. go
  5. ALTER SEQUENCE dbo.Seq1
  6.   RESTART WITH 1
  7.   NO CYCLE;

Pero sin duda, y al menos para mi que siempre estoy dandole vueltas a exprimir SQL Server, una de las mayores ventajas en cuanto a escalabilidad es la posibilidad de obtener rangos

  1. DECLARE @first AS SQL_VARIANT;
  2. EXEC sys.sp_sequence_get_range
  3.        @sequence_name= N’dbo.Seq1′,
  4.   @range_size        = 11,
  5.   @range_first_value = @first OUTPUT;
  6. SELECT @first;

La ventaja de realizar la acción anterior es que podremos reservar un rango de valores y trabajar con el a sabiendas que nadie nos lo va a quitar…¿y esto para qué será útil? Pues pensemos en una aplicación que trabaje de forma desconectada y le de al botón de salvar…asta ahora la única manera de autogenerar las claves si usábamos IDENTITY era insertar las filas para que se nos generaran los valores…y luego hacer UPDATE…con lo que obviamente conlleva tanto a nivel de mala escalabilidad (triggers?, dobles operaciones, lo autogestionas tu y te comes toda la carga de trabajo?…) como de “suciedad” o complejidad de código de aplicación añadida.

Pero ahora puedes decir…oye mira, dame 100 valores de mi secuencia, que voy a trabajar con ellos y ya luego veré si te los inserto o no, pero de momento me los das y me los reservas 🙂 . Todo desconectado y de forma muy clara y sencilla.

Es algo que se sale de la temática de este post, pero que seguro que mas pronto que tarde escribiré al respecto.

Comparación entre IDENTITY y SEQUENCE

Opción Identity Sequences
Obtener valor antes de usar No Yes
Objeto independiente No Yes
Uso en operación UPDATE No Yes
Uso en operación SELECT No Yes
Control de la ordenación Not in SELECT INTO
Yes in INSERT SELECT
Yes
Afectado por ROLLBACK No No
Posibilidad de asociarlo/desasociarlo a una columna No Yes
Definir umbrales máximo/minimo No Yes
Puede ser circular? No Yes
Puede modificar el incremento No Yes
Soporta caching? No Yes
Obtener rango de valor No Yes

Funciones escalares adicionales

Se han añadido las siguientes funciones escalares del sistema:

  • 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)
  • TRY_CONVERT( data_type [(lenght)], expression [, style])
  • FORMAT( value, format [, culture] )
  • PARSE( string_value as data_type [USING culture] )
  • TRY_CONVERT( string_value as data_type [,USING culture])
  • IIF( boolean expr, true_value, false_value)
  • CHOOSE( index, val1, val2 [, valN])
  • CONCAT(val1, val2…[, valN])

Mejoras en formateo de datos

En SQL11 disponemos de las nuevas funciones FORMAT(), PARSE() y TRY_PARSE(), que nos harán la vida mas fácil a la hora de trabajar con los tipos de datos que deseemos formatear. Quiero dejar claro, que su existencia no implica que a partir de ahora toda conversión deba pasar por ella y dejemos de lado CAST y CONVERT. Estas nuevas funciones no sirven para lo mismo como ahora veremos.

FORMAT()

De fácil codificación, es tan simple como su nombre que nos indica. Formatear un valor a nuestro antojo…pero con una utilidad práctica.

Su sintaxis es la siguiente:

FORMAT( value, format [, culture] )

El resultado de la función es una cadena de texto con el formato especificado en la combinación de format y culture.

De esta forma, se pueden hacer cosas como esta de una manera muy sencilla:

  1. DECLARE @d DATETIME = GETDATE();
  2. SELECT @d, FORMAT( @d, ‘dd/MM/yyyy’, ‘en-US’ ) AS Result
  3. go

Cuyo resultado podemos ver reflejado así:

Pero cuidado, porque es tan flexible, que incluso podemos equivocarnos y escribir esto (nótese las mm minúsculas)

  1. DECLARE @d DATETIME = GETDATE();
  2. SELECT @d, FORMAT( @d, ‘dd/mm/yyyy’, ‘en-US’ ) AS Result
  3. go

Cuyo resultado será ahora el siguiente:

Note el lector que el valor 50 es el de los “minutos”, que quedan designados con mm, a diferencia del mes, que queda designado con MM.

Ahora bien, ¿para qué puede sernos útil esto?

Pensemos por ejemplo en que necesitamos devolver en idiomas diferentes el resultado de una fecha. A menos que manualmente gestionemos una tabla que mantengamos nosotros con nombres de días y demás en cada uno de los idiomas que deseemos soportar (cosa que es un festival de horas perdidas), la solución ahora es cambiar en tiempo de ejecución el lenguaje:

  1. SELECT DATENAME(WEEKDAY, getdate());
  2. SET LANGUAGE ‘spanish’;
  3.   SELECT DATENAME(WEEKDAY, getdate());    
  4. SET LANGUAGE ‘us_english’;

Como vemos, esto puede ser tedioso y sobre todo problemático si olvidamos el último SET LANGUAGE para volver a dejar el lenguaje como estaba…

Ahora podemos hacerlo de una manera mas sencilla utilizando esta nueva función, incluso en la misma query:

  1. select format(getdate(),N’dddd’,N’en-us’),
  2.        format(getdate(),N’dddd’,N’es-es’)

Pero la función no solo sirve para trabajar con tipos de datos fecha. Tambien hace facil trabajar con valores numéricos que representan «dinero”. De esta forma, obtener el símbolo que tiene la moneda según la cultura es tan simple como esto:

 

  1.     DECLARE@m DECIMAL(12,2) = 1234567.89;
  2. SELECT FORMAT(@m, ‘C’, ‘es-es’),
  3.        FORMAT(@m, ‘C’, ‘en-us’),
  4.        FORMAT(@m, ‘C’, ‘ja-jp’)

Queda claro que esta función es útil para formatear fechas y números de forma explícita e independiente a la cultura o locale en cada caso, como string. Se hace de nuevo hincapié que el resultado de la función es una cadena de texto, no un tipo de datos heredado del tipo de datos de entrada.

Para más información consultar http://technet.microsoft.com/en-us/library/hh213505%28SQL.110%29.aspx

PARSE()

En este caso, la función transforma el valor de entrada el el tipo de datos requerido en tiempo de ejecución. Es como ir un pasito más allá.

Su sintaxis es la siguiente:

PARSE( string_value as data_type [USING culture] )

En este caso, string_value representa al valor que deseamos convertir (es un nvarchar(4000)) y debe ser la representación válida del tipo de datos al que deseamos convertirlo.

Para mas información consultar http://technet.microsoft.com/en-us/library/hh213316%28SQL.110%29.aspx

TRY_PARSE()

En este caso, usaremos dicha función para evitar el error que produce la función anterior PARSE() a la hora de convertir un valor que no sabemos si va a ser convertible desde string al tipo de datos deseado.

TRY_PARSE( string_value as data_type [,USING culture])

Al contrario que antes, ahora en lugar de devolverse error, se devolverá un NULL en caso de que no se pueda parsear al tipo de datos deseado

Conclusión

Son muchas y variadas las mejoras en cuanto a T-SQL que vienen de la mano de SQL Server Denali; algunas no las hemos mencionado todavía como mejoras en FTS, Spatial Data types, FileTable,…puesto que se trata en esos casos de no solo mejoras del lenguaje, sino características nuevas que no podemos discutirlas como una simple mejora del lenguaje expresivo.

Os animo a que descarguéis y probéis la nueva versión beta SQL Server 11 Denali, puesto que lo que se avecina es bastante interesante.

Para descargar acceder al siguiente link: http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

 

Enrique Catalá