This function is extremely useful. As of late, I use it mainly to help me manage a loop within a loop, which we will save for another time because I need to release said recent work to other users who are not me. However, I want to document this information somewhere for quick and easy reference of my own.

Here is a scenario. I want to create a temporary table of records, and I need to iterate through each row, and I need to a do it in a specific order. I need the row number to act like a record number for just this temporary table, like so:

Declare @tblExample table (idRecord int, idLocation int, dtDate datetime)

Insert into @tblExample

Select Row_Number() Over (order by dtDate) as idRecord, idLocation, dtDate
From tblTask a
	join tblLocation b
		on a.idLocation = b.idLocation
Where 1 = 1
and dtDate >= getdate()

Sometimes I end up removing items from a temporary table and need to reset the idRecord column so that it updates to new row numbers. I can do that with the following code:

Here is how to do that:

Update a
Set idRecord = a.idRecordNew
From
	(
		Select Row_Number() Over (Order by idRecord, idLocation, dtDate) as idRecordNew
		From @tblExample
	) a

That is all for this post.