I'm somewhat intrigued by your statement, "...SQL Server isn't ever going to
go back and [reuse an ID] and that bugs me".  You are "bugged" that a bigint
deleted ID isn't re-used, but presumably you are not bugged that a guid
isn't re-used (it better not be :-).  It isn't so much "nitpicking" as that
I don't understand the difference between the two situations and why one way
is acceptable to you and the other isn't.

I point out that the length of a bigint is 8 bytes and you know that that of
a guid is 16 bytes.  I don't think the difference in key sizes is hugely
significant, and I doubt you would be able to measure any significant
performance difference in queries or updates between the two, although there
would be some because the guid will make both data rows and index entries
larger, and thus less efficient.

I presume that this is purely an internal value, never exposed to an
end-user.  Just out of curiosity, why are you using such a key at all?  Why
not use, say, a combination of a user identifier and the datetime as the
'Search_key' and forgo an artificial key completely?

But, whatever works for you...

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Jerry Diegel [mailto:diegelj@g...]
Sent: Tuesday, August 27, 2002 2:41 PM
To: sql language
Subject: [sql_language] RE: bigint to uniqueidentifier(SQL 2000)


Simple, because once there's a gap, SQL Server isn't ever going to go back
and say,"Oh look, 2 was deleted, we can use 2 again," and that bugs me.  On
the otherhand, NewID(), from what I understand, isn't going to duplicate
that feature, since it's random, and I don't care what order the records
were created in.  Call it nit picking if you'd like.

However, in researching this issue I found the following:

"At 16 bytes, the uniqueidentifier data type is relatively large compared to
other data types such as 4-byte integers. This means indexes built using
uniqueidentifier keys may be relatively slower than implementing the indexes
using an int key."

Which I find to be a much better argument then "There are gaps galore in any
set of guids..." Hence, I'm going to stick with the bigint.

Thanks

Jerry


-----Original Message-----
From: Jeff Mason [mailto:jeffm.ma.ultranet@r...]
Sent: Tuesday, August 27, 2002 12:13 PM
To: sql language
Subject: [sql_language] RE: bigint to uniqueidentifier(SQL 2000)


So what if there are gaps?  Why do you care?  (There are gaps galore in any
set of guids...)

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Jerry Diegel [mailto:diegelj@g...]
Sent: Tuesday, August 27, 2002 12:30 PM
To: sql language
Subject: [sql_language] RE: bigint to uniqueidentifier(SQL 2000)


The tables in question are going to be used for storing specialized session
data.  There's a main table, that will hold the primary search_key, then a
handful of other tables where that search_key acts as foreign key.  Say a
user starts a search and selects n companies from a list containing
thousands of companies.  At that point, the plan is, to create a new search
session for that user, go to the Company_Criteria table, and store those n
companies, one per row, with Search_Key as a foreign key, along with a
couple of other columns dealing with what the company does for a living.
There's a couple other tables for other criteria that act in a similar
manner.

I started out with Search_key being a bigint, identity, increment by one.
Then I started picturing all the searches that never got finished, and were
subsequently deleted, leaving gaps in the key sequence, and started thinking
uniqueidentifier.

Is that a horribly bad idea?  This is all still in very early stages of
development, so any suggestions would be greatly appreciated.

Thanks,

Jerry

-----Original Message-----
From: Jeff Mason [mailto:jeffm.ma.ultranet@r...]
Sent: Tuesday, August 27, 2002 10:54 AM
To: sql language
Subject: [sql_language] RE: bigint to uniqueidentifier(SQL 2000)


How would you articulate the rule that would govern the conversion of an
integer value to another value that by definition is guaranteed unique no
matter where or when it is run?

You can't; the 'conversion' is nondeterministic which is why the conversion
is not allowed.

Throw away the bigint column and just add a column of type
'uniqueidentifier'; update the table and set values for the new column.  If
you are using SQL Server, you can generate uniqueidentifier values via the
NEWID() function.

Why do you want to do this?  Unique identifiers generally make lousy primary
keys.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Jerry Diegel [mailto:diegelj@g...]
Sent: Tuesday, August 27, 2002 10:25 AM
To: sql language
Subject: [sql_language] bigint to uniqueidentifier(SQL 2000)


I need to convert several primary keys from bigint to uniqueidentifier, but
keep getting the following error...

"Conversion from 'bigint' to 'uniqueidentifier' is not supported on the
connected database server."

Any suggestins?

Thanks,

Jerry