Month: September 2006

Bob Beauchemin has just spent a few days in TechEd Hong Kong, giving a few talks on various aspects of SQL Server 2005 and some topics related to .NET development. He has also offered a full-day pre-conference seminar on SQL Server 2005 features like T-SQL enhancements, T-SQL vs SQLCLR, tuning, etc.

To me, the topic about "Next generation data access in .NET application with ADO.NET vNext" is exciting, while Bob has just offered a summary of ADO.NET vNext on his blog "What is ADO.NET vNext anyway?" right after his talk! [;)] More information about ADO.NET vNext can also be found at MSDN "Next-generation data access: making the conceptual level real", while Community Technology Preview is available for download at ADO.NET vNext CTP August 2006.

RANK(), DENSE_RANK() and NTILE() are newly added functions in SQL Server 2005 T-SQL syntax. Another ranking function is ROW_NUMBER() that I have blogged earlier.

RANK() returns the rank of each row within the partition of a result set. When there is a tie, the same rank is assigned to the tied rows.

For example, 1, 2, 3, 3, 3, 6, 7, 7, 9, 10

DENSE_RANK() works like RANK(), except that the numbers being returned are packed (do not have gaps) and always have consecutive ranks.

For example, 1, 2, 3, 3, 3, 4, 5, 5, 6, 7

NTILE(integer_expression) breaks the rows within a partition into groups, while the number of groups is specified by "integer_expression". This is useful when percentile rank is required.

NTILE(N) returns 1 for the rows in the first group, 2 for those in the second group, and returns N for the last (N-th) group. Each group contains the same number of rows, or, if the number of rows in a partition is not divisible by "integer_expression", lower-numbered groups (starting from 1, 2, …) will each contain one more row. For example,

NTILE(2): 1, 1, 1, 1, 1, 2, 2, 2, 2, 2
NTILE(3): 1, 1, 1, 1, 2, 2, 2, 3, 3, 3
NTILE(4): 1, 1, 1, 2, 2, 2, 3, 3, 4, 4
NTILE(5): 1, 1, 2, 2, 3, 3, 4, 4, 5, 5
NTILE(6): 1, 1, 2, 2, 3, 3, 4, 4, 5, 6

The basic syntax follows.

ROW_NUMBER()  OVER ([<partition_by_clause>] <order_by_clause>)

RANK()  OVER ([<partition_by_clause>] <order_by_clause>)

DENSE_RANK()  OVER([<partition_by_clause>]<order_by_clause>)

NTILE(integer_expression)  OVER ([<partition_by_clause>] <order_by_clause>)

==========
Putting it all together, the following query shows all ranking functions in action using the famous AdventureWorks OLTP database!

SELECT i.ProductID
  , ProductName = p.Name
  , i.LocationID
  , i.Quantity
  , RowNumber = ROW_NUMBER()
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity)
  , Quartile = NTILE(4) 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity)
  , Rank = RANK() 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity)
  , DenseRank = DENSE_RANK() 
                  OVER (PARTITION BY i.LocationID 
                        ORDER BY i.Quantity)
 FROM Production.ProductInventory i 
       INNER JOIN Production.Product p 
             ON i.ProductID = p.ProductID
 WHERE i.LocationID in (3, 4, 5)
 ORDER BY i.LocationID, RowNumber;

—– here comes the query result —–
(ProductName is intentionally removed for better presentation)

