Exploring ROW_NUMBER function in SQL Server 2005

Some of the SQL Server 2005 new features are the Ranking functions which are used to analyse data. Among these functions we have, for instance, ROW_NUMBER which helps to enumerate every row of a result set in a simpler way. Before showing the illustration of it, I will share the syntax:

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )
where:

• partition_by_clause: Divide the result set generated by FROM clause into partitions on which ROW_NUMBER will work.
• order_by_clause: Determine the order for ROW_NUMBER.

Now the illustration: we are going to enumerate the details of the sales based on the price.

SELECT ROW_NUMBER() OVER( ORDER BY UnitPrice DESC ) AS Item, 
SaleOrderID, ProductID, Quantity,UnitPrice 
FROM OrderDetail 

Item    SaleOrderID     ProductID   Quantity    UnitPrice
----    ------------    ---------   --------    -----------
1        00000015       FX8S           1        7621.71
2        00000016       FX8E           1        3601.77
3        00000016       FX5P           1        2207.87
4        00000014       FX5G           1        6507.82
5        00000018       FX21           1        631.62
6        00000019       FX19           1        751.62

Another example of it would be using PARTITION clause for partitions result sets by ProductGroupID and then ROW_NUMBER will be applied based on ProductStock. Therefore, we are writing a query like this:

SELECT ROW_NUMBER() OVER(PARTITION BY ProductGroupID ORDER BY ProductStock ASC) AS Item, 
ProductID, ProductDesc, ProductGroupID, ProductStock 
FROM Products

Item  ProductID    ProductDesc                       ProductGroupID    ProductStock

----  ---------    ------------------------------    --------------    -----------
1     DS02         MAINBOARD 845 PEMYL               G003               -1
2     DS06         MAINBOARD 850 EV2                 G003               10
1     DS07         TECLADO 104 MULTIMEDIA32          G004               20
1     DS12         SWITCH 5 PORT 10/100, 220V        G006               26
1     DS45         MUEBLE DE OFICINA                 G007               34
1     DS76         DISCO DURO                        G008               20
1     DS07         IMPRESORA LASER                   G009               10
1     DS45         MEMORIA STICK                     G010               2
2     DS29         MEMORIA VIDEO                     G010               5
1     DS08         TINTA CANON BCI24 NEGRO           G011               12
1     DS41         WEB CAM ALTIOR MODELO B02         G012               0
1     DS74         MONITOR DE 15                     G016               6
1     DS83         IMPRESORA MATRICIAL DFX5001       G018               12
2     DS84         IMPRESORA MATRICIAL DFX8502       G018               17
3     DS21         IMPRESORA MATRICIAL FX21944       G018               48

(15 row(s) affected)

As I mentioned before, this functionality is helpful, simple and easy to use it, so it does not worth a major explanation. That is all for now. Thanks for reading. Stay tuned.

The power of PIVOT in SQL Server 2005

With the arrival of SQL Server 2005, we have many new features available to make the most out of it. Today's post is going to be about one of the greatest new functionalities, that is, the powerful PIVOT sentence which can be used for converting rows into columns (and vice versa) in order to have a vision much more structured and meaningful of the data result sets.

So, moving on the example, we are going to use some tables of the 'Northwind' database like 'Orders', 'Orders Details', 'Employees', and 'Products'. The objective is to show the total value sold by each employee for the following products: 'Alice Mutton', 'Filo Mix', 'Flotemysost', 'Geitost', 'Konbu', 'Maxilaku', 'Pavlova', 'Tofu', and  'Vegie-spread'. Well, in SQL Server 2000, it can be done by using INNER JOINs and 3 Subqueries (if you had more tables to query, it would become a complex work to do). As a result of this, not only does the query become complex, but also it is not the most optimum choice.

SELECT Ventas1.*

FROM (SELECT Empleado, 

        (SELECT Products.ProductName FROM Products    

          WHERE Products.Productid=Ventas.ProductID) Producto, 

                SUM (ventas.ValorVendido)Total

      FROM (SELECT (C.lastname + ‘ ‘ + c.firstname)Empleado,ProductId,

                 (OD.Unitprice*OD.Quantity) [ValorVendido]

            FROM [Order Details] OD

             INNER JOIN ( [Orders] O        

              INNER JOIN Employees C

            ON O.Employeeid=C.Employeeid)

             ON OD.Orderid=O.OrderID) Ventas

GROUP BY Empleado, Productid) Ventas1

WHERE Producto IN(‘Alice Mutton’,‘Filo Mix’,‘Flotemysost’,‘Geitost’,

‘Konbu’,‘Maxilaku’,‘Pavlova’,‘Tofu’,‘Vegie-spread’)

Here we can see the partial result:



Having seen how difficult some queries can become by using old-fashioned and traditional techniques, now we are going rewrite the query to use PIVOT. Here is the code:

SELECT Empleado, [Alice Mutton],[Filo Mix],[Flotemysost],

      [Geitost],[Konbu], [Maxilaku],[Pavlova],[Tofu],[Vegie-spread]

FROM (SELECT Empleado, 

             (SELECT PRoducts.Productname FROM Products

             WHERE Products.Productid=Ventas.Productid) Producto,

                 SUM (ventas.ValorVendido) Total

      FROM (SELECT (C.lastname+ ‘ ‘ + C.firstname) Empleado, ProductID, 

               (OD.Unitprice*OD.Quantity) [ValorVendido]

            FROM [Order Details]OD

                INNER JOIN ([Orders] O

                INNER JOIN Employees C

             ON O.EmployeeID=C.EmployeeID

        )

ON OD.OrderID=O.OrderID) Ventas

GROUP BY Empleado,Productid) ventas1

PIVOT(SUM(total) FOR [Producto] IN ([Alice Mutton],[Filo Mix],[Flotemysost],

[Geitost],[Konbu],[Maxilaku],[Pavlova],[Tofu],[Vegie-spread])) AS pvt

Only after successfully familiarising with PIVOT will we realise how easy and practical is use it so as to save not only time but also costs. More details about it can be found by checking the following resource http://msdn2.microsoft.com/es-es/library/ms177410.aspx.

That is all for now until next post. Let me know any remarks and experience you have may using PIVOT. Thanks for reading. Stay tuned.

How to connect to SQL Server 2005 much faster via SQL Management Studio

While connecting with SQL Server 2005, we have to deal with the 'splash' screen and then indicate, basically, the following information such as service type, server name, and authentication type. This process may sometimes slow us down, nevertheless, we can optimise it as long as we know where we want to establish a connection. Put differently, we can save some time by using 'sqlwb.exe' tool which allows to start SQL Management Studio rapidly. This tool has some parameters we can use them to speed up the logon process to SQL instances. Here I am coming up with an illustration how to use 'sqlwb.exe' with some basic parameters. For instance,  if we would like to connect to the 'TestDB' database on 'SVPRDB1' server and with Windows Authentication, it can be done in this way:

sqlwb.exe -E -S SVPRDB1 -d TestDB -nosplash

Only after successfully executing that will we connect directly to 'TestDB' database without specifying manually the parameters and also without seeing 'the splash' screen showing the presentation of SQL Management Studio version. Now we can also customise the shortcut as it is shown in the following picture:



As earlier I said, this is the fastest way to connect to SQL Server which will allow to save some seconds. It couldn't have been simpler. That is all for now. Thanks for reading. Stay tuned.