Re: choosing primary key datatype

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 04/29/04 

Date: Thu, 29 Apr 2004 14:00:00 -0700

>> I was wondering if anyone might be willing to provide some insight
into the best datatype to use for primary keys in SQL Server 2000.<<

Why, we all know it is a Hebrew word that is exactly 19 characters long.
This is because Adam named all the entities in the Universe that way in
the Year 4004 BC. But you have to look very closely on the bottoms of
all entities to see it; it has gotten a little worn since God put it
there.

Sill answer? Sure; this was a silly question. **By definition** a
primary is a subset of attributes that uniquely identify an entity.

When you research your data model, you first look for industry
standards. Vehicles have VIN numbers, retail goods have UPC, books have
ISBN, etc.

>> From what I have read, in situations where uniqueness is required
across servers, GUID's work well. <<

This is how MS does replication. It has nothing to do with keys.

>> Would appreciate any opinions or links to good articles that discuss
this issue. <<

It just went out of print, but see if you can get a copy of my DATA &
DATABASES. I have chapters on the design of encoding schemes and the
natgure of keys.

There is no "magic, universal, one-sizes fits all" answer.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: backup Keys
    ... "Back up the SQL Server 2005 encryption keys by using the corresponding SQL ... Regarding on the symmetric and asymmetric keys in SQL Server 2005 database, ...
    (microsoft.public.sqlserver.security)
  • Re: Why is this Not Updatable?
    ... have a Memo field. ... are there proper keys established on the SQL Server ... > warning that no changes can be made), do the primary key columns ...
    (microsoft.public.access.queries)
  • Re: Trying to optimize a query with a bunch of INNER JOINs
    ... Yes, I do have foreign keys on the main table, as well as primary keys on ... > Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.programming)
  • Re: why default index for primary key is clustered?
    ... >> So why SQL Server created clustered index by default for primary key ... When you had sequential tape files, ... A master tape would be read in sorted order ... Please post DDL, so that people do not have to guess what the keys, ...
    (microsoft.public.sqlserver.programming)
  • Re: Why is this Not Updatable?
    ... Bill To Info field instead of the BillToID field. ... are there proper keys established on the SQL Server ... > warning that no changes can be made), do the primary key columns ...
    (microsoft.public.access.queries)