Searching for Rowid in sql-server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Searching for Rowid in sql-server 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-29 : 08:22:24

Neetu writes "Hello everybody,

Pls. let me know if there is any thing like rowid , rownumber or anything related to the uniqueness of a row. This feature is present in Oracle in hidden form as rowid . In Sql its necessary to know in order to fetch data from table without primary key.

Thanx in advance"

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 08:37:12

Hi Neetu, Welcome to SQL Team!

Nope, 'fraid not. There are some workarounds, but they will mostly come back to using the PK at some point.

There might be something in SQL 2005 but I'm not sure.

What you trying to do, paging? or something else?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 08:50:16

>>let me know if there is any thing like rowid , rownumber or anything related to the uniqueness of a row

In SQL Server you need to use Primary , Identity or Unique columns to maintain uniqueness

>>There might be something in SQL 2005 but I'm not sure.

Somewhere I read it is supported

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 09:04:54

"Somewhere I read it is supported"

Yeah, me too. Trouble is I have a nasty feeling that it was in an early Beta and was then removed.

Kristen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-29 : 19:42:51

There is a ROW_NUMBER() function added to SQL2K5, it's not exactly the same as rowid/rownum in Oracle but it should provide the row numbering features everyone seems to need.

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 00:06:44

Thanks Rob, fills in a gap that my aging memory had left spare! Perhaps if I could defrag it I could fill the rest in one piece and become a genius!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-30 : 01:10:55

>>it's not exactly the same as rowid/rownum in Oracle

Rob, may we know the difference?

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-30 : 07:10:14

Oracle stores rowid behind the scenes. ROW_NUMBER() is calculated as the query is processed but never stored.

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 07:42:47

Rob,

Can you do


SELECT *
FROM MyTable
WHERE ROW_NUMBER() BETWEEN @intStartRow AND @intEndRow
ORDER BY MyDisplayOrder

or do you have to do


SELECT *
FROM
(
    SELECT *
    FROM MyTable
    ORDER BY MyDisplayOrder
) X
WHERE ROW_NUMBER() BETWEEN @intStartRow AND @intEndRow

Thanks

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources