OFFSET y FETCH, paginando sin el TOP en SQL 2012

Siguiendo con los artículos sobre nuevas funcionalidades para desarrolladores en SQL 2012 ahora les presento el concepto de paginación.
Hay otras bases del mercado que ya hace bastante tiempo tienen este concepto, pero en SQL Server si queriamos no traer toda la consulta sino solamente una parte la única herramienta con la que contabamos era la sentencia TOP (o algunas trampas para poder simular la paginacion). La sentencia TOP nos permitía traer solamente los N primeros valores de una consulta. Pero es muy habitual tener una interfaz de cara al cliente que le muestra un gran resultado por páginas, entonces desearía poder traer solamente lo que voy a mostrar, y para la primer página me sirve el TOP pero para el resto no.
Con el objetivo de apalear este problema es que se incorporó OFFSET y FETCH. Al utilizar estas sentencias en lugar de devolver todos los valores, devuelve a partir del valor indicado en OFFSET. ¿Cuantos valores retirna? la cantidad que se indique en FETCH.
Para que esto funcione es necesario que la consulta tenga un ORDER BY. Veamos un ejemplo.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 13/01/12
-- Description: Paginacion en SQL 2012
-- =============================================

-- Llenemos una tabla con valores para poder probar
CREATE TABLE #Temp(id INT PRIMARY KEY, campo2 INT, campo3 VARCHAR(100))
DECLARE @i INT
SET @i = 0
WHILE @i<100
BEGIN
    INSERT INTO #Temp
    SELECT @i, @i*7, 'Fila ' + CAST(@i AS VARCHAR)
    SET @i = @i + 1
END
GO

-- Primera prueba: ver el contenido
SELECT * FROM #Temp
GO

-- Forma vieja de paginar
SELECT TOP 10 * 
  FROM #Temp
ORDER BY campo2

SELECT TOP 10 * 
  FROM #Temp
 WHERE campo2>=70
ORDER BY campo2

-- Forma nueva de paginar
-- Obtengo los primeros 10 resultados
SELECT * 
  FROM #Temp
ORDER BY campo2
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Obtengo los siguientes 10!!!
SELECT * 
  FROM #Temp
ORDER BY campo2
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

DROP TABLE #Temp
GO

Según la documentación oficial se recomienda utilizar FETCH en lugar del viejo TOP. El motivo es que con el TOP es necesario ejecutar tantas consultas como páginas si deseen, y siempre es necesario hacer nuevamente una búsqueda. Con el FETCH el motor se encarga de que cuando volvamos a pedir algo no requiera reprocesarlo sino utilizar lo que ya se calculó.
Para que esto funcione correctamente se deben cumplir algunas simples condiciones:

1. La informacion a consultar no debe cambiar entre consulta y consulta
2. El ORDER BY debería ser sobre una columna (o varias) que sea única

Si todo esto se cumple (lo cual no es complicado) podremos comenzar a disfrutar de los beneficios del FETCH.

Manejo de excepciones en SQL 2012

Antes que nada quiero saludar a todo el mundo luego de un mes de descanzo totalmente alejado de la pc! Ahora con todas las ganas de comenzar el 2012! Ahora a comenzar con el artículo.
En SQL 2008R2 o inferior la forma que teniamos de hacer el manejo de errores era utilizando el TRY y CATCH y luego en caso de ser necesario generar un error con RAISE ERROR. Cualquiera que esté familiarizado con el paradigma de objetos también lo está con el concepto de "lanzar excepciones". Este paso es lo que se ha implementado en esta nueva versión de SQL con la instrucción THROW.
Veamos un ejemplo de como manejabamos las excepciones antes:

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 13/01/12
-- Description: Ejemplo de manejo de errores en SQL2012
-- =============================================

BEGIN TRY
 SELECT 1/0
END TRY
BEGIN CATCH
    DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
    SELECT @ErrMsg = ERROR_MESSAGE(),
        @ErrSeverity = ERROR_SEVERITY()

    RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
