Metricarts

Columnas

SQL Server y Novedades en Transact-SQL
Publicado por admin_metricarts
el Lunes 28 de Enero de 2013
3 comentarios

Cada día las empresas tienen más facilidades para acceder a lo último en tecnología. Pero compra de tecnología y software avanzado no siempre garantiza que a estos se les saque el mejor rendimiento. Para ello debe haber un conocimiento extra en cuanto a nuevas funcionalidades posean las nuevas versiones de motores de bases de datos o lenguajes de programación.

En este artículo se hace referencia a nuevas funciones o mejoras que se han desarrollado en el motor de Microsoft SQL Server 2012 manejadas a través de su lenguaje Transact-SQL y que aliviarán cargas de datos o consultas para reportes, que inciden considerablemente en el rendimiento y tiempos estimados de desarrollo, ejecución y mantención de soluciones.

Cláusula OVER

La cláusula OVER ya viene desde versiones anteriores y es muy usada para realizar particiones de los datos u ordenar datos, la cual siempre viene asociada a otra función tal como ROW_NUMBER(), RANGE(), o funciones de agregado como SUM() sin la necesidad de agrupar datos con la sentencia GROUP BY, entre otras más.

Lo nuevo que permite esta cláusula es realizar sumas acumuladas o promedios móviles entre los datos, reduciendo el tiempo de las consultas.

Para entender esto veamos un ejemplo simple. Hagamos el ejercicio que queremos tener el Acumulado del Año de las ventas Por Mes de una empresa. O sea, en Enero queremos tener 2 resultados; el monto total de Enero y la suma de todos los meses que van del año (en este caso ambos valores se repetirán por ser el primer mes del año). Pero en Febrero ya no será lo mismo, ahora tendremos el total de Febrero y la suma de Enero y Febrero. Esto seguirá acumulándose por el resto de los meses. Este es un ejemplo bastante habitual en reportería.

Antes, la forma en que se resolvía era cruzando la tabla de las ventas del mes  (tabla A) consigo misma (tabla B) donde la condición de cruce se hacía respecto a un mes de A con todos los meses anteriores de B. (Ver código 1 de ejemplo).

 

SELECT A.NombreMes, MIN(A.Monto) MontoMes, SUM(B.Monto) MontoAcumuladoFROM #VentaMes ALEFT JOIN #VentaMes BON A.Mes >= B.Mes

GROUP BY A.NombreMes, A.Mes

ORDER BY A.Mes

Código 1: Consulta para realizar un Suma Acumulada con SQL Server 2008.

 

Ahora en cambio la consulta no implica cruzar consigo misma. Basta usar la cláusula OVER con la sentencia ORDER BY como argumento y la función de agregado SUM() para realizar la operación. En el código 2 se puede ver cómo queda la consulta en SQL Server 2012. En el ejemplo se tenía el monto de los meses ya sumado, pero el rendimiento real de la consulta se puede ver en tablas con miles de registros donde cruzar la tabla respecto a sí misma puede resultar muy costoso.

SELECT NombreMes, Monto, SUM(Monto) OVER (ORDER BY Mes asc) AS MontoAcumuladoFROM #VentaMesORDER BY Mes ASC

Código 2: Consulta para realizar un Suma Acumulada con SQL Server 2012.

La sentencia anterior organiza los datos por mes (ORDER BY Mes asc) y realiza la suma de todo los montos hasta la fila actual. Este tipo de consultas puede tener más argumentos, como indicadores de cuantos registros considerar, donde iniciar la suma, o realizar nuevas particiones con el uso del Partition By. Para mayor detalle los invito a consultar el artículo de Microsoft referenciado aquí.

 

OFFSET-FECHT

La funcionalidad OFFSET-FECHT ha sido agregada a la conocida cláusula ORDER BY. Esta orden nos permite filtrar la cantidad de filas que queremos ver o usar sin tener la necesidad de mostrar un TOP N o realizar un ROW_NUMBER()  previamente para mostrar por ejemplo resultados desde la fila 1000 a la 3000. También esto se conoce como paginación.

En una versión anteriores tendríamos que haber usado el ROW_NUMBER()  para añadir un nuevo campo, ordenando por la o las columnas deseadas y luego haber hecho una nueva consulta sobre los datos obtenidos, ahora filtrando este nuevo campo entre los valores 1000 y 3000 (ver consulta 3 de ejemplo).

 

SELECT *FROM (SELECT Fecha, Monto, ROW_NUMBER() OVER(ORDER BY Fecha ASC) OrdenFROM VentasDia

) A

