Hi Trey,
Thanks for the input. One question though, if the identity columns work reliably, why use compound data primary keys? IOW, why do both unique integer key for a propogated foreign key and keep a data primary key on the table? It sems to me that if the integer key is unreliable, your database is screwed and if it is reliable, not why not use it as the primary key on the table itself?

Gary



>>Hi all,
>>I'm recommending the use of identity columns for primary keys for a database I'm working with as opposed to a key generation proc and table scheme. I've never had a problem with identity columns but want to toss this question out to the group. Does anyone know of any downsides to identity columns?
>>
>
>As a row id, setting the identity property works quite well, as long as you don't care if there are ever any gaps.
>
>[you're probably already doing the following in some way, but i figure i'll go ahead and mention it]
>Make absolutely sure, though, that you have a logical primary key [i.e., unique attribute or set of attributes] made up of real data for each table. You can implement this physically by putting the primary key constraint on the logical primary key attribute(s), and putting a unique constraint on the identity column. To make FK references to your tables, the referenced column doesn't have to be the one with the primary key constraint on it - it can have a unique constraint on it.
>e.g. say you need Invoices and details. In this case, let's say that to the app user, a unique invoice means the invoice they sent to a particular client on a particular date. In the database, the auto-generated invoice number is better suited for foreign key references and searches. So for these two tables, you might could use a schema like this:
>

>create table Invoice (
>  InvoiceNo int identity constraint ID_Invoice_InvoiceNo unique clustered,
>  CustomerID int not null,
>  InvoiceDate datetime not null,
>  constraint PK_Invoice Primary Key (CustomerID, InvoiceDate) nonclustered
>)
>
>create table InvoiceDetails (
>  InvDetID int identity constraint ID_InvDet unique,
>  InvoiceNo int References Invoice(InvoiceNo),
>  ProductID int not null,
>  Quantity int not null,
>  UnitPrice numeric(15,2) not null,
>  constraint PK_InvDet Primary Key (InvoiceNo, ProductID) clustered
>)
>

>
>Now, you can swap the constraints and put the PK constraint on the identity column, and the unique constraint on the logical primary key column(s). I've started to find it less confusing to keep the logical pk the pk constraint, but I think it's because I'm developing Teflon(r) brain... :)
>
>>Also, we are going to have some free form comment fields in the database and I was thinking about using text fields. Any opinions? Thanks.
>
>Depends. In SQL2K, varchar columns can go up to 8000 wide, so that may be big enough.
>Either way, if you need to search these free-form columns, take advantage of the full text search capabilities. It works quite well.
>
>HTH
>
>Trey