La paginación de datos desde el propio motor es una técnica que se utiliza en algunos procesos para poder llevar a la aplicación una cantidad reducida de registros y de ser necesarios mas saltar a una pagina posterior.

Para poder lograr esto han existido durante muchos años distintas técnicas de programación a nivel TSQL.

Ahora en SQL 2012 se ha incorporado la paginación de forma nativa la cual es parte del ANSI SQL 2000.

Para ello se utiliza dentro del Order By el seteo de OFFSET, FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY.

El siguiente link tiene información detallada de como se utiliza.

El siguiente código traerá los primeros 10 registros de un set de datos.

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID 
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY;

Bien ahora veremos un ejemplo de como se aplica la paginación antes de SQL 2012 y con esta nueva versión.

Para usar paginación antes de SQL 2012 una de las técnicas mas utilizadas es el uso de las CTE y Row_Number()

DECLARE @pagesize AS INT, @pagenum AS INT;
SET @pagesize = 5;
SET @pagenum = 1;

WITH SalesRN AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY Name) AS rownum,
    ProductNumber, ProductID,Name
    FROM[Production].[Product]
)
SELECT rownum, ProductNumber, ProductID,Name
FROM SalesRN
WHERE rownum > @pagesize * (@pagenum-1)
  AND rownum <= @pagesize * @pagenum
ORDER BY rownum;
go

Ahora bien, si queremos hacer la misma query con SQL 2012, podríamos escribir lo siguiente

DECLARE    @PageSize    TINYINT = 5,    
           @CurrentPage INT     = 1;
           
SELECT  ProductNumber, ProductID,Name
    FROM[Production].[Product]
ORDER BY Name    
OFFSET (@PageSize * (@CurrentPage - 1)) 
ROWS    
FETCH NEXT @PageSize ROWS ONLY;

Como se puede observar es mucho mas simple y natural ya que respeta a un estándar como el ANSI y la otra solución es un simple truco para lograr esto.

Veamos que sucede con los Query Plan de cada uno

Como se podrá observar en ambos casos es necesario hacer un Scan del Clustered pero al hacerlo con las nuevas instrucciones tenemos menos procesos y esto podría tener efectos del tamaño de una tabla.

Aquí comparamos ambos query plan de forma conjunta.

Ahora haremos una query que contemple paginación y el uso de Where, el primer ejemplo es antes de SQL 2012 y el segundo usando SQL 2012.

DECLARE @pagesize AS INT, @pagenum AS INT;
SET @pagesize = 5;
SET @pagenum = 1;


WITH SalesRN AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY Name) AS rownum,
    ProductNumber, ProductID,Name
    FROM[Production].[Product]
    WHERE Color = 'Red'
)
SELECT rownum, ProductNumber, ProductID,Name
FROM SalesRN
WHERE rownum > @pagesize * (@pagenum-1)
  AND rownum <= @pagesize * @pagenum
ORDER BY rownum;

DECLARE    @PageSize    TINYINT = 5,    
           @CurrentPage INT     = 1;

SELECT  ProductNumber, ProductID,Name
    FROM [Production].[Product]
where Color = 'Red'
ORDER BY Name    
OFFSET (@PageSize * (@CurrentPage - 1)) 
ROWS    
FETCH NEXT @PageSize ROWS ONLY;

Muchas veces los desarrolladores tenemos la necesidad de consultar datos de la metadata de SQL Server, que es esto de la metadata? pues bien, la metadata es la información que guarda SQL Server en tablas sobre datos como (Stores, tablas, columnas de una tabla, las vistas, el código de los Store, etc.)

Imaginemos que deseamos saber los parámetros que tiene un Store Procedure o bien en que tablas se encuentra una columna dada, o cuales son las relaciones que tiene una tabla, para poder hacer todo este tipo de querys necesitamos consultar esta metadata.

