Return a single row number for each record in a table

This article is by far smaller than other ones, but it is easier as well and is actually a part of a second one about performing row-by-row operations on a single table... and I hope those articles should be usefull for you. First, Let's considrer the following Query:

Select * from Facility

The results, are these ones, as expected...

If you run this query in your SSMS (SQL Server Management Studio, if you are unfamiliar with the acronym) , you usually will see every single row in the Facility table, and even in the messages pane there is a little hint of how many rows where selected alongside with all of the results brough by the aforementioned query, but you do not have a "selectable" column that could be used to identify each row. You can think about using a primary key or index, but in this case we are talking about Identifiying a single row rather than a single record. So, we need to add something to this query: The row_number() function.

row_number() returns a bigint representing the sequential number of an individual row within a table or a partition of it (we'll discuss this later because it will in fact number in a different way in each case). As you noticed so far, this function is used for sequentially number the rows resulting from a select statement.

Now, here is an example of a row_number() function:

Select row_number() over (order by FacilityID) as [Row #],* from Facility

Now, By doing this, you will get something like this:

As you noticed, the syntax of a row_number() functions includes an over clause, this is part of the function. The order by argument is used to determine the sequential order in which each row is being shown. If we modify our previous example as follows:

Select row_number() over (order by FacilityName) as [Row #],* from Facility

These will be the results:

You will now notice the difference between the two examples immediatly!

You may be asking yourself "What is the catch with all this stuff??", well, If you want to know do not miss the next part of this article: The cursor Curse. Stay tunned and have fun!

Dr. Osorio