Getting a unique ID in SQL Server - other than a GUID

**Erland Sommarskog** · May 1 '06, 10:15 PM

Re: Getting a unique ID in SQL Server - other than a GUID

Brad Eck (brad.eck@sites dynamic.com) writes:[color=blue]
> In Access, newID returns a unique for the table. In SQL Server, newid()
> returns a GUID - unique in the world. I do not need or desire that
> complexity. Is there a way to get a simple unique int on the table in
> SQL Server?[/color]

CREATE TABLE alfons (ident int IDENTITY,
data varchar(23) NOT NULL,
CONSTRAINT pk_ident PRIMARY KEY (ident))
go
INSERT alfons(data) VALUES ('Whatever')
SELECT scope_identity( ) -- 1
INSERT alfons (data) VALUES (NULL) -- This fails
INSERT alfons (data) VALUES ('NULL')
SELECT scope_identity( ) -- 3
SELECT ident, data

To summarise:

o You give a column the IDENTITY column. This must be a numeric column,
usually int, but you can use bigint or numeric(23, 0) as well.
o scope_identity( ) returns the most recently generated identity value
in the current scope.
o If there is an error, an IDENTITY values is nevetheless consumed, as
testified by the example.Thus do *not* use this if you need a contiguous
series.

The main advantage of IDENTITY is that it's good for scalability; many
processes can insert at the same time without waiting to get a number.

If you believe that you will need to change the value, or insert explicit
value, do not use IDENTITY, but roll your own. This is not very difficult
at all.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Getting a unique ID in SQL Server - other than a GUID

Getting a unique ID in SQL Server - other than a GUID

Comment