ProductID   LocID Quantity RowNumber  Quartile Rank DenseRank
———– —– ——– ———- —– ———- ——
492         3     17       1          1     1          1
496         3     30       2          1     2          2
493         3     41       3          2     3          3
494         3     49       4          3     4          4
495         3     49       5          4     4          4
494         4     12       1          1     1          1
492         4     14       2          1     2          2
493         4     24       3          2     3          3
496         4     25       4          3     4          4
495         4     35       5          4     5          5
317         5     158      1          1     1          1
318         5     171      2          1     2          2
351         5     179      3          1     3          3
319         5     184      4          1     4          4
952         5     192      5          1     5          5
400         5     260      6          1     6          6
815         5     265      7          1     7          7
401         5     283      8          1     8          8
352         5     300      9          1     9          9
488         5     318      10         1     10         10
477         5     323      11         1     11         11
476         5     324      12         1     12         12
949         5     336      13         1     13         13
487         5     337      14         2     14         14
950         5     342      15         2     15         15
332         5     344      16         2     16         16
945         5     347      17         2     17         17
948         5     347      18         2     17         17
951         5     348      19         2     19         18
802         5     350      20         2     20         19
803         5     356      21         2     21         20
804         5     363      22         2     22         21
399         5     366      23         2     23         22
398         5     372      24         2     24         23
320         5     372      25         2     24         23
484         5     374      26         2     26         24
481         5     374      27         3     26         24
479         5     390      28         3     28         25
816         5     406      29         3     29         26
327         5     408      30         3     30         27
819         5     409      31         3     31         28
482         5     427      32         3     32         29
485         5     427      33         3     32         29
818         5     428      34         3     34         30
821         5     432      35         3     35         31
817         5     443      36         3     36         32
820         5     446      37         3     37         33
486         5     515      38         3     38         34
480         5     515      39         3     38         34
483         5     531      40         4     40         35
316         5     532      41         4     41         36
321         5     540      42         4     42         37
330         5     548      43         4     43         38
329         5     558      44         4     44         39
328         5     568      45         4     45         40
323         5     568      46         4     45         40
324         5     568      47         4     45         40
478         5     568      48         4     45         40
331         5     574      49         4     49         41
322         5     587      50         4     50         42
350         5     622      51         4     51         43

==========
For more information

– NTILE { http://msdn2.microsoft.com/en-us/library/ms175126.aspx }

– RANK { http://msdn2.microsoft.com/en-us/library/ms176102.aspx }

– DENSE_RANK { http://msdn2.microsoft.com/en-us/library/ms173825.aspx }

– ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }

– Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }

– OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }

– ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }

– What's New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }

– AdventureWorks – SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }

ROW_NUMBER() is a new function that is added to the SQL Server 2005 T-SQL syntax. ROW_NUMBER() is used to assign ranks to the result of a query. The basic syntax is 

ROW_NUMBER()  OVER ([<partition_by_clause>]<order_by_clause>)

Here comes the first sample query that will return the top 10 selling items in the AdventureWorks OLTP database. A rank will be assigned based on the total sales “SUM(A.LineTotal)” of each item.
The “TOP 10” clause is used to obtain the top 10 rows based on “ORDER BY SUM(A.LineTotal) DESC” after the GROUP BY clause.
You should note that adding a clause like “WHERE RowNumber <= 10” or “WHERE ROW_NUMBER() <= 10” does *not* work here, as the ROW_NUMBER() function is done after the result set is obtained from the query.

SELECT TOP 10 RowNumber = ROW_NUMBER() OVER (ORDER BY SUM(A.LineTotal) DESC)
  , A.ProductID
  , ProductTotal = SUM(A.LineTotal)
  , B.ProductNumber
  , ProductName = B.Name
 FROM Sales.SalesOrderDetail AS A
  INNER JOIN Production.Product AS B
   ON A.ProductID = B.ProductID
 GROUP BY A.ProductID, B.ProductNumber, B.Name
 ORDER BY SUM(A.LineTotal) DESC

———- following is the result ———-

(ProductNumber and ProductName are intentionally removed for better presentation)

RowNumber            ProductID   ProductTotal
——————– ———– —————————————
1                    782         4400592.800400
2                    783         4009494.761841
3                    779         3693678.025272
4                    780         3438478.860423
5                    781         3434256.941928
6                    784         3309673.216908
7                    793         2516857.314918
8                    794         2347655.953454
9                    795         2012447.775000
10                   753         1847818.628000

==========
The following query returns the top 3 sales persons for each product.
“PARTITION BY” is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.

SELECT Z.*
 FROM (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
    , C.SalesPersonID
    , A.ProductID
    , ProductTotal = SUM(A.LineTotal)
    , B.ProductNumber
    , ProductName = B.Name
   FROM Sales.SalesOrderDetail AS A
    INNER JOIN Production.Product AS B
     ON A.ProductID = B.ProductID
    INNER JOIN Sales.SalesOrderHeader C
     ON A.SalesOrderID = C.SalesOrderID
   WHERE C.SalesPersonID IS NOT NULL
   GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name) Z
 WHERE Z.RowNumber <= 3
 ORDER BY Z.ProductID, Z.RowNumber

