|
|
Posted by Erland Sommarskog on 09/26/34 11:46
Brad Eck (brad.eck@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, esquel@sommarskog.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
Navigation:
[Reply to this message]
|