Uniquely identifying each record in table like ROWID in Oracle

Question

Hi all,

 Is there any direct equivalent concept of Oracles ,ROWID  in SQL Server. As you might be knowing that ROWID is the unique hex number given to each record of a table in Oracle. The column is not added explicitly , automatically it will come .. and we can do certain queries using ROWID ..

Please let me know ,is there any equal concept in SQL Server. I verifyied , NEWID(),GUID() ... etc concepts ..

NOTE: I don't want to add the column to the table EXPLICITLY ...

Please let us know if there is any ALTERNATE way ...

Thans in advance

---

-- Reddy Balaji C. ##Mark as answer if it solves the issue

Answer 1

I doubt if you can do it with SQL Server. As SQL and ORACLE product is different and architecture is also different.

HTH

Answer 2

There is no direct equivalent.

Why you need it? Probably MSSQL have another feature for your task.

Answer 3

In keeping with relational theory, there is no exposed ROWID in SQL Server table. Internally, there are values that SQL Server uses, and you can find out some of the details in my book (SQL Server 2008 Internals or by searching Paul Randal's blog. )

Also in keeping with relational theory, it is recommended that each table have a Primary Key, and that PK can serve as your row id.

If you provide a bit more detail of what you want this information for, we could provide more specific help as to what alternative or internal features could help.

Also, always tell us what version you are using.

---

HTH, Kalen Delaney www.SQLServerInternals.com

Answer 4

SQL Server has an IDENTITY property for the table.... However if you delete the row for example the gap is presented

 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

And there is no absolute guarantee that the IDENTITY will be unique, if you don't build a unique index and you allow direct inserts of identity values.

---

HTH, Kalen Delaney www.SQLServerInternals.com

Answer 6

Sure, if there is a trigger on the table and it fails the an IDENTITY property will be genareted though

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 7

Hi all,

    This is the story behind this question:

We have an application running on advantage database and delphi... and now we are moving the database to SQL Server in .Net environment.There, in the OLD appliation, advantage database is using ROWID in the coding(which is similar to ROWID in Oracle) .. But , to migrate the code from advantage DB to SQL Server, we need to replicate the functionality, without changing the table structure.

Unfortunately, there is no primary key in that table :(

We thought of adding one column (either Identity or GUID ) , but the customer is not agreeing for that because , he wants to connect the SQL Server DB from the delphi(OLD application) environment also.

Please suggest possible ways to solve this. Thank you.

---

-- Reddy Balaji C. ##Mark as answer if it solves the issue

Answer 8

http://msdn.microsoft.com/en-us/library/ms182776.aspx

rowversion (old and inaccurate name timestamp) is pretty close, isn't it?

I guess it's way out of fashion if five different answers didn't mention it!

Josh

ps - but you do have to add it explicitly. 

 

Answer 9

Hi

As far as I'm aware, ROWID in Oracle is used as a unique identifier for a row of data in a Table.  SQL Server offers something similar: -

CREATE TABLE [dbo].[Test] (
 [RowID] [int] IDENTITY (1, 1) NOT NULL ,
 [Test] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Test] WITH NOCHECK ADD
 CONSTRAINT [PK_Test] PRIMARY KEY  CLUSTERED
 (
  [RowID]
 )  ON [PRIMARY]
GO

the above example is using the column RowID as an Identity column and also includes a primary Clustered Index on the same column.

For your imported data; could you adopt a similar approach and just adjust the incremental values & data types to suit?

---

Please click "Mark As Answer" if my post helped. Tony C.