Help plz.. Global Identity

Question

Help plz.. Global Identity

CDBA

Hall of Fame

Points: 3266
More actions
March 20, 2008 at 7:26 pm

#188340

Hi
I got an unique requirement. One of the developer need to create a unique value (int or number) with each row insertation and for some of the selected tables of DB.
So, the issue is as follows:
1. He wants to use something like identiy
2. But the idendity value will be unique through out the DB
Could any one please help me how to do this? Many thanks in advance
Regards
Utsab Chattopadhyay

CDBA

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

A "UniqueIdentifier" datatype and a default of NEWID() would probably fit the bill (although, I don't care for them. Lookup both items in Books Online for more details.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CDBA Hall of Fame Points: 3266 More actions · Answer 2

Thanks a lot. But per my understanding, this approach will give number as GUID (B85E62C3-DC56-40C0-852A-49F759AC68FB). Is it possible to get a number (Like identity returns) instead?

Many thanks in advance...

Regards

Utsab Chattopadhyay

CDBA

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 3

The only way I know to do something like this is with a table that contains key values. You can then use IDENTITY. But, short some pretty intense triggers or series of foreign keys, you have to rely on happy thoughts to ensure that it will be unique across the entire database. I'd follow Jeff's advice.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ian Yates SSCoach Points: 19738 More actions · Answer 4

Odd requirement - is it something to do with trust accounting?

Anyhow, you will need a global table with an identity column and then link to this from your other tables. The method is

begin transaction

insert into identityTable

select @identity = scope_identity()

insert into other table --include the scope_identity() stored in @identity variable

commit transaction

I guess you could even use triggers on the other tables to do the identity generation and insert into other table. You may or may not wish to have a column in identityTable indicating what type of record the identity value was for (eg debtor, creditor, etc).

Note that if you rollback any transactions you'll have holes in your identity numbers.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

Utsab Chattopadhyay (3/20/2008)
Thanks a lot. But per my understanding, this approach will give number as GUID (B85E62C3-DC56-40C0-852A-49F759AC68FB). Is it possible to get a number (Like identity returns) instead?
Many thanks in advance...
Regards
Utsab Chattopadhyay

Nothing automatic... you'd need to build a "sequence" table which, in your case, would have a single row in it to remember the "NextID" for such a thing. It becomes a real pain when you try to insert rows in batches of more than 1.

You could use @@DBTS somehow and convert it to a BIGINT, but I really don't think a unique ID across all tables is the greatest idea even in the presence of multiple companies or the possiblility of a future merge of companies. If you Must use that method, then NEW() ID is both the easiest, fastest, and only guaranteed method that will work without causing deadlocks or hotspots on a sequence table.

Heh... I suppose you could convert NEWID() to Varbinary and then BigInt but you'll always end up with 19 digit numbers that may be either positive or negative numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Jeff Moden (3/21/2008)
Heh... I suppose you could convert NEWID() to Varbinary and then BigInt but you'll always end up with 19 digit numbers that may be either positive or negative numbers.

Plus if you hash/recode a 16-byte GUID down to an 8-byte BigInt, you couldn't necessarily still guarantee uniqueness.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Utsab: I think that you should just tell the developer that they need a Uniqueidentifier/NEWID().

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

CDBA Hall of Fame Points: 3266 More actions · Answer 8

Thank you guys for your helps.... I asked developer to use GUID only. Alternately I asked him to write a code which will do it with a cost to performace.

I guess, as I DBA, that is the best I could do 😀

Please share your thoughts if you think otherwise....

And Happy Easter to all

CDBA

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

I would ask the developer to explain why this is necessary before proposing a solution.

It doesn’t have the feel of a real business requirement. It sounds more like an ugly hack due to a poor database or application design.