sequence numbers 1

[junkmail]

It there a function available in SQL 2005 that will allow a field in the database to always have its current row number?

 

[SkipVought]

Hi,

What does that mean, row number.

A relational database table is like a bag of marbles. There is no order to the relation. You may be able to report, via a query and order the query results, but the rows in the database contain no inherent order.

 

[Olaf Doschke]

What SkipVought says is true.

What you can do to achieve something similar is adding an integer identity field, which is an autogenerated and ascending integer. So if you order by that field you get the data in that order. But if using a where clause or deleting rows of course the row number of a record changes in the result.

The row number is not a point of interest at all in a database, what matters is having a unique identifier in a record to have a permanent reference like a permalink is for a blog article. Of course it's as permanent, as the record is similarly as the permalink gets broken if the blogger deletes his article. What's used for that type of identifier called primary key typically is integer identity(1,1) or uniqueidentifier with default value newid().

Bye, Olaf.

 

[DaveInIowa]

There is a function, but as Skip and Olaf mentioned its result is meaningless and (please help me if I'm wrong here) cannot be relied upon to be the same over each execution of the query, even if no rows have been modified.

ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNumber

I put this out there not as sound advice but for others to comment on if this doesn't work as I've explained; then I've learned something also.

 

[junkmail]

Thanks for all your input!

 

[SkipVought]

@junkmail,

I'd consider Olaf's reply a "Great post," so I hit "Star it!"

I notice that over the fourteen or so years that you have been a member, you have never done so. Were none of the responses over the nearly decade and a half, not up to that kind of recognition? Just curious.