question

Mansour Shoari avatar image
Mansour Shoari asked

Find the row number of selected records in a SQL 2K5 table

Good day, Is there a way to get the actual row number of selected records in a table with the result of the SELECT statement in SQL 2K5. I think knowing the row number might come handy in jumping to a specific record (e.g. for quick edits etc.) in a table when it is open in SSMS. Thank you.

sql-server-2005t-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Well, to assign row numbers to things in a result set you can use ROW_NUMBER, but I don't think you mean that, do you?

If you're talking about being able to assign a unique number to each row in a table, then create a column that is an IDENTITY column.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thomas 1 avatar image
Thomas 1 answered

You asked two different questions. To the first, I presume you want to know how many records were returned in a Select statement in T-SQL. If so, then use @@ROWCOUNT. For example:

Select * From sys.columns
Select @@ROWCOUNT

The second question relating to "jumping to a row" is entirely different. The only safe way to retrieve a specific row is to run another Select statement based on a Unique Key like the Primary Key. Perhaps SSIS has some other means to do this, but I wouldn't bet my code on it.

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, Thomas. Maybe I should rearticulate my question. I would like to find out in which row of a table (when you open it in the SSMS) a return of a specific SELECT statement located. Please note that there is no "RowNo" column in the table. SELECT * from dbo.Employee where EmployeID = ‘DTM-67954’ Result: RowNo EmployeeID 865 DTM-67954
0 Likes 0 ·
There is no concept of order in a table. A table, by definition, represents an unordered set. Thus, even executing the same SELECT statement without an ORDER BY clause is not guaranteed to be returned in the same order. Contrast this with something like Access which is an ISAM (Indexed Sequential Access Method) where data is stored in the order that it is entered.
0 Likes 0 ·
Good point, Thomas. It is much appreciated.
0 Likes 0 ·
Lynn Pettis avatar image
Lynn Pettis answered

SQL Server does not have an internal row number for the rows entered into a table. If you need this functionality, you would need a column with an identity property set to record the necessary value as data is entered.

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, Lynn. I agree with your comment. However, when you open a table in SSMS, at the lower left corner of the viewed area (it is actually part of the status bar) you can see total number of rows in the open table as well as the number of row that is selected (and, left/right arrow signs). In the white editable area you can type e.g. 865 (+ ENTER). This takes you to row number 865. This is the number I like to be able to get from the SELECT statement’s “switch” or any other command! By the sound of it, this is not possible in SQL and I can only leave it as a feature request for Microsoft.
0 Likes 0 ·
The row numbers there are artificial. There is no guaranttee that each time you open the table the data will be in the same order BY clause Still, in the output grid you are discussing, the row number is not part of the table in the databasse.
0 Likes 0 ·
True. Thanks for the note, Lynn. I think using the IDENTITY is the only option.
0 Likes 0 ·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

Using an Identity or GUID, or some key to ensure that you will get the exact record that you need is the safest bet in getting the record you are trying to get.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.