Windowing and Ranking in SQL Server – Part 1

Hello Folks,

You must have heard about this feature which was introduced earlier in SQL Server 2005. Although since then there hasn’t been any change with arrival of SQL Server 2008 R2.

The windowing provides a new perspective on the data, while the ranking functions then use that perspective to provide additional ways to manipulate the query results.

Windowing:

The Windows have to be applied before the Ranking functions in the Query. It becomes easier to think through the window and then add the ranking function.

OVER () Clause:

• The OVER () clause creates a new window on the data. Like an independent ordering of the rows, which may or may not be of the same sort order of the ORDER BY clause. 
• The windowing capabilities create an alternate flow to the query with its own sort order and ranking functions. 
• In fact the results of the windowing and ranking are passed back into the query before the ORDER BY clause. 
• The complete syntax is: 

OVER (ORDER BY columns)

• The “columns” can be any available column or expression, as in ORDER BY clause. But the OVER () clause won’t accept a column ordinal position, like 1, 2 etc., as it would be in the ORDER BY clause. 
• It has the advantage of the indexes and can be very fast, even if the sort orders are different from the main query’s sort order. 
• This will become much clear after seeing this example: 

First, you should see the table that we are using in this example-

Now, the OVER () clause creates a separate view to the data sorted by Salary;

USE TEST
SELECT ROW_NUMBER() OVER(ORDER BY Salary) AS RowNumber,
Fname,Class, Salary
FROM Students
ORDER BY RowNumber

The result can be seen as:

You can see from the above that Salary are sorted, an according to that Row Number are incremented.

Using Partitioning:

• The OVER () clause normally creates a single sort order, but it can divide the windowed data into partitions, which are similar to groups in an aggregate GROUP BY query.
• They are very powerful because the ranking functions will be able to restart with every partition.
• The following example will make you more clear, of what this is all about:

I have used the same table as shown above, “Students”.

Now, I am showing how the Partitions work with the OVER () clause. Here, i am making a separate partition for each “Class”, and the “RowNumber” will increment according to it;

SELECT ROW_NUMBER() 
OVER(PARTITION BY Salary ORDER BY Salary) AS RowNumber,
Fname,Class, Salary
FROM Students
ORDER BY Salary

The results of the above query can be seen as:

You can also use the above Partition with the Aggregate functions.

Well this was all about the Windowing and in the next part I will deal with Ranking functions i.e., how it comes into the play and for what?

So keep in touch!

Hope you got it understood well 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook