Ranking Functions within SQL Server 2005

With SQL Server 2005, Microsoft introduced a number of new features. These new features make it easier for a DBA or SQL Server developer to code against and to maintain SQL Server databases. In this article I will discuss the new ranking functions provided with SQL Server 2005. These new functions make it much easier for you to code your T-SQL to associate a ranking to your result set. As I discuss each of the new ranking functions I will provide one or more examples to help illustrate how these new functions work.

What are Ranking Functions

Ranking functions are functions that allow you to sequentially number your result set. These functions can be used to provide you with a number of different sequential numbering schemes. For example you can number each row in your result set sequentially where the first row has a ranking number of 1, the second row has a ranking of 2, third row has 3, and so on. You can also use these ranking functions to sequentual number groups, so each group would have a numbering scheme of 1,2, 3, and then the next group would start over with 1, 2, 3, etc..

Test Data for My Examples

In order to provide examples of each ranking function I need to have some test data that the ranking function will process against. For my test data I will use a simple “Person” table. This table will consist of three columns “FirstName”, “Age” and “Gender”. Below is the code to create and populate my sample test data file.


FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')


The first ranking function I will discuss is the ROW_NUMBER function. This function returns a sequential number starting at 1 for each row or grouping within your result set. The ROW_NUMBER function has the following calling syntax:

ROW_NUMBER ( )  OVER ( [  ]  )

Where the:

is a column or set of columns used to determine the grouping in which the ROW_NUMBER function applies sequential numbering.

” is a column or set of columns used to order the result set within the grouping (partition).

To demonstrate how to use the ROW_NUMBER function, my first example below will sequentially number all the rows in my Person table, and order them by Age.


FROM Person

Here is the result set for the above T-SQL code:

Row Number by Age    FirstName  Age

-------------------- ---------- -----------
1 Larry 5
2 Doris 6
3 George 6
4 Mary 11
5 Sherry 11
6 Sam 17
7 Ted 23
8 Marty 23
9 Sue 29
10 Frank 38
11 John 40

Here you can see I have sequentially numbered all my Person table rows starting from 1, and the result set is ordered by the Age column. This ordering was accomplished by placing the “ORDER BY Age” criteria in the ORDER BY clause of the ROW_NUMBER function.

Suppose you do not want your result set to be ordered, but you want to simply just sequentially number each row. The ROW_NUMBER function requires an ORDER BY clause, so something is needed in this clause. In the following query I specified “SELECT 1” for the ORDER BY clauses, doing this just returned my rows physically how they where stored, and sequentially numbered them starting from 1:

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],

FROM Person

Here is the result set from running the above query:

Row Number by Record Set FirstName  Age

------------------------ ---------- -----------
1 Ted 23
2 John 40
3 George 6
4 Mary 11
5 Sam 17
6 Doris 6
7 Frank 38
8 Larry 5
9 Sue 29
10 Sherry 11
11 Marty 23

The ROW_NUMBER function not only allows you to order the entire row set, but you can also use the PARTITION clause to sequentially number groups of rows. Rows will be sequentially numbered within each unique partition value. The sequential number will restart at 1 for each new partition value in your record set. Take a look at the following query:


FROM Person

When I run this query, this is my result set:

Partition by Gender  FirstName  Age         Gender

-------------------- ---------- ----------- ------
1 Doris 6 F
2 Mary 11 F
3 Sherry 11 F
4 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Marty 23 M
6 Frank 38 M
7 John 40 M

In this example I partitioned by Gender, and ordered by Age. Doing this allowed me to sequentially number the female records in my Person table by age, and then have the sequential numbering start over again for the male group.

RANK Function

Sometimes you want a row that has the same order by column value as another row to have the same ranking. If this is the case then the RANK() function will help you. The RANK function has the following calling syntax:

RANK ( )  OVER ( [  ] > )

Where the:

is a column or set of columns used to determine the grouping in which the RANK function applies sequential numbering.

<order_by_clause>” is a column or set of columns used to order the result set within the grouping (partition).

The RANK function sequentially numbers a record set, but when two rows have the same order by value then they get the same ranking. The ranking value still gets incremented when two rows have the same order by value, so that when a new ranking order by value is encountered the ranking value on that new row will be 1 more than the number of proceeding rows. Let me show you a couple of examples to help you better understand the RANK function.