WHERE Orden BETWEEN 1000 AND 3000

Código 3: Consulta para paginar filas con SQL Server 2008.

El uso de OFFSET es a continuación del ORDER BY <Campo> y se añade de la siguiente forma:

OFFSET <fila de inicio> ROWS FETCH NEXT <Cantidad de filas a considerar> ROWS ONLY. En el Código 4 se muestra con ejemplo el uso de la sentencia.

 

SELECT Fecha, MontoFROM VentasDia

ORDER BY Fecha

OFFSET 1000 ROWS

FETCH NEXT 2000 ROWS ONLY

Código 4: Consulta para paginar filas con SQL Server 2012.

 

Try_Convert

Es usual que en muchas cargas de datos se haga necesario transformar valores de un tipo a otro para poder incluirlos en nuestras nuevas bases o para simplemente mostrarlo en un reporte. El problema que conlleva esta transformación es que si el dato que intentamos convertir no calzaba con el otro tipo se inducía inmediatamente a un error de la sentencia. Ahora, SQL Server 2012 incluye la función try_convert() la cual realizará el casteo del dato, siempre y cuando este pueda realizarse; en caso contrario retornará un valor NULL.

 

SELECT TRY_CONVERT(INT, ‘1’)

Código 5: Ejemplo de uso de la función TRY_CONVERT.

 

CONCAT

En las versiones anteriores se podía realizar la concatenación de cadenas de valores simplemente usando el operador  “+”.  Pero esta operación no permite realizar la concatenación de valores que fueran de distinto tipo sin hacer la conversión previa. También otro problema que presenta es que si uno de los registros tiene un valor NULL, la agrupación completa quedará con valor NULL.

Ahora con SQL Server 2012 y la función CONCAT se puede combinar diferentes tipos de datos sin tener que realizar cambios ni tener que comprobar si alguno de estos es nulo.

 

SELECT CONCAT(‘LA FECHA Y HORA ACTUAL ES: ‘, GETDATE())

Código 6: Ejemplo de uso de la función CONCAT.

 

IIF

Se hizo una versión reducida del antiguo IF… ELSE de versiones anteriores, en la cual se generaban secciones para distintas instrucciones. Aún este método se puede usar, pero para condiciones cortas es mejor usar la nueva función. Esta se usa indicando una condición, que puede ser verdadera o falsa, luego el valor que se retorna en caso de que la condición se cumpla y luego el valor retornado en caso de que no haya cumplimento. En el código 7 se muestra un breve ejemplo de su uso.

SELECT IIF(@VALOR1 > @VALOR2, ‘TRUE’,’FALSE’)

Código 7: Ejemplo de uso de la función IIF.

Metric Arts cuenta con larga experiencia en el desarrollo de soluciones en la plataforma Microsoft SQL Server y su integración con los múltiples sistemas existentes en las organizaciones.

Artículo escrito por Jaime Saez. Jaime es Ingeniero Senior de Metric Arts, experto en soluciones de integración y reportería en plataformas SAP y Microsoft. Dentro de sus áreas de expertisse destacan la industria bancaria y gobierno.

3 comentarios sobre “SQL Server y Novedades en Transact-SQL

  1. Patricio Cofre dice:

    Súper buenas mejoras para reducir la cantidad de código y con esto reducir la posibilidad de errores.

    Le he sacado mucho provecho a CONCAT e IIF. Hay dos funciones nuevas adicionales que creo que están muy buenas en este mismo sentido.

    EOMONTH – Para obtener el último día del mes de una fecha determinada: http://technet.microsoft.com/en-us/library/hh213020.aspx

    DATEFROMPARTS – Para concatenar una fecha a partir de sus partes: http://technet.microsoft.com/en-us/library/hh213228.aspx

  2. Marco Ramírez dice:

    Excelente artículo

    Excelente lo del DateFromParts(), Pato! Ahorra mucho tiempo y pienso que debería haberse creado hace mucho tiempo ya. También Concat() me parece muy interesante por el manejo de los nulls.
    Saludos!

  3. Fredy Hernandez dice:

    Me gustaría que las funciones IIF y SUM se pudieran combinar. Para el caso en que se quiere sumar un valor, pero que este valor por medio del IIF pueda asignarle positivo o negativo.
    ejemplo: SUM(IIF Mov=”C”,valor,valor*-1) AS Saldo.

Deja un comentario

Su correo nunca será publicado o compartido. Los campos obligatorios están marcados *

    *

Dos objetivos: Aumentar Valor y Reducir Riesgo

Related posts

No hay artículos relacionados

Customers