December 2007 - Artículos - Percy Reyes's Technical Blog

December 2007 - Artículos

PIVOT es uno de los nuevos operadores incluídos en SQL Server 2005 que podemos usar en la cláusula FROM para rotar filas en columnas y conseguir informes de tabla cruzada en un formato tabular, más presentables, claramente resumidos y descriptivos.  En un post anterior tratamos un ejemplo acerca del uso de PIVOT. Bien, la forma cómo se usó PIVOT en dicho ejemplo viene a ser su implementación estática, asi es, vimos cómo implementar PIVOT estático, además se dejó algunos recursos para familiarizarse con el tema. Por ejemplo, usemos la base de datos AdventureWorks y analicemos un poquito el resultado del siguiente query que más adelante pivotearemos:

SELECT CustomerID,YEAR(DueDate) [Year], TotalDue 
FROM Sales.SalesOrderHeader
ORDER BY CustomerID

En los resultados podemos apreciar que cada cliente hizo muchas ventas por año, entre los años 2001 y 2004.

CustomerID    Year        TotalDue
-----------      ----------- ---------------------
1                   2001        14603,7393
1                   2001        26128,8674
1                   2002        37643,1378
1                   2002        34722,9906
2                   2002        10184,0774
2                   2002        5469,5941
2                   2003        1739,4078
2                   2003        1935,5166
2                   2003        3905,2547
2                   2003        4537,8484
2                   2004        4053,9506
2                   2004        908,3199
3                   2004        17051,8292
3                   2004        34873,5257
...                 .....         .......(Continúa)

En fin, el uso de PIVOT estático tiene la clara desventaja de limitar en número de columnas desglosadas para mostrar información de la columna pivoteada, por ejemplo, de acuerdo al resultado del query anterior, si queremos pivotear la columna TotalDue, y desglosar la información en 4 columnas, es decir, pivotear TotatlDue para los años del 2001 al 2004, se tendría que especificar cada uno de esos valores dentro de la consulta, cuestión que será sencillo y fácil dado que sólo tendremos que indicar 4 valores.

SELECT CustomerID, [2001] AS '2001', [2002] AS '2002', [2003] AS '2003', [2004]  AS '2004'
FROM (
      SELECT CustomerID,YEAR(DueDate) [Year], TotalDue FROM Sales.SalesOrderHeader
     ) pvt
PIVOT (SUM(TotalDue) FOR [Year] IN ([2001],[2002],[2003],[2004])) AS Child
ORDER BY CustomerID

Hasta aquí todo parece estar bien, y claro que es así. He aquí los resultados:

CustomerID   2001                       2002                  2003                    2004
-----------      --------------------- --------------------- --------------------- ---------------------
1                 40732,6067            72366,1284          NULL                    NULL
2                 NULL                     15653,6715          12118,0275           4962,2705
3                 39752,8421            168393,7021        219434,4265          51925,3549
4                 NULL                     263025,3113        373484,299           143525,6018
5                 NULL                     33370,6901          60206,9999           20641,1106
6                 NULL                     NULL                   668,4861               2979,3473
7                 NULL                     6651,036             3718,7804              NULL
8                 NULL                     NULL                  19439,2466            10900,0347
9                 NULL                     320,6283             11401,5975            5282,8652
10               NULL                     96701,7401          291472,2172          204525,9634
11              40350,4474             24300,4254          NULL                     NULL
12              NULL                      117419,735          191505,7911          29091,7653
14              NULL                      NULL                   7348,0162              1446,6848
...             ........                    ...........             .............          ......(Continúa)

Sin embargo,  hay un problema, ¿Sabes cual es el problema? -> la implementación de PIVOT estáticos no es escalable, ya que los valores de la columna que se desglosa para llenar información de la columna pivoteada puede aumentar, es decir, conforme vaya pasando el tiempo la ventas se seguirán llevando acabo teniendo años superiores al 2004, como 2005, 2006,..., y si quisiera (pasados 2 años por ejemplo) pivotear todas las ventas para todos los años hasta la fecha tendría que modificar el query, modificar e indicar los años adicionales a desglosarse.