Bien, como lo hacemos, pues hay varias formas , una de ellas es consultas las tablas internas de SQL lo cual no es muy recomendado porque pueden cambiar entre versiones del producto (hasta con la instalación de un service pack) y dejarnos de funcionar nuestras querys, a partir de 2005 se pueden usar las vistas de sistema que son una mejora importante ya que nos dan una capa intermedia de acceso sin acceder directamente a las tablas de sistema, pero solo funciona a partir de 2005.

La otra opción es usar unas vistas ANSI (Standard para aquellas bases de datos que soporten ANSI) llamadas INFORMATION_SCHEMA, estas vistas se encuentran en casi todas las versiones de SQL Server (2000, 2005 y 2008) pero como son ANSI no se puede obviamente consultar todo lo que se puede hacer con las vistas o tablas internas.

Ahora bien, estas vistas ANSI si a los efectos de lo que un desarrollador puede consumir abarcan casi el 90% ya que en todos los motores existen las tablas, las relaciones, las columnas etc., esas cosas que consumimos con frecuencia las resuelven estas vistas ANSI sin mayor problema.

Bien, en este post les voy a mostrar distintas consultas habituales que hacemos como desarrolladores y las resolveré con las INFORMATION_SCHEMA.

Consultar información de las columnas de tablas

-- TABLAS QUE CONTIENEN UNA COLUMNA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'CUSTOMERID'

Listado de Tablas

SELECT * FROM INFORMATION_SCHEMA.TABLES 

Campos que conforman una PK

SELECT 
   TC.TABLE_NAME, CU.COLUMN_NAME 
FROM 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
   INNER JOIN 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
   ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
   WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
   AND TC.TABLE_NAME = 'CUSTOMER' -- TABLA 
ORDER BY TC.TABLE_NAME    

Relaciones entre tablas

SELECT 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    ConstraintName = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
      SELECT 
            TC.TABLE_NAME, CU.COLUMN_NAME 
      FROM 
      INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
      INNER JOIN 
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
      ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
      WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE 
PK.TABLE_NAME = 'SALESORDERHEADER' -- TU TABLA

Checks de una tabla dada

SELECT TC.TABLE_SCHEMA,TC.TABLE_NAME,CU.COLUMN_NAME,
CK.CHECK_CLAUSE 
  FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS  TC
ON TC.TABLE_NAME = CU.TABLE_NAME 
AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
INNER JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS CK
ON
CK.CONSTRAINT_NAME = TC.CONSTRAINT_NAME 
WHERE CONSTRAINT_TYPE = 'CHECK'
AND
TC.TABLE_NAME = 'CUSTOMER' -- TU TABLA

Parámetros de un Store Procedure

SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS 
WHERE SPECIFIC_NAME ='ufnGetProductDealerPrice'

Resumen:

Aquí les mostré algunas de las querys que mas usamos los desarrolladores cuando necesitamos consultar la metadata, como podrán haber observado no es nada complejo hacerlo y como en ANSI lo podemos usar en todas las versiones de SQL Server, para cosas mas complejas y quizás mas para administradores DBA es aconsejable usar las vistas de sistema de 2005 / 2008 y en 2000 las tablas de sistema pero para los desarrolladores yo recomiendo las INFORMATION_SCHEMA ya que con ellas cubren casi todas las necesidades y ante cambios de versión de producto no deben actualizar su código.

Les dejo un adjunto con todo el código acá descripto y algunos ejemplos mas

El otro día mientras daba un curso en una empresa para desarrolladores, vi que algunas cosas de las transacciones no se comprendían bien y francamente me llamo la atención.

Luego de ese curso pensé en explicar en mi blog como se manejan las transacciones dentro de SQL Server y que cuidados tener, y bueno aquí estamos

¿Porque usamos transacciones?

Bueno aquí la cosa parece simple verdad, básicamente usamos transacciones para poder asegurar integridad de una operación, si por ejemplo necesitamos guardar una factura y esta se compone de Cabecera y Detalle no se debería guardar si hubo algún error en el detalle.

También podríamos mencionar otros ejemplos, donde se necesitan hacer mas de una operación y de fallar alguna no aplicar nada.

Tenemos dos tipos de transacciones en SQL Server, las implícitas y las explicitas.

