manually creating unique IDs

[Darrel]

I have a table that stores the content for a web site I'm making. Each one
has a unique ID so I can grab it via the querystring.

Since it's being done via a query string, I didn't want to use default
GUIDs, since they're just really verbose.

So, I'm just making my own. What's the best way to check for uniqueness on
insert? My thinking was that I'd query the ID field, set it to an array, and
then loop through all of the variables. ie:

----------------------------
count = 1

while any variable in array = count then
count = count+1
end while

new item's ID = count
-----------------------------

That would work, but could get extremely intensive if it were a large db.

So then I thought perhaps I'd just grab all IDs, and then sort the array,
grabbing the highest one and just add one to it. That would work too.

Are there other methods I should consider? Is there a way (via SQL?) to
automatically enter in a unique ID without needing the huge GUID?

-Darrel

 

[Guadala Harry]

Why re-invent the wheel?

Have you considered using an IDENTITY property in your table definition?
(SQL Server calls it IDENTITY, I think in Access it's "AutoIncrement").

--GH

 

[Darrel]

Have you considered using an IDENTITY property in your table definition?

(SQL Server calls it IDENTITY, I think in Access it's "AutoIncrement").

That's exactly the solution. Thanks! (Still new enough to SQL that I didn't
know about this one!)

-Darrel

 

[Guadala Harry]

<<Still new enough to SQL that I didn't know about this one!>>

Well, welcome to the party!

If you are new to the game I'll forewarn you that many database design
experts promote the idea that one should never or practically never use an
IDENTITY property as a primary key to a table. These experts call it a
"surrogate key" and will tell you to always use a "natural key" instead. If
you post questions about primary keys in the SQL Server news group you'll
get pounded if you talk about IDENTITY (mostly by Joe Celko). What you
should be aware of is that there are most definitely times to use an
IDENTITY or even a GUID column for a primary key. What's important is to
know when to use which - and if you do want surrogate keys - how best to
implement them in your db design.

Here is some excellent reading arguing FOR the use of surrogate keys
(IDENTITY and/or GUID):

http://www.dbpd.com/vault/9805xtra.htm

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6136&whichpage=1
This second link is to a lengthy discussion that is highly entertaining and
enlightening. This discussion started in February 2001 and the most recent
post was in July 2004. It might take an hour or so to get through it, but it
definitely contains all points of view on the whole "surrogate vs natural
key" issue. The first couple of dozen posts are of the normal "pissing
contest" variety - but a few pages into it this guy goes off on making the
case FOR surrogate keys. He has a bunch of interesting numbers and
compelling rationalle. Enjoy!

-GH