Posted by pb648174 on 03/13/06 21:21

As far as DDL, just assume a simple lookup table, Id and Description.

The main reason I use identities is for ease of use. Sometimes I use
natural keys, when they make sense and are a fairly small number of
columns, but other times we have tables that are basically a lookup
table, Id and Description. I suppose I could make the 500 character
description the primary key and only have a single column, but there
QueryString length limitations to deal with, index performance and
other things to worry about with having a large piece of text as a
primary key. I also don't like the idea of having that description data
duplicated when used as a foreign key for all the other tables that
relate to it. If that is just a personal preference and not an actual
problem, I don't know, but as Donald Rumsfeld says, you go to war with
the army you have.

Sure, we could re-architect the solution to make Celko and others treat
us nicely, but that would be hard to sell to the customers - 1 yr delay
to rewrite the software so purists are happy in the discussion forums.
As of now a serializable transaction seems to be the best way to handle
this issue in my mind using my original posted code since we can't also
force our customer to spend 25K to upgrade their SQL servers.

[Back to original message]