In this first example I want to rank my record set by Age:


FROM Person

Here is the output for this command:

Rank by Age          FirstName  Age

-------------------- ---------- -----------
1 Larry 5
2 Doris 6
2 George 6
4 Mary 11
4 Sherry 11
6 Sam 17
7 Ted 23
7 Marty 23
9 Sue 29
10 Frank 38
11 John 40

By looking at this output you can see that whenever rows have the same Age value their “Rank by Age” value are the same. You can see this for “Doris” and “George”, “Mary” and “Sherry”, as well as “Ted” and “Marty”. Each of these row pairs have the same “Rank by Age” value. Note that “Doris” and “George” both have a ranking of 2, but the ranking for “Mary” the next unique Age doesn’t have a ranking value of 3, but instead has a ranking of 4. This is because “Mary” is the forth record returned in the record set, and the RANK() functions takes this into account when setting the ranking value of the next unique “Rank by Age” value.

If you want to have multiple rankings in your record set, where each ranking is for a specific group you need to use the “PARTITION BY” clause of the RANK function. Below is an example where I grouped my ranking by Gender, and ordered each ranking by Age:

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],

FROM Person

Here is the results of running the above query:

Partition by Gender  FirstName  Age         Gender

-------------------- ---------- ----------- ------
1 Doris 6 F
2 Mary 11 F
2 Sherry 11 F
4 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
4 Marty 23 M
6 Frank 38 M
7 John 40 M

Here you can see that the “F” Gender started ranking at 1 and goes through 4, then the ranking starts over with 1 when the first “M” Gender is encountered.


The DENSE_RANK function is similar to the RANK function, although this function doesn’t produce gaps in the ranking numbers. Instead this function sequentially ranks each unique ORDER BY value. With the DENSE_RANK function each row either has the same ranking as the preceeding row, or has a ranking 1 greater then the prior row. The DENSE_RANK function has the same syntax as the RANK function.

Here I use the DENSE_RANK function to rank all my Person records by Age:


FROM Person

This code produces the following output:

Dense Rank by Age    FirstName  Age

-------------------- ---------- -----------
1 Larry 5
2 Doris 6
2 George 6
3 Mary 11
3 Sherry 11
4 Sam 17
5 Ted 23
5 Marty 23
6 Sue 29
7 Frank 38
8 John 40

Here you can see the “Dense Rank By Age” column numbers are sequential without any gaps. And when two rows have the same ORDER BY value they have the same ranking like “Ted” and “Marty”.

NTILE Function

The last ranking function is the NTILE function. This function is used to break up a record set into a specific number of groups. The NTILE function also uses similar syntax as the other ranking functions.

In this first example, I want to group my Person records into three different groups of records. I want these groups to be based on the Age column. To do that I would run the following T-SQL:

SELECT FirstName,

NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
FROM Person

Here is my result set from the above T-SQL command:

FirstName  Age         Age Groups

---------- ----------- --------------------
Larry 5 1
Doris 6 1
George 6 1
Mary 11 1
Sherry 11 2
Sam 17 2
Ted 23 2
Marty 23 2
Sue 29 3
Frank 38 3
John 40 3

In my result set I ended up with three different “Age Groups”. The first age group goes from Age 5 to Age 11, the second age group goes from 11 to 23, and the last age group is 29 to 40. The NTILE function just evenly divides your record set into the number of groups the NTILE function requests. By using the NTILE function each record in a group is give the same ranking.

The NTILE function is very useful if you only want to return a specific grouping of records. Below is an example where I returned only the middle group (Age Group = 2) from my prior example:

SELECT FirstName,

Age AS [Age Group]
FROM ( SELECT FirstName,
FROM Person) A
WHERE AgeGroup = 2

Here you can see I only returned the second Age Group:

FirstName  Age         Age Group

---------- ----------- -----------
Sherry 11 11
Sam 17 17
Ted 23 23
Marty 23 23


Coding a process to sequential number your record sets used to take a number of lines of code. SQL Server 2005 solved this coding dilemma by providing some new ranking functions. Hopefully the next time you need to sequential number a record set, one of the new ranking functions in SQL Server 2005 will make your number task, a trivial task.

