Re: What's the best practice for primary keys?

synergy56_at_hotmail.com
Date: 07/28/04 

Date: 28 Jul 2004 09:46:43 -0700

Hi all

I'm about to start a new project and the decision on primary keys is a
tough call to make. I've spent the last day or so reading lots of
heated debate on the subject. Since this is a fairly recent thread I
thought I'd chirp in.

Past experience has given me a strong preference for meaningless
primary keys. I found this discussion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6136

Scroll through and you'll find that someone called quazibubble gives a
very long response to why he thinks meaningless keys are the way to
go. Despite the fact that he has an opinionated, angry and somewhat
obnoxious way of communicating, he makes some very valid real world
points which I find hard to disagree with.

So ... my issue now is how to create the meaningless keys. The usual
two choices seems to be to use an IDENTITY column or use GUIDs
(UniqueIdentity field in SQL Server). My last project used INDENTIYs
and it all worked pretty well but I'm reconsidering this for the next
project.

There is something very 'clean' about using GUIDs, especially if you
are doing multi-tier object orientated programming. With GUIDs, you
can generate a new key before you write to the db. Disconnected mobile
users can even create their own keys offline and then import into the
db when they connect. GUIDs also make life much easier if you need to
merge two databases since the keys are always unique.

But ... I'm still concerned about performance of GUIDs. They are
random 128 bit (ie 16 byte) numbers. In Int32 with 2 billion possible
values is only 4 bytes. There must be a lot more work going on
especially for queries with multiple joins. I've read people say
everything from "we haven't noticed any performance problems with
GUIDs" to "we went with GUIDS and we're really regretting it now". So
.. damit, its not an easy decision. I've read differing opinions from
equally well known and respected gurus. Many will say "do your own
performance test" but I'm not confident of really reproducing the real
world as it will be in a few years.

Another downside of GUIDs is that in some tables we really need to
create a somewhat meaningful key. A quote or order needs an integer ID
that can be printed and referred back to be a person so if I went
generally with GUIDs then I'd also need an incrementing number field
which would need to be indexed so now I really have two indexes.

Also, with a web application, its kind of nice to have urls like
www.example.com?id=123

With IDENTITY fields you need to query SCOPE_IDENTITY (don't use
@@IDENTITY) to find out what ID you just created. Also its vendor
specific which may be important if you ever want to move away from SQL
Server. I think most databases have something like it but they're all
different.

An alternative that I'm leaning towards is to have a table of 'Next
ID' numbers for each table. A stored procedure like this returns the
next ID and increments:

CREATE PROCEDURE GetUniqueID (@tablename varchar(20))
AS
declare @ID int
UPDATE UniqueIDs
SET @ID = ID, ID = ID + 1
WHERE TableName = @tablename
return @ID
GO

I believe that is safe without further locking. An alternative sproc
could return a range of numbers in one go although I think that will
need some locking.

This method gets the efficiency of using int keys and allows me to
know the ID before writing to the db. It also lets me easily set the
range and is vendor independent. It doesn't help with disconnected
users which we don't have anyway but I guess if necessary then I could
give them their own number range. That's a bit messy but ... probably
workable. We're not too concerned about 'holes' in the sequence if I
grab an ID but then don't use it.

Well ... I'm rambling a bit and maybe this is not quite the question
that Jay was asking. I'm interested in hearing any real world
experiences.

Cheers
Ross

"Jay Douglas" <jayREMOVEIFNOTSPAM@squarei.com> wrote in message news:<eociM6oaEHA.3480@TK2MSFTNGP11.phx.gbl>...
> Another theoretical SQL development question:
>
>
>
> Our development team has had several heated discussions over what a primary
> key should be in SQL tables.
>
>
>
> Some say that every table should have an auto increment int ID field for the
> primary key allowing for ease of use in development and relations. For
> example:
>
>
>
> PK CatId (int autoinc)
>
> CatName (varchar)
>
> CatDob (datetime)
>
> CatSex (char)
>
> FavoriteToy (varchar)
>
>
>
> Then I've seen some arguments that an auto increment field isn't really
> relevant to the data and the actual data should be used as a primary key.
> For example:
>
>
>
> PK CatName (varchar)
>
> PK CatDob (datetime)
>
> PK CatSex (char)
>
> FavoriteToy (varchar)
>
>
>
> I was wondering what you feel the best practices are and for what reason.
>
>
>
> Thanks in advance for your input . I'm excited to see what comes out of this
> inquiry.

Relevant Pages

  • Re: Trend towards artificial keys (GUIDs) sez my textbook...is AI next?
    ... after the author said he's a "big fan" of such GUIDs. ... without surrogate keys. ... They MIGHT be introduced during physical design, ... For example, when an employer assigns a number to every employee, prints it on a card and tells each employee to include their number on all correspondence, the employer is inventing exactly such a key. ...
    (comp.databases.theory)
  • Re: Trend towards artificial keys (GUIDs) sez my textbook...is AI next?
    ... after the author said he's a "big fan" of such GUIDs. ... But in the rest of his book, he adds a surrogate key (either GUID ... I believe that logical design should be done completely ... without surrogate keys. ...
    (comp.databases.theory)
  • Cracked the about:blank problem!!
    ... REGEDIT will return several keys in HKEY_CLASSES_ROOT ... search the registry for these 2 GUIDs. ... restart in Safe Mode with Command Prompt. ...
    (microsoft.public.windows.inetexplorer.ie6.browser)
  • Re: Autoincremental field in VFP7
    ... ALTER TABLE x ALTER COLUMN keyfield Integer NOT NULL PRIMARY KEY ... MCSD, Visual FoxPro MVP ... This type of problem is exactly why I have stayed away from integer keys ... GUIDs for primary keys. ...
    (microsoft.public.fox.helpwanted)
  • [TOOL] Keyfinder - Cryptographic Analyzing Tool
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Keyfinder analyzes files for public/private keys, ... It identified such areas by measuring the entropy, ... int ext_entropy; ...
    (Securiteam)