By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

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

P: n/a
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?

Brad Eck
http://www.sitesdynamic.com
http://www.basketsetcetera.com

*** Sent via Developersdex http://www.developersdex.com ***
May 1 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
May 1 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.