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
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