Inserted ID in MS SQL Server

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
SELECT @@IDENTITY AS [@@IDENTITY]
SCOPE_IDENTITY() is a better solution for most circumstance.

References:

  1. http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
  2. http://www.dotblogs.com.tw/ajun/archive/2008/04/01/2504.aspx

Note: In Access it only support @@IDENTITY

Example: 

set rs = Server.CreateObject("ADODB.RecordSet")
rs.open sql, conn, 1, 1