>I am upsizing a database from Visual FoxPro to SQL Server 7.0, and am wondering what to do about primary keys. In VFP, I am in favor of using surrogate primary keys. Is this the right was to go with SQL Server? If so, should I use an Identity column? I am reading a preview of Client Server Applications with Visual FoxPro and SQL Server 7.0, that seems to indicate that Identity columns are not a good was to go.

I use surrogate primary keys in VFP also. Identity column are just an easy way to auto number the next row inserted. But they do present a few problems as does just about any way that you try to id the next row.

The old standby method of keeping a table of next ids has its problems also, i.e., you get the last number, add one to it, just as someone inserts a row and a new number before I save mine.

The difficult part of identity columns, and I haven't come up with a satisfactory method, is when their used as foreign keys and they must be updated at the same time as the parent. As in invoice header and invoice details. You would want to handle it all in a transaction, updating the invoice header then the details.

Maybe Michael Levy can give us an elegant way.