Posted by David Portas on 03/13/06 21:51

pb648174 wrote:
> 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.

Continue to use IDENTITY. Just declare the other key as well. Does my
solution work for your original problem?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]