A row has no row number

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 Steve
2 Gail

and I query:

select ID, name from people order by name

I get

ID Name
-- -------
2 Gail
1 Steve

I could add a row number

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

and get this:

   Name
-- -------
1 Gail
2 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 Bob
2 Gail
2 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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

1 Response to A row has no row number

  1. Todd Fifield says:

    Nice little article. I once had a client that created in indented Bill Of Materials table as a heap and expected everything to come out in order because that’s the way the rows were inserted. I kept telling them it was going to break sooner or later and that it should be fixed.

    They didn’t get anyone to fix it and it did break eventually.

    Like

Comments are closed.