Reference: http://www.databasejournal.com/features/mssql/article.php/3661461/New-Ranking-Functions-within-SQL-Server-2005.htm

Infosys’ success secret

The three aspects of ensuring success for any entrepreneur was innovation, ability to use innovation to create service and products that add value to customers and the ability to communicate the value to customers, Infosys Chairman N R Narayana Murthy said.

"Think of innovation and marketing as two most important instruments for creating a wonderful organisation and tremendous brand equity," Murthy advised entrepreneurs at the launch of a new marketing book, "No Marketing Money" authored by Jessie Paul, a former global brand manager at Infosys.

Using Innovative platform that does not cost too much money could help entrepreneurs to create brand equity, he said sharing his own experience of building Infosys from a little known brand to a globally recognised brand.
He said during a survey conducted in 1992 with prospective employees on the company they would like to join, zero per cent opted for Infosys. This dismal picture drove him to take a vow to change it. "We will be number one in India in five years," he had told his colleagues then.

His confidence was met with some scepticism by colleagues who wondered how a small company with less resources could hope to compete with richer and bigger companies, he recalled.

"We will do new unusual things," he had told his colleagues. "We will do what other companies did not do," he said reminiscing over the transformation of a company to a leading global software firm.

"Think of new ideas and think of platforms that do not cost too much and that will get people to talk about you, magazines to write about you," he said, adding communicating unusual ideas would attract people.

Paul said the book was a guide for companies especially startups to establish themselves and grow rapidly on a relatively limited budget. It provides actionable insights and advice on how to steer as well as manage brands.

Girish Paranjpe, Joint CEO, IT Business and Member of the Board, Wipro, said "Clear Positioning and Innovative marketing are required if more Indian firms are to play a role on the global stage."

Reference: http://infotech.indiatimes.com/News/Know-Infosys-success-secret/articleshow/5021209.cms

Developing High-Performance ASP.NET Applications