Otro desventaja sería por ejemplo, ¿Qué pasa si tengo que pivotear un columna que tiene 30 valores diferentes?, pues usando PIVOT estático tendría que estar indicando manualmente cada uno de los 30 valores dentro del query, cosa que se torna fastidiosa, aburrida, y sobre todo trabajosa, y esto es poco, pueden ser más valores, por ejemplo 100, me muero!.

Todos estos líos se solucionan mediante PIVOT dinámico, que no viene a ser más que una implementación algoritmica para capturar dinámicamente los valores de la columna a desglosare para la columna pivoteada e insertarlo dentro de la cadena final del query a ejecutarse usando sp_executesql (SQl Dinámico). Dejo el query a su merced!, podeis masticarlo a su gusto!.

DECLARE @TableYears AS TABLE([Year] INT NOT NULL)
DECLARE @Year INT, @YearsPVT NVARCHAR(MAX) 
INSERT INTO @TableYears  SELECT DISTINCT YEAR(DueDate) AS [Year] FROM  Sales.SalesOrderHeader 
SET @Year = (SELECT MIN([Year]) FROM @TableYears)
SET @YearsPVT=N''
WHILE @Year IS NOT NULL
BEGIN
  SET @YearsPVT = @YearsPVT + N',['+ CONVERT(NVARCHAR(10),@Year) + N']'
  SET @Year = (SELECT MIN([Year]) FROM @TableYears WHERE [Year]>@Year)
END
SET @YearsPVT = SUBSTRING(@YearsPVT,2,LEN(@YearsPVT))
PRINT @YearsPVT
DECLARE @SQL NVARCHAR(MAX)
 SET @SQL = N'SELECT *
            FROM ( 
                SELECT CustomerID,YEAR(DueDate) [Year], TotalDue FROM Sales.SalesOrderHeader
                ) pvt
            PIVOT (SUM(TotalDue) FOR [Year] IN (' + @YearsPVT + ')) AS Child
            ORDER by CustomerID'
 
EXECUTE sp_executesql @SQL

 

Los resultados del query con PIVOT dinámico serán los mismos que los conseguidos usando PIVOT estático :D. Funciona!, sí señor! :). Ahora vayamos con otro ejemplo, algo parecido, pero ahora será usando la base de datos Northwind, pivoteamos la columna Monto y lo desglosaremos de acuerdo al CategoryName:

--ESTO ES LO QUE PIVOTEAMOS!
SELECT P.ProductID, C.CategoryName, OD.UnitPrice * OD.Quantity AS Monto  
FROM Products P 
    INNER JOIN dbo.[Order Details] OD
ON P.ProductID=OD.ProductID
   INNER JOIN Categories C
ON C.CategoryID=P.CategoryID

Estos serán los resultados:

ProductID   CategoryName         Monto
-----------    ------------------         ------------------
11              Dairy Products        168,00
42              Grains/Cereals        98,00
72              Dairy Products        174,00
14              Produce                  167,40
51              Produce                  1696,00
41              Seafood                  77,00
51              Produce                  1484,00
65              Condiments            252,00
22              Grains/Cereals        100,80
57              Grains/Cereals        234,00
65              Condiments            336,00
20              Confections            2592,00
33              Dairy Products        50,00
...              ................       .....(Continúa)

Pivoteando el resultado anterior, usando PIVOT "estático":  

--PIVOTEO
SELECT ProductID, [Beverages], [Condiments], [Confections], [Dairy Products], 
       [Grains/Cereals], [Meat/Poultry],[Produce],[Seafood] 