Las primeras son aquellas que no debemos indicarle al SQL que hacemos una transacción pero el lo genera, por ejemplo como el siguiente caso.

DELETE FROM CLIENTES
WHERE PAIS = 'ARGENTINA'

Aquí se ejecutara el delete para todos los clientes que cumplan la condición de Argentina, si hay un error al intentar borrar un cliente se hará un rollback y no se borrara ninguno, esto se debe a que el delete al igual que el insert y update internamente arman una transacción.

Las transacciones explicitas son aquellas que nosotros indicamos con la sentencia Begin Transaction / Commit o Rollback Transaction.

BEGIN TRANSACTION 
  INSERT INTO EMPLEADOS (ID, NOMBRE)
  VALUES (1,'CONDUIT')
  
  DELETE FROM AUDITORIA
  WHERE EMPLEADO = 1
COMMIT TRAN

¿Como funciona el Commit y Rollback?

El commit es el que confirmara la transacción y el rollback es el que la deshará, pero aquí hay algunos detalles muy importantes en su comportamiento cuando usamos transacciones anidadas.

Primero veamos un ejemplo simple

CREATE TABLE #T1 (ID INT, NOMBRE VARCHAR(50))
GO

CREATE TABLE #T2 (ID INT, FECHA DATETIME NOT NULL)
GO

BEGIN TRY

    BEGIN TRAN   
    
      INSERT INTO #T1 VALUES (1,'CONDUIT')
      INSERT INTO #T1 VALUES (1,GETDATE())
    
    COMMIT TRAN
END TRY

BEGIN CATCH
  ROLLBACK TRAN
  SELECT @@ERROR 
END CATCH

En el ejemplo básicamente hacemos dos insert y si hay algún error entramos en la sección del Catch y lo primero que hacemos es el rollback para luego mostrar el error.

Este es un simple ejemplo donde si no hay problemas se harán los dos insert y el commit los confirmara y de haber un error en algún insert se hará Rollback.

Anidar transacciones

Hasta aquí vimos dos ejemplos bastantes simples del manejo de transacciones. Ahora bien que sucede si necesitamos manejar transacciones anidadas, por ejemplo ejecutamos un Store que abre una transacción que llama a otro Store que abre otra transacción y así, o bien en el mismo código.

Aquí primero debemos comprender como impacta un Rollback y un Commit y es donde mayor confusión quizás hay.

El Rollback hará que se deshagan todas las transacciones o sea si armamos una transacción dentro de otra y la segunda hace un rollback hará que también la primera lo haga, en cambio el commit lo hará por cada bloque a menos que apliquemos un commit en el bloque externo lo cual hará que todo se comitee.

Para poder evaluar cuantas transacciones tenemos abiertas podemos utilizar la variable

@@Trancount

Para ser mas ejemplificado con este tema vamos a ver un poco de código, el primer ejemplo muestra como funciona el Rollback

USE TEMPDB
go

CREATE TABLE Cabecera (id int, fecha Datetime)
go

CREATE TABLE Detalle (CabeceraId int, linea int NOT NULL,
                      Cantidad decimal (8,2) NOT NULL)
GO

CREATE TABLE Auditoria (Id int identity,
                        Fecha datetime not null
                       ) 

-- Ejecute el Script por pasos para ir comprendiendo


-- ======================================= 
--      Funcionamiento del Rollback
-- =======================================

-- Vemos que no hay Transacciones
SELECT @@TRANCOUNT 

-- Abrimos una transacción
BEGIN TRAN
  -- hacemos los insert
  INSERT INTO Cabecera 
  VALUES (1,GETDATE())
  
  INSERT INTO Detalle 
  Values (1,1,100)
  
  -- vemos las transacciones abiertas (1)
  SELECT @@TRANCOUNT 

  -- Abrimos otra transacción
  BEGIN TRAN 
    -- Verificamos que Trancount se incremento en 1
    SELECT @@TRANCOUNT   
    
    -- Hacemos un Rollback
   ROLLBACK TRAN
  
    -- Verificamos que Trancount quedo en 0
    -- Lo cual ha hecho un rollback de todo
    SELECT @@TRANCOUNT   