GO

En cambio ahora con esta nueva instrucción sería:

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 13/01/12
-- Description: Ejemplo de manejo de errores en SQL2012
-- =============================================

BEGIN TRY
 SELECT 1/0
END TRY
BEGIN CATCH
     THROW 51000, 'Mensaje de error a elección!', 1;
END CATCH
GO

Nuestra lógica no sufrió grandes cambios pero esto permite empezar a pensar en un paradigma de excepciones nuestro código.
Algunas diferencias entre el comportamiento de una y otra son:
RAISE ERROR

• El error debe estar definido en la sys.messages
• Los mensajes pueden utilizar el caracter % para ser parametrizados
• Se puede definir la severidad del error, siendo esta cualquier número, incluyendo las mas graves
• No se puede arrojar en cadena (hacia los llamadores) la excepción. Cuando se genera una nueva "pisa" la anterior

En cambio con la nueva instrucción THROW

• El mensaje de error se define al momento de lanzar la excepción
• Los mensajes NO pueden ser parametrizados. Tener en cuenta que al generarse en el momento los mensajes, esto no debería ser un gran problema
• La severidad del error es siempre la misma, severidad 16
• Un sp que captura una excepción puede relanzarla tal como en cualquier entorno de objetos

A mi ver es algo muy cómodo. Lo bueno es que no deberán salir todos corriendo a cambiar su código porque aún se mantendrá compatibilidad con ambas.
Espero que les haya servido.

Para mas info:
http://msdn.microsoft.com/en-us/library/ee677615%28v=sql.110%29.aspx

Nuevas funciones TRY_PARSE y TRY_CONVERT en SQL 2012

Hay muchas funciones nuevas en SQL 2012 apuntadas al desarrollo, y me gustaría ir dedicandole un lugar. La primera que veremos es TRY_PARSE y TRY_CONVERT que permiten ejecutar lo mismo que sus predecesoras pero sin dar error cuando la conversión no se puede realizar. Veremos algunos ejemplos de su aplicación:

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 17/11/11
-- Description: Ejemplo de manejo de errores en SQL2012 aca Denali
-- =============================================

-- Llenemos una tabla con valores para poder probar
CREATE TABLE #Temp(id INT PRIMARY KEY, campo2 INT, campo3 VARCHAR(100))
INSERT INTO #Temp SELECT 1, 1, '1'
INSERT INTO #Temp SELECT 2, 1, '001'
INSERT INTO #Temp SELECT 3, 1, 'fake1'
INSERT INTO #Temp SELECT 4, 1, 'fullfake'
INSERT INTO #Temp SELECT 5, 1, '01/31/2012'
INSERT INTO #Temp SELECT 6, 1, '31/01/2012'

-- Primera prueba: ver el contenido
SELECT * FROM #Temp

-- Segunda prueba: Castear la columna 3 a entero usando la vieja forma
SELECT CONVERT(INT, campo3) FROM #Temp
/* Resultado:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'fake1' to data type int.
*/

-- Tercera prueba: Hacer lo mismo pero con Try_Convert
SELECT TRY_CONVERT(INT, campo3) FROM #Temp
-- No hay error!!! Los dos primeros registros se convirtieron y los siguientes 3 son nulos

-- Cuarta prueba: Hacer lo mismo pero con fechas
SELECT CONVERT(DATE, campo3) FROM #Temp
/* Resultado:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
*/

-- Cuarta prueba: Hacer lo mismo pero con Parse
SELECT PARSE(campo3 AS DATETIME) FROM #Temp
-- Error!!! pero si usamos TRY_PARSE...
SELECT id, TRY_PARSE(campo3 AS DATETIME USING 'en-US') FROM #Temp
-- La salida parsea los valores que pudo y deja en nulo el resto!

