jueves, febrero 21, 2008

Queries Recursivos en SQL Server 2005

Hace poco estaba preparando una platica de Sql Server 2008 y me preguntaron que eran Common Table Expressions (CTEs), una característica recien introducida en Sql Server 2005. En su momento lo vi, pero no había tenido la necesidad de usarlos hasta ahora. Estos sirven para hacer queries sobre esquemas jerarquicos. Veamos este ejemplo real. Nosotros tenemos una lista de Nodos que representan categorías y pueden tener subcategorías y sub-subcategorias con N niveles de profundidad.

Si supieramos que sólo vamos a mezclar marcas con categorías y estas últimas con líneas, podríamos resolver todo en un par de joins, el problema viene cuando el nivel de profundidad es "ilimitado".

Este es el código con CTE en Sql Server 2005. 

WITH TreeView(NodeId, Name, Description, Level, Father, Selectable, Enabled)--Definimos nuestro CTE
AS
(
    -- Definimos la raíz o miembro anclar
    SELECT NodeId, Name, Description, 0 as Level, Father, Selectable, Enabled
    FROM Nodes
    WHERE NodeId = @NodeId
    UNION ALL
    -- Definimos el miembro recursivo haciendo referencia a nuestro CTE
    SELECT n.NodeId, n.Name, n.Description, tv.Level+1 as Level, n.Father, n.Selectable, n.Enabled
    FROM Nodes n
    INNER JOIN TreeView tv on n.Father = tv.NodeId
)

-- Ahora simplemente consultamos nuestro CTE

SELECT * from TreeView
Order by Level

Al final terminaremos con algo como esto.

image

Lo más importante a notar es como al definir nuestro miembro recursivo hacemos referencia a TreeView.NodeId para poder encontrar la relación padre-hijo. Esa es la clave de todo.

Como funciona, se obtiene el miembro ancla y se une por cada uno de estos el resultado de ejecutar el query para los miembros recursivos, una vez que este último no regresa filas en automático termina la recursión para continuar con una siguiente fila así hasta acabar con todos.

Lamentablemente en este proyecto aún estamos usando Sql Server 2000, así que esto no sirvio más que para que me diera coraje seguir batalalndo con Cursores, aquí esta la solución que ya tenía para 2000, no la voy a describir mucho, sólo dire que nos obliga a tener un procedure, cursores y tablas temporales con la limitante de sólo 32 niveles de anidación (por las llamadas a Sprocs):

ALTER procedure [dbo].[GetChildsForNode]
    @father integer,
    @level integer
as
begin
/*
* Obtiene de manera recursiva todos los nodos hijos y los guarda en una tabla temporal #nodes
*
* Autor: Miguel Madero
* Fecha de creación: 02/05/2008
* Fecha de última modificación: 02/05/2008
*
*/
    -- Insert current level nodes
    insert into #nodes
    select * from Nodes
    where father = @father and [level] = @level and [Enabled] = 1

    -- Each insertedNode will now be the father
    declare @newFather integer
    declare @newLevel integer
    -- We wanna go down 1 level 
    set @newLevel = @level + 1
    declare cursorNodesByLevel cursor for
        select Father from #nodes where [level] = @level and [Enabled] = 1
    open cursorNodesByLevel
    -- Each insertedNode will now be the father
    Fetch Next from cursorNodesByLevel into @newFather
    WHILE (@@FETCH_STATUS<>-1)
    BEGIN
        IF(@@FETCH_STATUS<>-2)
        BEGIN
            -- We call ourselves with the new father and newLevel
            exec GetChildsForNode @newFather, @newLevel
        END
        -- Each insertedNode will now be the father
        Fetch Next from cursorNodesByLevel into @newFather
    END
    -- Cleanup
    Close cursorNodesByLevel
    Deallocate cursorNodesByLevel
end

Lo usarías de la siguiente forma:

CREATE TABLE #nodes
    (
        NodeId int NOT NULL,
        [Name] varchar(50) NOT NULL,
        [Description] varchar(500) NULL,
        [Level] int NOT NULL,
        Father int NULL,
        Selectable bit NOT NULL,
        [Enabled] bit NOT NULL
    )
        -- We fill the ChildNodes which will be left in the temp table
        exec GetNodeWithChilds @nodeId
       

        -- Now we do something with that like get the products on any node

        select p.ProductId, p.NodeId, ad.descr, ad.precioUsuario, ad.disponiblesUsuario
        from Products p
        join ProductsAspel pa on p.ProductId = pa.ProductId
        join ArticulosDisponibles ad on ad.clv_art = pa.AspelKey
        where usId = @usId and NodeId in (Select NodeId from #nodes)
        order by descr, clv_art,marcnombre, desc_lin
    -- Cleanup
    Drop Table #nodes

Por cierto Sql Server 2008 tiene un nuevo Hierarchy Type del que les platicaremos en los eventos de presentación del producto que estaremos haciendo por todo México.

Más información de CTE

domingo, febrero 03, 2008

Instalar Northwind en SqlExpress

Es muy fácil, vayan e instalen esto.

En c:\Sql Server 2000 Sample Databases hay un script, corranlo y listo.

Para más info vean este post Smart Client Data : Installing Northwind on SqlExpress

Technorati Tags: ,