go    

El siguiente ejemplo muestro como funciona el Commit con el manejo de transacciones

-- ======================================= 
--      Funcionamiento del Commit
-- =======================================

-- Vemos que no hay Transacciones
SELECT @@TRANCOUNT 

-- Abrimos una transacción
BEGIN TRAN
  -- hacemos los insert
  INSERT INTO Cabecera 
  VALUES (1,GETDATE())
  
  INSERT INTO Detalle 
  Values (1,1,100)
  
  -- vemos las transacciones abiertas (1)
  SELECT @@TRANCOUNT 

  -- Abrimos otra transacción
     BEGIN TRAN 
    -- Verificamos que Trancount se incremento en 1
     SELECT @@TRANCOUNT   
    
    -- Hacemos un Rollback
     INSERT INTO Auditoria (Fecha)
     VALUES (GETDATE())
  
     COMMIT TRAN
    -- Verificamos que Trancount decrecio en 1
     SELECT @@TRANCOUNT   

COMMIT TRAN  -- Hacemos el utimo Commit
  
-- Verificamos que Trancount queda en 0
SELECT @@TRANCOUNT   
go    
  

Hasta que pudimos observar con ejemplos la diferencia de comportamiento que tenemos entre el Rollback y el Commit

como hemos explicado en el inicio del articulo.

Ahora bien, en el siguiente ejemplo muestro como manejar transacciones anidadas sin que el rollback se vaya a 0 y que lo haga a un punto que yo determine.

-- ======================================= 
--      Manejo de transacciones Anidadas
-- =======================================

-- Vemos que no hay Transacciones
SELECT @@TRANCOUNT 

-- Abrimos una transacción
BEGIN TRAN A
  -- hacemos los insert
  INSERT INTO Cabecera 
  VALUES (1,GETDATE())
  
  INSERT INTO Detalle 
  Values (1,1,100)
  
  -- Hacemos un punto de salvacion
  
  SAVE TRAN B
  
  -- vemos las transacciones abiertas (1)
  SELECT @@TRANCOUNT 

  -- Abrimos otra transacción
     BEGIN TRAN C
    -- Verificamos que Trancount se incremento en 1
     SELECT @@TRANCOUNT   
    
    -- Hacemos un Rollback
     INSERT INTO Auditoria (Fecha)
     VALUES (GETDATE())
  
     ROLLBACK TRAN B
    -- Verificamos que Trancount es 2 y no 0
    -- Esto se da porque el rollback afecta 
    -- el punto de almacenamiento y no la transacción
    -- en si
     SELECT @@TRANCOUNT   

-- Hacemos los Commit
 COMMIT TRAN  
 COMMIT TRAN  
-- Verificamos que Trancount queda en 0
SELECT @@TRANCOUNT   
go    

-- Verificamos los datos y vemos que en Auditoria
-- no se guardaron registros porque hubo un rollback
-- pero solo de ese punto

SELECT * FROM Cabecera 
SELECT * FROM Detalle 
SELECT * FROM Auditoria 
  

Control de errores y transacciones

Hasta aquí hemos visto que el manejo del Rollback es muy distinto al del Commit Tran, ahora bien, como hacemos para controlar los errores ? la buena practica es que ante un error lo primero que se haga sea un Rollback pero que sucede si hubo un rollback anterior y el TranCount quedo en 0? Lo que deberíamos hacer es en control del error verificar primero la variable Trancount y si es mayor que 0 entonces si hacer el Rollback, aquí vemos un ejemplo.

-- ======================================= 
--      Manejo de Errores & transacciones 
-- =======================================
  
SELECT @@TRANCOUNT 

-- Abrimos una transacción
BEGIN TRY

