SQL server

Using row_number() for numbering rows.

There are times when tables have no unique numerical identifiers to use. I was in one such situation when we had a heap with 25 million rows and no identifier row. In these cases the row_number() function can be used.

Example TSQL:

select * from
select row_number() over (order by [name]) as row_num,name
from sys.databases
)as dbs

The row_number() has to be used with the over(). The over() tells which ordered column will have the row numbers associated with it. Note that we are using a nested select statement.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s