-- Y si cambiamos la referencia cultural...
SELECT id, TRY_PARSE(campo3 AS DATETIME USING 'es-ES') FROM #Temp
-- Obtenemos otro de los registros!!! sin tener que recordar los odiosos codigos 101 etc

DROP TABLE #Temp

Mas info: http://msdn.microsoft.com/en-us/library/hh213126%28v=sql.110%29.aspx

Listar todas las fechas de un intervalo en TSQL

Hace poco en un foro alguien preguntó como hacer una consulta SQL para obtener todas las fechas en un intervalo dado, y como me gustó la solución la reescribi de forma mas generica y me gustaría compartirla con todos

-- Declaro las variables
DECLARE @desde DATETIME
DECLARE @hasta DATETIME
-- Seteo un valor inicial
SELECT @desde='20110101',@hasta='20111101';
-- Primero calculo la cantidad de dias y luego listo tantos numeros como diferencia de dias
WITH 
    CantDias AS (SELECT DATEDIFF(DAY, @desde, @hasta) Cantidad),
    Numeros AS(
        SELECT 1 numero
        UNION ALL
        SELECT n.numero + 1 numero
          FROM Numeros n, CantDias c 
        WHERE n.numero<=c.Cantidad
)
SELECT DATEADD(DAY, Numeros.numero-1, @desde) FROM Numeros OPTION (MAXRECURSION 0);

Microsoft SQL Server 2012 Release Candidate 0 (RC0)

Ya se encuentra disponible el RC0 del SQL2012!!! para el que lo quiera bajar dejo el link:
http://www.microsoft.com/download/en/details.aspx?id=28145

Entre esta semana y la que viene estaré subiendo algunos pequeños artículos apuntando a las novedades, pero para variar voy a enfocar en los cambios que afectan a los desarrolladores.

Como saber si un dia es fin de semana en SQL

Hace poco en un foro vi dos veces seguida esta pregunta así que lo comparto con todos. La pregunta era como hacer para obtener si un día es fin de semana o no. Esto era para poder sumar cierta información solamente de los días hábiles.

La solución propuesta es la siguiente:

DECLARE @fecha1 DATETIME = '20111111 14:30'
SELECT CASE WHEN (DATEPART(WEEKDAY, @fecha1) + @@DATEFIRST)%7 IN (0, 1) THEN 'Fin de semana' ELSE 'Dia laboral' END

Es interesante como hay que tener en cuenta DATEFIRST para independizarse de como se ha configurado el servidor.

Licencias de SQL 2012

Dejo un link explicando los cambios que se vienen en el licenciamiento de SQL 2012
Link oficial: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx

Buen link al respecto:

http://blogs.flexerasoftware.com/elo/2011/11/revolutionary-changes-to-microsoft-sql-server-2012-license-models.html

Creo que lo que mas me apena es que nos deja la version Datacenter, pero era un final anunciado...

Recompilando vistas en SQL Server

Esta vez haré un post mas corto de lo habitual. Ayer a un colega se le presentó una situación, que por un cambio en varias cosas de su motor debía recompilar una vista. Esto es facil utilizando

EXEC SP_REFRESHVIEW [miVista];

El problema es que ocurre cuando en lugar de una vista son cientas y potencialmente divididas en varias bases de datos. La solución que le brindé es un TSQL que le generase un script con todos los refresh para que despues pueda ejecutarlo y guardarlo como documentación. Para esto me valí de un procedimiento no documentado del sql que es el sp_MSforeachdb.

Les comparto como quedó el script final

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 09/11/11
-- Description: Como recompilar todas las vistas
-- =============================================

sp_MSforeachdb '
SELECT ''USE [?]
GO 
EXEC SP_REFRESHVIEW [''+NAME+''];
GO'' FROM SYSOBJECTS WHERE TYPE = ''V'' AND NOT ''?'' IN (''master'', ''tempdb'', ''msdb'', ''model'');
'
 

More: http://msdn.microsoft.com/en-us/library/ms187821.aspx

Aplicar filtros al sp_who2 en SQL Server

Muchas veces usamos el sp_who2 para ver las conexiones activas y determinada información sobre esta, pero esto es comodo si los resultados son pocos, en el caso de ser muchos registros, cientos o miles esto ya no es aplicable.
Para estos casos deseariamos poder aplicar un filtro al sp_who2 pero esto lamentablemente no es posible. Lo que veremos en este ejemplo es como hacer un script, que podemos tener siempre a mano, que se encargue de aplicar los filtros.
El primer paso será crear una tabla temporal donde gaurdará los datos devueltos por el sp_who2. Como siempre primero controlo si la tabla ya existe para que no me arroje error al ejecutar.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 25/10/11
-- Description: Filtrar los resultados del sp_who2
-- =============================================

-- Creo la tabla temporaria donde guardaré la salida del sp_who
IF NOT OBJECT_ID('tempdb.dbo.#sp_who2') IS NULL
DROP TABLE dbo.#sp_who2

CREATE TABLE #sp_who2
    (SPID INT, 
    Status VARCHAR(1000) NULL, 
    Login SYSNAME NULL, 
    HostName SYSNAME NULL, 
    BlkBy SYSNAME NULL, 
    DBName SYSNAME NULL, 
    Command VARCHAR(1000) NULL, 
    CPUTime INT NULL, 
    DiskIO INT NULL, 
    LastBatch VARCHAR(1000) NULL, 
    ProgramName VARCHAR(1000) NULL, 
    SPID2 INT,
    REQUESTID INT) 

GO

Una vez creada guardo en ella la salida del sp_who2

-- Inserto los valores
INSERT INTO #sp_who2
EXEC sp_who2
GO 

El paso siguiente es solamente hacer un select sobre dicha tabla, pero ahora aplicando filtro u orden, es decir lo mismo que haría en cualquier tabla. Por ejemplo filtrar solo las conexiones de mi usuario ordenadas por fecha.

SELECT *
  FROM #sp_who2
-- Pongo el filtro que deseo
 WHERE Login = 'AAIELLO'
-- O el orden que deseo!!!
ORDER BY LastBatch DESC
GO

Por último elimino las estructuras creadas para no dejar "sucia" la base.

 -- Elimino las estructuras temporarias
DROP TABLE #sp_who2
GO

 

Espero que les sirva!!! dejo a continuación el script copleto para que sea comodo de guardar.

 

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 25/10/11
-- Description: Filtrar los resultados del sp_who2
-- =============================================

-- Creo la tabla temporaria donde guardaré la salida del sp_who
IF NOT OBJECT_ID('tempdb.dbo.#sp_who2') IS NULL
DROP TABLE dbo.#sp_who2

CREATE TABLE #sp_who2
    (SPID INT, 
    Status VARCHAR(1000) NULL, 
    Login SYSNAME NULL, 
    HostName SYSNAME NULL, 
    BlkBy SYSNAME NULL, 
    DBName SYSNAME NULL, 
    Command VARCHAR(1000) NULL, 
    CPUTime INT NULL, 
    DiskIO INT NULL, 
    LastBatch VARCHAR(1000) NULL, 
    ProgramName VARCHAR(1000) NULL, 
    SPID2 INT,
    REQUESTID INT) 

GO

-- Inserto los valores
INSERT INTO #sp_who2
EXEC sp_who2
GO

SELECT *
  FROM #sp_who2
-- Pongo el filtro que deseo
 WHERE Login <> 'sa'
-- O el orden que deseo!!!
ORDER BY LastBatch DESC
GO

-- Elimino las estructuras temporarias
DROP TABLE #sp_who2
GO

 

Screencast: Local Server Groups en SQL

Hola!!! para los que les pareció interesante el artículo de como ejecutar un mismo script sql sobre varios servidores, acá dejo un screencast explicando de forma mas didáctica como hacer esto.
Saludos!