Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Oracle's SYS_GUID() vs MS SQL Server NEWID()

4,331 views
Skip to first unread message

Uma Muthu

unread,
Aug 22, 2003, 3:24:41 PM8/22/03
to
I am working with two databases who share data: an Oracle 9i DB and a MS SQL
Server 2000. I am concerned that GUIDs that are generated on one DB may be
duplicated ):- on the other DB, and when we go to share data a duplicate key
scenario might arise.

Is there any way I can determine the algorithms used to generate the GUIDs
on both DBs so that I can format them (if I have to) in a way that they
will
never collide? I use sys_guid() to generate GUIDs in Oracle, and NewID() in
SQL Server.

Any suggestions would be appreciated.

- TIA -
~ Uma Muthu ~


Maximus

unread,
Aug 22, 2003, 4:03:18 PM8/22/03
to
"Uma Muthu" <Ma...@NoCoolSpamBlue.com> wrote in message
news:Z5u1b.6048$Nc.37...@news1.news.adelphia.net...

GUID's are 128 bit values, you shouldn't worry about collisions as the
chance of this happening is so fantastically small.

Daniel Morgan

unread,
Aug 22, 2003, 4:30:18 PM8/22/03
to
Maximus wrote:

I agree but at the same time it is theoretically possible. Can you put them into
ranges such that values are not accepted and another attempt made unless they
are obviously separate?

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)


Richard Kuhler

unread,
Aug 22, 2003, 4:58:53 PM8/22/03
to

Even if you knew the algorithms, you wouldn't have any assurance that
one of the vendors couldn't change it in some way that still guaranteed
uniqueness in their own product but now produced collisions across both
products. I would suggest that you add a byte and use 0 for Oracle and
1 for SQL Server, etc...

Richard Kuhler

Maximus

unread,
Aug 22, 2003, 5:07:07 PM8/22/03
to
"Daniel Morgan" <damo...@exxesolutions.com> wrote in message
news:3F467D5A...@exxesolutions.com...

Theoretically possible but very improbable (2^128 = 3.4028E+38) though it's
really dependent on the algorithm used for generation. The only way to
guarantee uniqueness is to tack on a database identifier to the GUID.


Connor McDonald

unread,
Aug 23, 2003, 9:05:05 PM8/23/03
to

Tag the oracle rows with a sequence starting at 1
Tag the sql server rows with a "sequence" starting at 1000,000,000

should be fine for the first billion rows

hth
connor
--
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"

Galen Boyer

unread,
Aug 29, 2003, 5:01:24 PM8/29/03
to
On Fri, 22 Aug 2003, asdfa...@eqeqweqwe.com wrote:

> GUID's are 128 bit values, you shouldn't worry about collisions
> as the chance of this happening is so fantastically small.

But, when the collision happens once, and he didn't account for
it...

--
Galen Boyer

0 new messages