By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

manually creating unique IDs

P: n/a
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
Nov 18 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <no*****@nospam.com> wrote in message
news:uP**************@TK2MSFTNGP12.phx.gbl...
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

Nov 18 '05 #2

P: n/a
> 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
Nov 18 '05 #3

P: n/a
<<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....36&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
Nov 18 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.