For those of you already diving into SQL Server 2005 this is nothing new but for those of you who are newly looking into it here is a cool new function.
ROW_NUMBER ()
The ROW_NUMBER will attach a row number to each row returned in a result set. So for example you can execute a query like this in 2005 and see the results below.
select object_id, name, row_number() over(order by name asc) as row_number
from sys.objects
where type = 'u'
go
Results
object_id Name row_number
1147151132 MSreplication_options 1
117575457 spt_fallback_db 2
133575514 spt_fallback_dev 3
149575571 spt_fallback_usg 4
1099150961 spt_monitor 5
1115151018 spt_values 6
If this is the first time you have seen this function you probably noticed something else unknown to you, the over() clause. The over() clause determines the order of a result set, before the function stated before it, is executed or applied. In essence, you have to supply the over() clause the column that you want to order the row number in. To use the example above, you may choose to give row numbers in the alphabetic ascending order of the name column as above. The over() clause is also used with other ranking functions.
That is example really doesn’t show a practical use this function though. Today I was asked to help with a client who is running report services on SQL 2005. This particular report was to display a graph grouping 100 employees by employee_id but sorted by the employee’s name. There was a problem that you could only legibly see about 10 employees per page on the graph. The problem was not limiting the results to 10 employees per page but rather to have a table of contents to quickly identify which employee by employee_id was on which page.
The query was actually quite simple in 2005. As I stated above the graphs were limited to 10 employees per page, so here the query I came up with. This is not the live data but only an example of how this could work.
-- Setup
-- creating the test database
create database test
go
use test
go
-- create the employee table
create table employee (id int identity(1,1), test int)
declare @i int, @j int
select @i = 1, @j = 200
while @i <= @j
begin
-- load table
insert into employee (test)
values (@i)
set @i = @i + 1
end
go
-- Querying test table
use test
go
select id, round(((row_number() over(order by id asc)+ 10) /10),1) as page_number
from employee
go
This query gives the result set below. Notice the “+10” in the query above before the row_number is divided by 10? The “+10” is to start with a page_number of 1 instead of 0.
Results
id page_number
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 2
11 2
12 2
13 2
14 2
... ...
Another cool thing about the row_number function is that you can filter by the function too but only if you utilize the new CTE functionality, I’ll talk about it later (Example below).
WITH test1 AS
(select id, row_number() over(order by id asc) as row, round(((row_number() over(order by id asc)+ 10) /10),1) as page_number
from employee)
select * from test1
where page_number between 5 and 6
go
This is just a couple of examples of how this could be used, but I am personally excited about the use of this function.
Man, the smallest things in life seem to bring such satisfaction.
PEACE,
Zach