Equivalent of Oracle's Rowid in SQL Server

How do I get row id of a row in sql server

SQL Server does not track the order of inserted rows, so there is no reliable way to get that information given your current table structure. Even if employee_id is an IDENTITY column, it is not 100% foolproof to rely on that for order of insertion (since you can fill gaps and even create duplicate ID values using SET IDENTITY_INSERT ON). If employee_id is an IDENTITY column and you are sure that rows aren't manually inserted out of order, you should be able to use this variation of your query to select the data in sequence, newest first:

SELECT 
   ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID, 
   EMPLOYEE_ID,
   EMPLOYEE_NAME 
FROM dbo.CSBCA1_5_FPCIC_2012_EES207201222743
ORDER BY ID;

You can make a change to your table to track this information for new rows, but you won't be able to derive it for your existing data (they will all me marked as inserted at the time you make this change).

ALTER TABLE dbo.CSBCA1_5_FPCIC_2012_EES207201222743 
-- wow, who named this?
  ADD CreatedDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Note that this may break existing code that just does INSERT INTO dbo.whatever SELECT/VALUES() - e.g. you may have to revisit your code and define a proper, explicit column list.

What is rowID & rowNum (ROWID vs ROWNUM)

Both, ROWNUM and ROWID are pseudo columns.

Rowid

For each row in the database, the ROWID pseudo column returns the
address of the row.

An example query would be:

SELECT ROWID, last_name  
   FROM employees
   WHERE department_id = 20;

More info on rowid here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm

Rownum

For each row returned by a query, the ROWNUM pseudo column returns a
number indicating the order in which Oracle selects the row from a
table or set of joined rows. The first row selected has a ROWNUM of 1,
the second has 2, and so on.

You can limit the amount of results with rownum like this:

SELECT * FROM employees WHERE ROWNUM < 10;

More info on rownum here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

Difference

The actual difference between rowid and rownum is, that rowid is a permanent unique identifier for that row. However, the rownum is temporary. If you change your query, the rownum number will refer to another row, the rowid won't.

So the ROWNUM is a consecutive number which applicable for a specific SQL statement only. In contrary the ROWID, which is a unique ID for a row.

ROWNUM as a pseudo column equivalent in T-SQL?

Use row_number():

select row_number() over (order by (select null)) as ES_CMPNY_STATUS_KEY

Note that the order by is needed. The (select null) appears -- in practice -- to avoid any additional sorting. In general, though, you would include a column that specifies a sort order for the data you want.

Equivalent of Oracle’s RowID in MySQL

In MySql you usually use session variables to achive the functionality:

SELECT @rowid:=@rowid+1 as rowid
FROM table1, (SELECT @rowid:=0) as init
ORDER BY sorter_field

But you can not make sorts on the table you are trying to delete from in subqueries.

UPD: that is you will need to create a temp table, insert the ranging subquery to the temp table and delete from the original table by joining with the temporary table (you will need some unique row identifier):

CREATE TEMPORARY TABLE duplicates ...

INSERT INTO duplicates (rowid, field1, field2, some_row_uid)
SELECT
  @rowid:=IF(@f1=field1 AND @f2=field2, @rowid+1, 0) as rowid,
  @f1:=field1 as field1,
  @f2:=field2 as field2,
  some_row_uid
FROM testruns t, (SELECT @rowid:=NULL, @f1:=NULL, @f2:=NULL) as init
ORDER BY field1, field2 DESC;

DELETE FROM my_table USING my_table JOIN duplicates
  ON my_table.some_row_uid = duplicates.some_row_uid AND duplicates.rowid > 0

Since that is one time operation, this should not bring too much overhead.

How to Find similar data with different Rowid in oracle?

you can do it many ways, and since you brought the rowid up, this is one of them:

select * from yourtable tab1 join yourtable tab2 on tab1.x = tab2.y and tab1.rowid <> tab2.rowid

what is max(rowid) in oracle

ROWID is Oracle's pseudocolumn which holds address of a particular row. It lets database locate rows. You normally don't see it in table structure, but it's there and remains hidden. This doesn't mean though, that you can't use it :-)

Your DELETE statement removes every row from table SALES_DETAILS that is a duplicate based on txt_id, customer_id pair, just like you mentioned. So what this means is it removes duplicates but saves the row with highest rowid value within the group.

Please look it up in documentation for more info.

problem when running SQL Oracle command with ROWID

ROWID refers to the address of some row so you should provide the name of the table so that Oracle understands that out of multiple tables in your FROM clause which table it should consider for ROWID.

Use ECM_WF_PROC_ETAPAS.ROWID

SQL Server : RowVersion equivalent in Oracle

The simple answer is No - but it's easy to create one yourself with a NUMBER column and a trigger to set/update it.

A simple example for Oracle 11gR2:

CREATE SEQUENCE global_rowversion_seq;

ALTER TABLE mytable1 ADD rowversion NUMBER;

ALTER TABLE mytable2 ADD rowversion NUMBER;

CREATE TRIGGER mytable1_biu
   BEFORE INSERT OR UPDATE
   ON mytable1
   FOR EACH ROW
BEGIN
  :NEW.rowversion := global_rowversion_seq.NEXTVAL;
END mytable1_biu;

CREATE TRIGGER mytable2_biu
  BEFORE INSERT OR UPDATE
  ON mytable2
  FOR EACH ROW
BEGIN
  :NEW.rowversion := global_rowversion_seq.NEXTVAL;
END mytable2_biu;

(If you're on an earlier Oracle version, the assignments in the triggers must be done with a query, e.g.:

  SELECT global_rowversion_seq.NEXTVAL
  INTO :NEW.rowversion
  FROM dual;

Now, keep in mind in some cases this design may have a performance impact in extreme situations (e.g. databases with extremely high insert/update activity) due to contention from all database inserts/updates using the same sequence. Of course, in this circumstance you probably would avoid triggers in the first place anyway.

Depending on how you use the rowversion column, it may be a good idea to use a separate sequence for each table instead. This would mean, of course, that rowversion would no longer be globally unique - but if you are only interested in comparing changes to rows within a table, then this would be fine.

Another approach is to advance the counter for each row individually - this doesn't need a sequence and allows you to detect changes to a row (but does not allow comparing any row to another row):

ALTER TABLE mytable ADD rowversion NUMBER;

CREATE TRIGGER mytable_biu
  BEFORE INSERT OR UPDATE
  ON mytable
  FOR EACH ROW
BEGIN
  :NEW.rowversion := NVL(:OLD.rowversion, 0) + 1;
END mytable_biu;

Each row will be inserted with rowversion = 1, then subsequent updates to that row will increment it to 2, 3, etc.

---

Related Topics