FROM 
(
    SELECT P.ProductID, C.CategoryName, (OD.UnitPrice * OD.Quantity) AS Monto
    FROM Products P 
        INNER JOIN dbo.[Order Details] OD
            ON P.ProductID=OD.ProductID
        INNER JOIN Categories C
            on C.CategoryID=P.CategoryID
) PIV
PIVOT (SUM(Monto) FOR  CategoryName IN ([Beverages], [Condiments], [Confections], [Dairy Products], 
       [Grains/Cereals], [Meat/Poultry],[Produce],[Seafood])) AS Child

Seguro que ya hiciste un copy/paste, je je je, y si aún el nivel de compatibilidad de la base de datos Northwind es 80 o inferior de seguro te encontrarás con este error:

Msg 325, Level 15, State 1, Line 13
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

El operador PIVOT sólo trabaja con base de datos cuyo nivel de compatibilidad mayor o igual a 90 (SQL Server 2005). Northwind es una base de datos creada con SQL Server 2000, y por lo tanto su nivel de compatibilidad es 80. Para solucionar este problema debemos obviamente cambiar dicho nivel de compatibilidad a 90.

EXEC dbo.sp_dbcmptlevel @dbname=N'Northwind', @new_cmptlevel=90

Ahora si, volvemos a ejecutar el query y tendremos (Muestro resultados parciales):

 ProductID   Beverages         Condiments       Confections     Dairy Products   Grains/Cereals    Meat/Poultry          Produce               Seafood
-----------      --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
1                  14277.60              NULL                  NULL                  NULL                  NULL                  NULL                  NULL                  NULL
2                  18559.20              NULL                  NULL                  NULL                  NULL                  NULL                  NULL                  NULL
3                  NULL                  3080.00                NULL                  NULL                  NULL                  NULL                  NULL                  NULL
4                  NULL                  9424.80                NULL                  NULL                  NULL                  NULL                  NULL                  NULL
5                  NULL                  5801.15                NULL                  NULL                  NULL                  NULL                  NULL                  NULL
6                  NULL                  7345.00                NULL                  NULL                  NULL                  NULL                  NULL                  NULL
7                  NULL                  NULL                    NULL                  NULL                  NULL                  NULL                22464.00              NULL
8                  NULL                  13760.00             NULL                  NULL                  NULL                  NULL                  NULL                  NULL
9                  NULL                  NULL                    NULL                  NULL                  NULL                8827.00               NULL                  NULL
10               NULL                  NULL                    NULL                  NULL                  NULL                  NULL                  NULL                  22140.20
11               NULL                  NULL                    NULL                13902.00              NULL                  NULL                  NULL                  NULL
12               NULL                  NULL                    NULL                12866.80              NULL                  NULL                  NULL                  NULL
13               NULL                  NULL                    NULL                  NULL                  NULL                  NULL                  NULL                  5234.40
14               NULL                  NULL                    NULL                  NULL                  NULL                  NULL                  8630.40               NULL
15               NULL                  1813.50                NULL                  NULL                  NULL                  NULL                  NULL                  NULL
16               NULL                  NULL                  18748.05              NULL                  NULL                  NULL                  NULL                  NULL
17               NULL                  NULL                    NULL                  NULL                  NULL                35482.20              NULL                  NULL
18               NULL                  NULL                    NULL                  NULL                  NULL                  NULL                  NULL                  31987.50
19               NULL                  NULL                 6159.50                NULL                  NULL                  NULL                  NULL                  NULL
20               NULL                  NULL                 23635.80              NULL                  NULL                  NULL                  NULL                  NULL

Lograremos lo mismo, esta vez usando PIVOT dinámico:

--pivot dinámico
DECLARE @CatPVT AS NVARCHAR(MAX), @Categorias AS varchar(20)
DECLARE @CatID INT 
SET @CatID=(SELECT MIN(CategoryID) FROM Categories)
SET @Categorias = ( SELECT CategoryName FROM Categories WHERE CategoryID = @CatID)
SET @CatPVT = N''
WHILE @Categorias IS NOT NULL
BEGIN
  SET @CatPVT = @CatPVT + N',['+ @Categorias +N']'
  SET @Categorias = (SELECT TOP(1) CategoryName 
                     FROM Categories WHERE CategoryID > @CatID 
                     ORDER BY CategoryID ASC)
  SET @CatID=(SELECT MIN(CategoryID) FROM Categories Where Categoryname=@Categorias)
END
print @CatPVT
SET @CatPVT = SUBSTRING(@CatPVT, 2, LEN(@CatPVT))
print 'ok'
print @CatPVT
 
DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *  
            FROM (SELECT P.ProductID, C.CategoryName, (OD.UnitPrice * OD.Quantity) AS Monto
                  FROM Products P 
                    INNER JOIN dbo.[Order Details] OD
                        ON P.ProductID=OD.ProductID
                    INNER JOIN Categories C
                    ON C.CategoryID=P.CategoryID
            ) PIV
            PIVOT (SUM(Monto) FOR  CategoryName IN ('+ @CatPVT  + ')) AS Child'
 
EXEC sp_executesql @sql


No debo recordarles que dentro de la implementación de PIVOT dinámico podrían usarse tablas temporables, variables del tipo TABLE y/o  CTEs en lugar de emplearse subconsultas, en todo caso los resultados serán los mismos, sin embargo el rendimiento podría ser mejor, y la lógica se simplicaría bastante.  En fin, es bastante sencillo, y a ver si en otro post explico algo al respecto.

If you like this post then follow @percyreyes on twitter.

Publicado por Percy Reyes | 35 comment(s)

Ciertas veces, para llevar acabo algunas tareas administrativas, tenemos la necesidad de saber cuales son las tablas (u otros objetos: Vistas, UDFs, Stored Procedures) de las cuales depende determinada tabla, o cuales son las tablas (u otros objetos) que dependen de determinada tabla. Existen dos formas, la primera es dando click sobre una tabla cualquiera y luego click derecho para finalmente escoger "'View Dependencies'". En este post voy a explicar como llevar acabo esta tarea mediante T-SQL.

Existen dos vistas del sistema: sys.objects y sys.sysreferences, que podemos consultar para extraer dicha información haciendo una operación JOIN entre estas en base a la dos columnas claves:kfeyid y rkeyid.

  • fkeyid -> Id. de la tabla que hace referencia
  • rkeyid -> Id. de la tabla a la que se hace referencia

La vista sys.objects contiene información para cada objeto(excepto triggers DDL y DML) creada dentro de la base de datos definido dentro del esquema del usuario. Usando sys.sysreferences podemos acceder a la metadata de las definiciones de todas las restricciones FOREIGN KEY a las columnas involucradas en una base de datos definida también dentro del esquema del usuario.

Por ejemplo si deseamos ver las tablas dependientes sobre la tabla Products (DB Northwind):

   1: SELECT S.[name] AS 'Tablas Dependientes'
   2: FROM sys.objects S INNER JOIN sys.sysreferences R 
   3:   ON S.OBJECT_ID = R.rkeyid
   4: WHERE S.[type] = 'U' AND
   5:       R.fkeyid = OBJECT_ID('Products')

Si deseamos sabes cuales son las tablas, las cuales dependen de la tabla Products: 

   1: SELECT S.[name] AS 'Tablas que depende de Products'
   2: FROM sys.objects S INNER JOIN sys.sysreferences R 
   3:     ON S.object_id = R.fkeyid
   4: WHERE S.[type] = 'U' 
   5:      AND R.rkeyid = OBJECT_ID('Products')

Este código podriamos generalizarlo dentro de un stored procedure y haciéndolo totalmente dinámico no sólo para consultar información acerca de tablas sino de otros objetos como Vistas, SPs, UDF, etc. Cuestión que usted podrá hacerlo rápidamente ;). En fin, eso fue lo que queria aportar, espero sea de utilidad!,

