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

User · Answer

There is a pseudocolumn called   physloc   that shows the physical address of the row   See Equivalent of Oracle s RowID in SQL Server

User · Answer

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

User · Answer

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

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

The answer is

Examples related to sql

Examples related to sql-server-2005

Examples related to rowid

Tags