Display the actual row the record is saved in SQL 2000

I guess this is a two part question:

1. I would to display the actual row the records are stored in the table

2. Is there a way I can use Select to display a record by just calling the actual row number the record is saved in?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
no, sql server 2000 does not have a "row number".

to store a sequential value for inserted rows, you would use a INT/IDENTITY field.
after the insert, you use SCOPE_IDENTITY()   [@@identity is only to be used in sql7] in the same session to retrieve the value generated by the insert.

other than that, you should not really need a "row number". for ranking, as from sql 2005, you have the ROW_NUMBER() function, among others...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you explan the big picture, please?
I am not to sure to understand what you are asking for?
rayluvsAuthor Commented:

I notice that in SQL when creating a table, there is no "row number" column included, which I understand it would be useless if included.

Ok, to further explain my question: if I create a Table with 3 columns: PatienID, PatienName and PatiendDiagnostics, I would have a SQL table structure of 3 columns.  Finally we populate, import or data entry to this table 99,999 patients.

What I would like to know if SQL does have a row number or record number value stored as it saves data.  And I would like to know if we can display that row number within a Select statement next to each line it displays.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

OriNetworksConnect With a Mentor Commented:
If I understand correctly, you can return the identity of the inserted row by selecting
I just read your last post after I sent mine. It sounds like you are looking for a primary key identity column. You may want to insert a new row with datatype int and set the identity property to true
rayluvsAuthor Commented:
Understood, SQL does not store this value.  With methods above in changing one of the columns types or storing sequential values, I can't use it since the table I want to display row-number is already being use for quite sometime; no re-restructure can be done.

Let me put the question this way:

Is there a way that after an INSERT I can recall that record location within the table without querying any of its columns? Some sort of SQL internal function that can display its location?

Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
short answer: no.

long answer: well, yes. you could query the system to to get the table's data block addresses, and dump those data pages hex data, and try to find the data your are looking for.
but I doubt this is anything you really are looking for, as that is only for dba to fix hard-core issues (like corrupt data pages, or investigating performance issues.)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.