If you like this post then follow @percyreyes on twitter.

Publicado por Percy Reyes | 3 comment(s)

Boost SQL Server Priority es una de las tantas opciones que podemos configurar en un servidor SQL Server para mejorar la performance del mismo. La opción Boost SQL Server Priority (Aumento de Prioridad) permite cambiar la prioridad de los procesos de Windows Server (Microsoft Windows 2000 ó Windows 2003) y de SQL Server.

Por defecto, Boost SQL Server Priority se encuentra ajustado en 0 lo cual hace que SQL Server se ejecute con una prioridad normal si es que estuviera ejecutándose en un equipo uniprocesador o un equipo simétrico de multiprocesador (SMP). Por otra parte, si establece Boost SQL Server Priority en 1, entonces el proceso de SQL Server se ejecutará con mayor prioridad. Esto puede mejorar el rendimiento de un servidor que está ejecutando sólamente instancias de SQL Server y no otras aplicaciones, es decir, se recomienda configurar este valor a 1 para servidores dedicados para SQL Server.

Esta opción Boost SQL Server Priority puede configurarse de dos maneras. La primera forma es mediante SQL Server Management Studio, para esto vea Cómo establecer la opción priority boost (SQL Server Management Studio).

priority boost

La segunda manera de ajustar este valor es mediante código T-SQL, y es lo que a continuación paso a demostrar. Lo que se debe saber hasta ahora es que Boost SQL Server Priority es una "opción avanzada" y para poder tener alcance a este nivel, el siguiente paso es Habilitar Opciones Avanzadas, ya que por defecto está deshabilitado.

sys.sp_configure 'show advanced options', 1

Ahora debemos correr la sentencia RECONFIGURE para confirmar o actualizar el cambio.

RECONFIGURE
GO

Hecho todo lo anterior, pasamos a configurar Boost SQL Server Priority:

EXEC sys.sp_configure N'priority boost', N'1'
GO

Confirmamos o guardamos cambios:

RECONFIGURE WITH OVERRIDE
GO

Tenga mucho cuidado y bastante criterio cuanto decida manipular esta opción. Si está ejecutando varias instancias de SQL Server en un equipo, y si activa Boost SQL Server Priority sólo para algunas instancias, entonces el desempeño del resto de instancia serán afectados negativamente. Sin embargo, si el sistema está dedicado a SQL Server y las tareas de lecura y escritura (Disk I/O) tienden a ser pesadas, entonces debería habilitar esta opción para obtener ganancias de rendimiento sustanciales. Algo importante en este tema es no configurar Boost SQL Server Priority para servidores agrupados que ejecutan SQL Server 7.0, SQL Server 2000 o SQL Server 2005; y de nuevo repito: no recomiendo el uso de esta opción en máquinas de un sólo procesador, salvo tenga configurado un servidor dedicado para SQL Server.

If you like this post then follow @percyreyes on twitter.

Publicado por Percy Reyes | 2 comment(s)

 

Hoy día he podido darme un tiempito para contarles que este último fin de semana ha sido una de las mejores en mi carrera y vida personal. Estuve muy feliz de oreja a oreja. Todo esto empezó luego de aprobar el examen 70-444: PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005. Desde luego la satisfacción es grande después de aprobar dicho examen gracias al esfuerzo y sacrificio durante los últimos años que vengo jugando con este grandioso producto de Microsoft: SQL Server 2005.

Contándoles un poco más al detalle. El día jueves 6 de Diciembre 2007 viajé a la ciudad de Lima, con dos fines, el primero de tomar los dos examenes, tanto 70-443 y 70-444, que me permitieran obtener la certificación MCITP Database Administrator, y el segundo de ir al concierto de Soda Stereo. Ambas cosas pude realizarlo exitosamente gracias a mi esfuerzo y al apoyo de muchas personas estos últimos meses.

