El día 28 de abril de 2009 estaré junto al grupo de usuarios MOSSCA brindando una conferencia sobre SharePoint y BI. En lo que a mi respecta estaré mostrando la integración de este producto con Reporting Service 2008 contando algunos casos reales de implementación que me ha tocado trabajar en estos años

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

El SSMS (SQL Server Management Studio) de 2008 tiene varios cambios con respecto a sus versiones anteriores, a mi en lo general la mayoría me han gustado y mucho.

Uno de esos cambios es una prevención de cambios en estructuras de nuestras tablas, muchas veces cuando utilizamos esta herramienta y hacemos cambios no nos damos cuenta por debajo de ese cambio que es realmente lo que sucede, hay algunas cosas que son simples y otras un poco mas complejas, por ejemplo si quiero agregar un nuevo campo y lo hago al final de los actuales eso a nivel TSQL se resuelve con un simple ALTER TABLE, ahora bien, si ese campo en lugar de querer agregarlo ultimo lo quiero hacer en medio de otro existente no quedara otro remedio a nivel TSQL que borrar la tabla, crearla nuevamente y pasar los datos, esto no seria problema si estamos en un ambiente de desarrollo o con pocos registros, pero que sucedería si estamos en un ambiente productivo o con muchos registros? pues podríamos colapsar al sistema ya que la operación demoraría un tiempo.

Para ser mas explícitos con esto aquí abajo copio dos códigos, ambos son la salida de agregar un campo desde el SSMS a una tabla, el primero es como ultimo campo y el segundo ejemplo en el medio de algún campo actual para que podamos comparar las diferencias y que cosas tiene que hacer SQL para lograr nuestro cometido.

Ejemplo 1

BEGIN TRANSACTION
GO
ALTER TABLE dbo.EMPLEADOS ADD
    CUIT nchar(10) NULL
GO
ALTER TABLE dbo.EMPLEADOS SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Ejemplo 2

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_EMPLEADOS
    (
    LEGAJO int NOT NULL,
    NOMBRE varchar(30) NULL,
    CUIT nchar(10) NULL,
    FECHA datetime NULL,
    EDAD int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_EMPLEADOS SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.EMPLEADOS)
     EXEC('INSERT INTO dbo.Tmp_EMPLEADOS (LEGAJO, NOMBRE, FECHA, EDAD)
        SELECT LEGAJO, NOMBRE, FECHA, EDAD FROM dbo.EMPLEADOS WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.EMPLEADOS
GO
EXECUTE sp_rename N'dbo.Tmp_EMPLEADOS', N'EMPLEADOS', 'OBJECT' 
GO
ALTER TABLE dbo.EMPLEADOS ADD CONSTRAINT
    PK__EMPLEADO__5487D90715702A09 PRIMARY KEY CLUSTERED 
    (
    LEGAJO
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.EMPLEADOS ADD CONSTRAINT
    CK__EMPLEADOS__EDAD__1758727B CHECK (([EDAD]>=(18) AND [EDAD]<=(70)))
GO
COMMIT

Como se podrá observar el segundo ejemplo puede llegar a causarnos bastantes problemas. Bien esto sucede porque el cambio que le solicitamos no tiene otra opción que hacerlo así, en otras palabras nosotros desde el SSMS podemos hacer las cosas de forma visual (de eso se trata la idea de usar parte de esta herramienta) pero todo se traduce a sentencias TSQL.

Ahora bien, en versiones anteriores al producto una persona podía hacer el segundo cambio sin mediar el impacto porque desconoce el mismo y la herramienta no lo previene.

Ahora y con la versión 2008 por defecto hay un seteo en las opciones que nos permite prevenir cualquier cambio vía interfaz que represente el borrado de una tabla (como en nuestro ejemplo 2), esa opción se muestra en la siguiente figura y se llama Prevent saving changes that requiere table re-reation

Al estar esta opción activada cuando hacemos un cambio que representa una regeneración de la tabla nos encontraremos con el siguiente mensaje de error: Saving Changes is not permitted 

Como así lo indica el titulo, ya contamos con el primer service pack del SQL Server 2008. Les recomiendo armar un ambiente de test antes de ponerlo en producción.