Brad Eck (br******@sitesdynamic.com) writes:
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?
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,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
