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

Question

I have one table CSBCA1_5_FPCIC_2012_EES207201222743, having two columns employee_id and employee_name

I have used following query

SELECT ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID) AS ID, EMPLOYEE_ID,EMPLOYEE_NAME 
FROM CSBCA1_5_FPCIC_2012_EES207201222743 

But, it returns the rows in ascending order of employee_id, but I need the rows in order they were inserted into the table.

Duffie

SQL Server has no idea what order they were inserted into the table (unless employee_id is an IDENTITY column, in which case you can kind of rely on changing the order to EMPLOYEE_ID DESC - but this isn't guaranteed). – Referential

No, SQL Server doesn't track that. You need to add such a column yourself. – Referential

Answer 1

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.

Referential

Answer 2

There is a pseudocolumn called %%physloc%% that shows the physical address of the row.

See Equivalent of Oracle's RowID in SQL Server

Cutinize

%%physloc%% also does not record the order the rows were inserted, though it will usually increase as the row insert order increases for a time. – Proclitic

Answer 3

SQL does not do that. The order of the tuples in the table are not ordered by insertion date. A lot of people include a column that stores that date of insertion in order to get around this issue.

Aymara