BEGIN TRAN
  -- hacemos los insert
  INSERT INTO Cabecera 
  VALUES (1,GETDATE())
  
  INSERT INTO Detalle 
  Values (1,1,100)
  
  -- Abrimos otra transacción
  BEGIN TRAN 
    -- Verificamos que Trancount se incremento en 1
    SELECT @@TRANCOUNT   
    
    -- Generamos un error para ir al bloque CATCH
    INSERT INTO Auditoria (Fecha)
    VALUES (NULL)
  
    -- Verificamos que Trancount quedo en 0
    -- Lo cual ha hecho un rollback de todo
  
END TRY

-- Control de errores

BEGIN CATCH
  IF @@TRANCOUNT > 0
   BEGIN
    ROLLBACK TRAN
   END 

SELECT ERROR_MESSAGE() 

END CATCH

Conclusiones

En este post les he mostrado como se manejan y como funcionan las transacciones en SQL Server, siempre recuerden que las transacciones deberían durar lo mínimo posible para evitar loqueos que terminen en problemas de performance, escriban solo el código adecuado dentro del Begin y Commit.

Esto que vimos también se aplica si hay Stores procedures y en uno abren una transacción que llama a otro que a su vez abre otra transacción, recuerden del save tran para esos manejos si lo que desean es volver a un punto determinado.

En muchas oportunidades tenemos la necesidad de resolver este tipo de querys, por ejemplo para una tabla de empleados, de menús , una estructura de productos, etc..... Armar este tipo de querys jerárquicas no suelen ser algo simple para la mayoría de los desarrolladores. En este ejemplo que les muestro en el post hare una query jerárquica en SQL 2005 o 2008 utilizando unas nuevas sentencias TSQL llamada CTE (Common Table Expresion).

-- CREAMOS UNA TABLA EMPLEADOS

IF OBJECT_ID('DBO.EMPLEADOS') IS NOT NULL
   DROP TABLE DBO.EMPLEADOS
GO

CREATE TABLE DBO.EMPLEADOS (ID INT NOT NULL,JEFE_ID INT NULL,
                            NOMBRE VARCHAR(300),Puesto varchar(300))
GO

INSERT INTO DBO.EMPLEADOS VALUES (100,NULL,'Martin','CEO')
INSERT INTO DBO.EMPLEADOS VALUES (101,100,'Maxi','Gerente Sistemas')
INSERT INTO DBO.EMPLEADOS VALUES (102,100,'Veronica','Gerente Calidad')
INSERT INTO DBO.EMPLEADOS VALUES (103,101,'Javier','Analista SR')
INSERT INTO DBO.EMPLEADOS VALUES (104,101,'Gabriel','DBA')
INSERT INTO DBO.EMPLEADOS VALUES (105,102,'Ana','Auditora')
INSERT INTO DBO.EMPLEADOS VALUES (106,103,'Luis','Programador')
INSERT INTO DBO.EMPLEADOS VALUES (107,105,'Jose','Asistente')
GO

-- MOSTRAMOS EL ORGANIGRAMA HASTA EL NIVEL 2

WITH REPORTE(JEFE_ID, ID,NOMBRE,PUESTO,NIVEL) AS 
(
    SELECT JEFE_ID, ID,NOMBRE,PUESTO, 0 AS NIVEL
    FROM DBO.EMPLEADOS
    WHERE JEFE_ID IS NULL -- TRAEMOS TODOS LOS QUE NO TIENEN JEFES 
    UNION ALL
    SELECT e.JEFE_ID, e.ID, E.NOMBRE,E.PUESTO,NIVEL + 1
    FROM DBO.EMPLEADOS e
        INNER JOIN REPORTE d
        ON e.JEFE_ID = d.ID 
)
SELECT REPORTE.JEFE_ID, REPORTE.ID, REPORTE.NOMBRE,REPORTE.PUESTO,
EMPLEADOS.NOMBRE AS JEFE_NOMBRE, EMPLEADOS.PUESTO AS PUESTO_JEFE 
FROM REPORTE LEFT JOIN DBO.EMPLEADOS ON
REPORTE.JEFE_ID = EMPLEADOS.ID
WHERE NIVEL <= 2 
GO

En SQL Server 2008 disponemos además de esto la posibilidad de utilizar un tipo de dato jerárquico llamado Hierarchyid