It seems that every month I have someone asking the question about ordering or row numbers for a query. Let’s get one thing clear from the start: there are no "row numbers" in a table.

You can assume that the first row you inserted is row number one, but it’s not. In fact, depending on the indexing or lack of indexing, you may or may not get that row returned first by a query. You can add an ORDER BY when you query the table, and in that case you can get the rows returned in a certain order every time, however the row number is not linked to a row.

As an example. If I have this People table:

ID Name-- -------1  Steve2  Gail

and I query:

select ID, name from people order by name

I get

ID Name-- -------2  Gail1  Steve

I could add a row number

 SELECT row_number() OVER (ORDER BY [name])       , [Name]   FROM dbo.People

and get this:

   Name-- -------1  Gail2  Steve

But "Gail" isn’t linked to "1" as a row number. If I do this:

 INSERT people SELECT 3, 'Bob'

SELECT row_number() OVER (ORDER BY [name])       , [Name]   FROM dbo.People

I now get this:

   Name-- -------1  Bob2  Gail2  Steve

Now "Bob" is 1. You can get row numbers, but they are only linked to an ORDER BY and a specific result set. If the data changes, the row numbers may move.

While it might appear in some queries that you are getting consistent ordering of results, don’t confuse coincidence with causality. You might live on those assumptions for years, building code on them, and then make a few changes and lots of things break.

If you need ordering, use ORDER BY.

Filed under: Blog Tagged: syndicated, T-SQL