Can SQL Server tell me which row was inserted most recently?

Often, people ask, "how do I get the last row out of a table?" Let me be absolutely clear that I am not talking about people who are inserting a row and then want to know what the ID number is—there's already an article for that (Article #2174). What I'm walking about here is people who are monitoring a system, and want details about the newest row in a table (not just the id number, and not related to any specific or recent interaction they had with the system). 
 
The main comprehension problem here is that SQL Server doesn't know what row was inserted last; you need to track that information yourself. Unlike Access and other file-based database platforms, a table in SQL Server is essentially an unordered heap. You can not assume that the last row inserted will be at the "end" of the table, mainly because tables in SQL Server don't have an "end." You need to tell SQL Server what you mean by "last" (using TOP with an ORDER BY, or aggregate functions like MAX) — which might require a change to your existing schema. 
 

---

Using the IDENTITY 
 
A built-in way to identify the last row is to use an IDENTITY column; for example: 
 

CREATE TABLE dbo.tracking0  (      userID INT IDENTITY(1,1)          PRIMARY KEY CLUSTERED,      username VARCHAR(32)  )  GO

 
Then, to get the "last" row: 
 

SELECT userID, username      FROM dbo.tracking0      WHERE userID =      (          SELECT MAX(userid)          FROM dbo.tracking0      )

 
You can also use: 
 

SELECT TOP 1 userID, username      FROM dbo.tracking0      ORDER BY UserID DESC

 
One problem with this approach is that you don't have any indication of *when* the last row was inserted. Also, it ties you to an incrementing surrogate key, which may not be desirable (especially in distributed systems). 
 

---

Storing the INSERT date/time 
 
Another simple way to track order of insert is to have a DATETIME or SMALLDATETIME column, with a default value of GETDATE() or CURRENT_TIMESTAMP. 
 

SET NOCOUNT ON    CREATE TABLE dbo.tracking1  (      username VARCHAR(32) PRIMARY KEY,      dt DATETIME DEFAULT GETDATE()  )  GO    INSERT dbo.tracking1(username) VALUES('a')  INSERT dbo.tracking1(username) VALUES('b')    -- wait for two seconds  WAITFOR DELAY '00:00:02'    INSERT dbo.tracking1(username) VALUES('c')  INSERT dbo.tracking1(username) VALUES('d')

 
Now, to get the "last" row: 
 

SELECT TOP 1      username, dt      FROM dbo.tracking1      ORDER BY dt DESC

 
And if you expect ties, and want to show all matches: 
 

SELECT TOP 1 WITH TIES      username, dt      FROM dbo.tracking1      ORDER BY dt DESC

 

---

Tracking Updates 
 
It is a little trickier to track updates. If you can control data manipulation so that stored procedures are always used, and never ad-hoc UPDATE statements, then you can simply include a "date updated" column as part of the update statement. For example: 
 

CREATE TABLE dbo.tracking2  (      userid INT IDENTITY(1,1)           PRIMARY KEY CLUSTERED,      username VARCHAR(32),      dtAdded DATETIME DEFAULT GETDATE(),      dtUpdated DATETIME DEFAULT GETDATE()        -- you might want to make this NULL      -- as a flag that the row has been      -- inserted but never updated  )  GO

 

CREATE PROCEDURE dbo.insertTracking2      @username VARCHAR(32)  AS  BEGIN      SET NOCOUNT ON      INSERT dbo.tracking2(username)          VALUES(@username)  END  GO

 

CREATE PROCEDURE dbo.updateTracking2      @userID INT,      @username VARCHAR(32)  AS  BEGIN      UPDATE dbo.tracking2          SET username = @username,          dtUpdated = GETDATE()          WHERE userid = @userID  END  GO

 
Now, let's throw some data in there, wait one second, and then update one of the rows: 
 

EXEC dbo.insertTracking2 'aaron'  EXEC dbo.insertTracking2 'macgyver'    WAITFOR DELAY '00:00:01'    EXEC dbo.updateTracking2 1, 'aaronb'

 
And you can see how the rows come back when you ORDER BY dtUpdated DESC: 
 

SELECT username, dtAdded, dtUpdated      FROM dbo.tracking2      ORDER BY dtUpdated DESC

 
If you cannot control data manipulation through stored procedures, you might have to use a trigger in order to capture ad hoc updates. Here is how the trigger would look: 
 

CREATE TRIGGER dbo.trUpdateTracking2      ON dbo.tracking2      FOR UPDATE  AS      -- to prevent recursion      IF NOT UPDATE(dtUpdated)            UPDATE dbo.tracking2              SET dtUpdated = GETDATE()              -- convenient, self-join and              -- against the inserted table              FROM dbo.tracking2 t2              INNER JOIN inserted i              ON t2.userid = i.userid  GO

 
Same as above; run an update against one of the rows, and then run another select, and you will see the dtUpdated date has changed. 
 
You might want to add logic to verify that at least one of the columns has actually *changed* before changing the updated date; this depends on whether you are trying to track activity on your database, or actual deltas in your data. 
 

---

More Information 
 
For more advanced methods of auditing SQL Server data changes, see Article #2448.