In SQL Server is there any Alternative of Oracle ROWID

Question

Post reply

In SQL Server is there any Alternative of Oracle ROWID

Zaheer Ahmed-316603

SSC-Addicted

Points: 442
More actions
May 5, 2006 at 11:39 pm

#112889

Hi All,
I need to know, is there any alternative of Oracle ROWID in SQL Server.
Best Regards,
Zaheer

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

This might do the trick for you, depending on your exact needs.

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 2

ROWID has no equivalent. ROWNUM can be simulated and the above link gives you some of those options.

Zaheer Ahmed-316603 SSC-Addicted Points: 442 More actions · Answer 3

Thanks Guys,

Actually I neet to do paging in ASP.NET application. I have created a Stored Procedure, which takes two parameters(@From, @To). I don't want to use primary key column, because there might be some missing values in primary key column.

Therefore I need to access records by its position rather than its value like (1 to 15 , 20 to 35 etc.)

Regards,

Zaheer

GSQL SSC Enthusiast Points: 184 More actions · Answer 4

Maybe a temporary table or table variable with an identity column would suit your needs. Insert the whichever with an order by clause in your query, then select the rows using your range. This gives the idea:

declare @ctr bigint ; select @ctr = 1

declare @t1 table (

rownum bigint identity(1,1),

col1 bigint

)

while @ctr < 100000

begin

insert @t1

select @ctr

select @ctr = @ctr + 1

end

select * from @t1

order by rownum

I hope this helps.

George

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 5

In ASP.NET the pagination is not driven off of your primary key, if you are binding your dataset to a datagrid you need to set two options for implementing the pagination, SET your pagesize to 15(desired value) and SET AllowPaging to true and write a simple logic to set the current page value and for navigating to the nextpage.

Prasad Bhogadi
www.inforaise.com

Joanie Jeffcoat SSC Veteran Points: 267 More actions · Answer 6

Prasad is right. Do it in .Net - much easier and faster!

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

Interesting...

I may have a method that might even beat ASP.Net for performance but I need to know, does your table have an auto-numbering IDENTITY column? And, it doesn't matter if it may be missing values... just that an IDENTITY column is present...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Zaheer Ahmed-316603 SSC-Addicted Points: 442 More actions · Answer 8

Again Thanks Guys

Actually I am using Repeater Control instead of Datagrid control, which doesn't have buitin paging option.

Well Jeff, my table doesn't have an auto-numbering IDENTITY column but unique column it has.

I have already used Overloaded Fill(dataset, startRecord, maxRecords, srcTable) method of SqlDataAdapter object and its working fine. But I need to improve preformance as using this method I have to write query like this Select * from Customers, which returns all records.

Please let me know it you know any way.

Regards,

Zaheer