Creating a Globally Unique ID without UNIQUEIDENTIFIER

Question

Creating a Globally Unique ID without UNIQUEIDENTIFIER

samalex

SSCertifiable

Points: 5429
More actions
October 18, 2010 at 9:42 am

#388032

I need to generate a globally unique ID for a system I'm working on, but I want to avoid using UNIQUEIDENTIFIER for obvious reasons. My first thought was to create a UniqueID table and just add a new row to it and capture the identity field:
CREATE TABLE UniqueID (
ID Numeric(18,0) PRIMARY KEY IDENTITY(1,1),
TableName NVarchar(100) NOT NULL ,
CreateDate DATETIME DEFAULT(GETDATE()) NOT NULL)
GO
DECLARE @UniqueID Numeric(18,0)
INSERT INTO UniqueID (TableName) Values ('AssetTable')
SET @UniqueID = @@Identity
Select @UniqueID
Ideally I'd like to put this logic into a Scalar function so that function could be called when running bulk inserts, but Insert statements can't run within Scalar Functions. I'm writing stored procedures for modifying data from the application which would work, but there are times when we'll need to run bulk inserts, and not being able to evoke this functionality from an Insert into ... Select ... type statement isn't ideal.
Are there any other suggestions on how to go about doing this? Or possibly other ways to get a sequential globally unique ID without using UNIQUEIDENTIFIER?
Thanks for any advice ...
Sam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

no need to re-invent the wheel;

starting with SQL 2005, a new function NEWSEQUENTIALID() will return a GUID, but the values are more indexable, as they return items that are numerically sequential.

you have to use it as the DEFAULT value for a column with the datatype 'uniqueidentifier' in a table, but it'll do exactly what you are after.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

samalex SSCertifiable Points: 5429 More actions · Answer 2

Lowell (10/18/2010)
no need to re-invent the wheel;
starting with SQL 2005, a new function NEWSEQUENTIALID() will return a GUID, but the values are more indexable, as they return items that are numerically sequential.
you have to use it as the DEFAULT value for a column with the datatype 'uniqueidentifier' in a table, but it'll do exactly what you are after.

Hi Lowell,

Actually I've been using NEWSEQUENTIALID() when creating new GUID records within my tables, but does this still carry any performance pitfalls with clustered indexing when using GUIDs? Also the 16-byte size is still a concern, but I'm not too worried about that as long as performance isn't hindered by using GUIDs.

Thanks --

Sam

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 3

there is a simlar discussion going on...

http://www.sqlservercentral.com/Forums/Topic1002532-263-1.aspx

samalex SSCertifiable Points: 5429 More actions · Answer 4

sql_lock (10/18/2010)

there is a simlar discussion going on...

http://www.sqlservercentral.com/Forums/Topic1002532-263-1.aspx%5B/quote%5D

Thanks, I didn't see that before posting, but I'm reading through it now.