—– CTE (common table expression) can also be used for the query above —–

WITH Z
AS (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
    , C.SalesPersonID
    , A.ProductID
    , ProductTotal = SUM(A.LineTotal)
    , B.ProductNumber
    , ProductName = B.Name
   FROM Sales.SalesOrderDetail AS A
    INNER JOIN Production.Product AS B
     ON A.ProductID = B.ProductID
    INNER JOIN Sales.SalesOrderHeader C
     ON A.SalesOrderID = C.SalesOrderID
   WHERE C.SalesPersonID IS NOT NULL
   GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name)
SELECT Z.*
 FROM Z
 WHERE Z.RowNumber <= 3
 ORDER BY Z.ProductID, Z.RowNumber

———- following is part of the result ———-

(ProductNumber and ProductName are intentionally removed for better presentation)

RowNumber            SalesPersonID ProductID   ProductTotal
——————– ————- ———– —————————————
1                    276           707         10859.642388
2                    277           707         10410.012222
3                    285           707         9890.467534
1                    276           708         11677.535352
2                    277           708         11673.985659
3                    285           708         10129.459479
1                    279           709         1141.795500
2                    276           709         875.211250
3                    277           709         826.785950

 

— (result is cut) —

===== For more information =====

– ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }

– OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }

– ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }

– Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }

– What’s New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }

– AdventureWorks – SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }

– WITH common table expression { http://msdn2.microsoft.com/en-us/library/ms175972.aspx }

 

===== Other posts that point here =====

~ Pagination in MSSQL 2005

http://blog.pengoworks.com/index.cfm/2008/6/10/pagination-your-data-in-mssql-2005

VSTO 2005 SE Beta has just been released, and the most exciting news is that managed Add-Ins for Office 2003 applications are now supported! (Yes. I mean Office 2003, and this is *not* a typo!)

VSTO 2005 SE Beta can be downloaded at { http://www.microsoft.com/downloads/details.aspx?FamilyId=5012A573-0D84-4E39-983C-CA22F2107B07&displaylang=en }, while the corresponding runtime can be found at { http://www.microsoft.com/downloads/details.aspx?FamilyId=5248A2D2-FC62-4A44-82CB-AE73C2FC7146&displaylang=en }.

For more information, visit { http://msdn.microsoft.com/office/tool/vsto/2005SE }

During the installation of SQL Server products and service packs, the password(s) of system administrator (sa) and/or SQL Server Sevices domain account may be stored as clear text or weakly encrypted readable format in the SQL Server Setup files and/or Setup.iss file. These files can be found in the following locations / files, and should be removed when the files are no longer required.

– %Windir% folder
– %Windir%\Temp folder
– Temp folder (as specified in System Control Panel –> Advanced –> Environment Variables)
– %SystemDrive%\MSSQL7\Install\or\Tools folder
– Sqlstp.log
– Sqlsp*.log
– Setup.iss
– Drive:\Program Files\Microsoft SQL Server\MSSQL\Install folder
– Drive:\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Install folder
– Remsetup.ini (at %Windir%)
– remote install script (RemoteComputerName_InstanceName.iss) at %Windir% folder

Users are suggested to do the following on or after the installation of SQL Server products and service packs.
– Copy *.iss file (for unattended installation) to a security-enhanced location that is not in searchable folders.
– Use Microsoft Windows NT Security Authentication to install SQL Server products and service packs.
– Use LocalSystem account to configure the SQL Services
– After the installation of SQL Server service packs, change the SQL Server system administrator (sa) password and SQL Service domain account password.
– Use the Killpwd.exe utility to clear the setup files. Killpwd utility automates scanning for the setup files and removes the passwords from them. Killpwd can be found at http://www.microsoft.com/downloads/details.aspx?amp;amp;displaylang=en&familyid=7BDA4AE4-E287-4A6B-86E4-9AFDB3EA26C9&displaylang=en

References
– http://support.microsoft.com/kb/263968/en-us