El día viernes 07 Diciembre 2007 en horas de la mañana, recien bajadito del bus, y sin desayunar jejeje, tomé el examen 70-444, el resultado fue positivo, pasé el examen. El sábado por la tarde (ya contento:-)) tuve que hacer 7 horas de cola para poder ingresar al Estadio Nacional, donde en horas de la noche, 9 pm se llevaría acabo el super mega concierto de Soda Stereo. El Concierto duró 2 horas y 50 minutos. Sencillamente espectacular y recontra geek, no podía creerlo, yo estaba en el concierto de mi banda favorita de música rock en español. woow!, que más puedo pedir?.

Luego de disfrutar de una espectacular concierto (donde canté, grité y salté cada una de las canciones), el día Lunes 10 de Diciembre tomé el examen 70-443: PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005. El resultado también fue positivo, la felicidad era inmensa, sonrisa de oreja a oreja.

El examen que más complicado me pareció es el 70-443, todas las preguntas consisten de resolución de casos reales, no son simplemente preguntas teóricas, sino resolver cada caso planteado en base a ciertos criterios. El examen 70-444 es un poco más sencillo, pero eso no deja de ser todo un reto aprobarlo.

En fin, estos era examenes que he venido postergando durante este año, pero que finalmente me animé a tomarlos, siendo así que desde ahora ya podré usar en mi blog el logo de esta certificación. =D.

If you like this post then follow @percyreyes on twitter.

Publicado por Percy Reyes | 23 comment(s)

El día de hoy se llevó acabo el evento TechNet Summit 2007 Trujillo Edition con total normalidad en el auditorio de la Universidad Privada Alas Peruanas (Sucursal Trujillo).  Básicamente el evento consistió en 4 sesiones, dos de las cuales fueron relacionados a Windows Server 2008 (Expositor: Nelson Angeles), y las otras dos de SQL Server 2005: Seguridad y SQL Server 2008 (Más allá de lo relacional) fueron dadas por mi persona. Dejo algunas fotos del evento, y en esta primera parte estoy yo dando la charla de SQL Server 2005 : Seguridad - Revisión en Profundidad :D.

01-12-07_092401-12-07_1004

Este evento fue realizado y organizado por las comunidades SQL Server Perú y CAM UAP Trujillo, y bueno, para serles sincero el evento estuvo interesante, no tan sólo por los temas hablados sino también porque los asistentes participaron bastante, pudo apreciarse al final que la gente se quedó contenta y conforme con las charlas dadas, y lo bueno es que se pudo dar a conocer nuevas características y funcionalidades próximas a ser liberadas tanto para Windows Server 2008 y SQL Server 2008.  (En las fotos aún sigo dando charla, y como también pueden apreciar a Nelson Angeles).

01-12-07_100801-12-07_1205

Todo el material de este evento ya pueden descargarlo, más abajo dejo el link. Además les invito a bajar este WebCast acerca de SQL Server 2008 (Generalidades), que lo distruten!. Ah!, por cierto, también pueden bajar todo el material de todas las sesiones de TechNet Summit 2007 Central que se dió en Lima.

Descargar Presentaciones y Demos: Material_TechNetSummit2007_TrujilloEdition.

Ojalá que más adelante podamos seguir organizando más eventos en esta linda ciudad, y esperamos asistan todos!. Ya estaremos comunicándoles! :P.

If you like this post then follow @percyreyes on twitter.

Publicado por Percy Reyes | 5 comment(s)

Esta vez aparezco sólo para contarles que desde hoy podrán leerme a través de http://www.sqlgeek.es , dominio que redireccionará a la página principal de mi blog.

Espero que sigan visitando este rinconcito de SQL Server, saludos a todos y ya estaremos con nuevos post de SQL Server 2005, y principalmente de SQL Server 2008 ;).

If you like this post then follow @percyreyes on twitter.

Publicado por Percy Reyes | con no comments