1. Disable session state when you are not using it.
2. Choose your session-state provider carefully.
3. Avoid unnecessary round trips to the server.
4. Use Page.IsPostBack to avoid performing unnecessary processing on a round trip.
5. Use ASP.NET server controls in appropriate circumstances.
6. Save server control view state only when necessary.
7. Leave buffering on unless you have a specific reason to turn it off.
8. Do not rely on exceptions in your code.
9. Use the common language runtime's garbage collector and automatic memory management appropriately.
10. If you have a large Web application, consider performing pre-batch compilation.
11. Recycle processes when running ASP.NET Web applications on Internet Information Services 5.0.
12. Adjust the number of threads per worker process for your application if necessary.
13. Use the HttpServerUtility.Transfer method to redirect between pages in the same application.
14. Make all modules in the request pipeline as efficient as possible.
15. Use early binding in Visual Basic .NET or JScript code.
16. Port call-intensive COM components to managed code.
17. Avoid single-threaded apartment (STA) COM components.
18. Use SQL Server stored procedures for data access.
19. Use the SqlDataReader class for a fast forward-only data cursor.
20. Choose the data viewing mechanism appropriate for your page or application.
21. Cache data and page output whenever possible.
22. For applications that rely extensively on external resources, consider enabling Web gardening on multiprocessor computers.
23. Be sure to disable debug mode.
24. Tune the configuration files for your Web server computer and specific applications to suit your specific needs.
25. Enable authentication only for those applications that need it. Like
a. Configure your application to the appropriate request and response encoding settings.
b. Consider disabling AutoEventWireup for your application.
c. Remove unused modules from the request-processing pipeline.


  1. Developing High-Performance ASP.NET Applications ( http://msdn.microsoft.com/en-us/library/5dws599a(VS.71).aspx )
  2. ASP.NET Best Practices for High Performance Applications ( http://www.codeproject.com/KB/aspnet/ASPNET_Best_Practices.aspx )
  3. Improving ASP.NET Performance ( http://msdn.microsoft.com/en-us/library/ms998549.aspx )
    10 Tips for Writing High-Performance Web Applications (
    http://msdn.microsoft.com/hi-in/magazine/cc163854(en-us).aspx )
  4. 10 ASP.NET Performance and Scalability Secrets ( http://www.codeproject.com/KB/aspnet/10ASPNetPerformance.aspx )
  5. Checklist: ASP.NET Performance ( http://msdn.microsoft.com/en-us/library/ms998596.aspx )

Caching in ASP.net

The main benefits of caching are performance-related: operations like accessing database information can be one of the most expensive operations of an ASP page's life cycle. If the database information is fairly static, this database-information can be cached.

Caching helps us to achieve three important aspects of QoS (Quality of Service):

  1. Performance - Caching improves application performance by minimizing data retrieval and formatting operations.
  2. Scalability - Since caching minimizes data retrieval and formatting operations, it reduces the load on server resources thus increasing the scalability of the application.
  3. Availability - Since the application uses data from a cache, the application will survive failures in other systems and databases.

Caching Options in ASP.NET

ASP.NET supports three types of caching for Web-based applications:

  1. Page Level Caching (called Output Caching)
  2. Page Fragment Caching (often called Partial-Page Output Caching)
  3. Programmatic or Data Caching

Output Caching

Page level, or output caching, caches the HTML output of dynamic requests to ASP.NET Web pages. The way ASP.NET implements this (roughly) is through an Output Cache engine. Each time an incoming ASP.NET page request comes in, this engine checks to see if the page being requested has a cached output entry. If it does, this cached HTML is sent as a response; otherwise, the page is dynamically rendered, its output is stored in the Output Cache engine.

Output caching is easy to implement. By simply using the @OuputCache page directive, ASP.NET Web pages can take advantage of this powerful technique. The syntax looks like this:

Partial-Page Output Caching

Partial-Page Output Caching, or page fragment caching, allows specific regions of pages to be cached. ASP.NET provides a way to take advantage of this powerful technique, requiring that the part(s) of the page you wish to have cached appear in a User Control. One way to specify that the contents of a User Control should be cached is to supply an OutputCache directive at the top of the User Control. That's it! The content inside the User Control will now be cached for the specified period, while the ASP.NET Web page that contains the User Control will continue to serve dynamic content.

Data Caching

Programmatic or data caching takes advantage of the .NET Runtime cache engine to store any data or object between responses. That is, you can store objects into a cache

To store a value in the cache, use syntax like this:

Cache["myblogpage"] = bar; or Cache.Insert("myblogpage", bar);

To retrieve a value, simply reverse the syntax like this:

sampleVar = Cache["myblogpage"];


Caching explained here:


Caching including other state managements explained here:


Career Path finder

Appreciate the effort spent to prepare this chart, a bit late for us though…but it can be useful for your relatives or friends..!

Giving SOAP a REST

Many developers will be surprised to learn that SOAP isn't the only game in town for Web services interfacing. REST offers a perfectly good solution for the majority of implementations, with greater flexibility and lower overhead. Developers need to stop reaching immediately for SOAP and start choosing the right technology for the application.

What is REST?

REST (representational state transfer) is an approach for getting information content from a Web site by reading a designated Web page that contains an XML (Extensible Markup Language) file that describes and includes the desired content.

Born of a UC-Irvine doctoral dissertation by Roy Fielding in the year 2000, REST has grown into a force in the application development community, offering a simplified method of using HTTP protocols to work directly with data.

Fielding describes REST as an architectural style that leverages the modern Web, adding "REST emphasizes scalability of component interactions, generality of interfaces, independent deployment of components, and intermediary components to reduce interaction latency, enforce security, and encapsulate legacy systems."

Delving into the short definition of REST, a basic use case for it would be an online publisher making available syndicated content. Subscribers would need only to know the URL (Uniform Resource Locator) for the page where the XML file was located, read it with a Web browser, interpret the content data, and reformat and use it appropriately.

  1. Overview of REST in WCF (http://msdn.microsoft.com/en-us/netframework/dd547388.aspx )
  2. What is REST(http://rest.blueoxen.net/cgi-bin/wiki.pl?WhatIsREST)
  3. Representational State Transfer - REST Tutorial (http://searchsoa.techtarget.com/generic/0,295582,sid26_gci1317288,00.html)
  4. How I Explained REST to My Wife (http://tomayko.com/writings/rest-to-my-wife)
  5. REST (http://www.xfront.com/sld001.htm)
  6. http://www.devx.com/DevX/Article/8155
  7. REST vs. SOAP (http://www.innoq.com/blog/st/2006/06/30/rest_vs_soap_oh_no_not_again.html)