Returning Identity Value in SQL Server: @@IDENTITY Vs SCOPE_IDENTITY Vs IDENT_CURRENT

http://weblogs.asp.net/arefin/archive/2010/05/04/returning-identity-value-in-sql-server-identity-vs-scope-identity-vs-ident-current.aspx:
" We have some common misconceptions on returning the last inserted identity value
from tables. To return the last inserted identity value we have options to use @@IDENTITY
or SCOPE_IDENTITY or IDENT_CURRENT function depending on the requirement but it
will be a real mess if anybody uses anyone of these functions without knowing exact
purpose. So here I want to share my thoughts on this. @@IDENTITY, SCOPE_IDENTITY
and IDENT_CURRENT are almost similar functions in terms of returning identity value.
They all return values that are inserted into an identity column. Earlier in SQL
Server 7 we used to use @@IDENTITY to return the last inserted identity value because
those days we don’t have functions like SCOPE_IDENTITY or IDENT_CURRENT but now
we have these three functions. So let’s check out which one responsible for what.
IDENT_CURRENT returns the last inserted identity value in a particular table. It
never depends on a connection or the scope of the insert statement. IDENT_CURRENT
function takes a table name as parameter. Here is the syntax to get the last inserted
identity value in a particular table using IDENT_CURRENT function.

SELECT IDENT_CURRENT('Employee')

Both the @@IDENTITY and SCOPE_IDENTITY return the last inserted identity value created
in any table in the current session. But there is little difference between these
two i.e. SCOPE_IDENTITY returns value inserted only within the current scope whereas
@@IDENTITY is not limited to any particular scope. Here are the syntaxes to get
the last inserted identity value using these functions

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

Now let’s have a look at the following example. Suppose I have two tables called
Employee and EmployeeLog.

CREATE TABLE Employee
(
EmpId NUMERIC(18, 0) IDENTITY(1,1) NOT NULL,
EmpName VARCHAR(100) NOT NULL,
EmpSal FLOAT NOT NULL,
DateOfJoining DATETIME NOT NULL DEFAULT(GETDATE())
)

CREATE TABLE EmployeeLog
(
EmpId NUMERIC(18, 0) IDENTITY(1,1) NOT NULL,
EmpName VARCHAR(100) NOT NULL,
EmpSal FLOAT NOT NULL,
DateOfJoining DATETIME NOT NULL DEFAULT(GETDATE())
)

I have an insert trigger defined on the table Employee which inserts a new record
in the EmployeeLog whenever a record insert in the Employee table. So Suppose I
insert a new record in the Employee table using following statement:

INSERT INTO Employee (EmpName,EmpSal) VALUES ('Arefin','1')

The trigger will be fired automatically and insert a record in EmployeeLog. Here
the scope of the insert statement and the trigger are different. In this situation
if I retrieve last inserted identity value using @@IDENTITY, it will simply return
the identity value from the EmployeeLog because it’s not limited to a particular
scope. Now if I want to get the Employee table’s identity value then I need to use
SCOPE_IDENTITY in this scenario. So the moral is always use SCOPE_IDENTITY to return
the identity value of a recently created record in a sql statement or stored procedure.
It’s safe and ensures bug free code.

How to Generating Unique Identifire in SQL Server

We often find ourselves in the need of generating unique numbers in our database applications.

Let us quickly take a tour of how to do that using Sql server 2005.

SQL Server contains the NEWID() function. This function creates a unique value of type uniqueidentifier. We can use this function in several ways to generate unique numbers to suit our requirements :

Generate GUID's :

UniqueIndentifiers are also knows as GUID's. To generate a GUID use :

SELECT NEWID() as Guid

generates e00ab1d2-7484-410b-a55a-bcfa131a727e on my machine

Generate only digits :

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY(5)) AS Bigint)) as UniqueNo

generates 427357674589

Generate fixed digit unique numbers :

At times, we may also need to generate fixed digit numbers. You can do that in the following manner :

SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(5)) AS bigint),12),0,0)