Identity Columns

[Previous] [Next]

In Chapter 13, "Implementing the Managers and Data Access," the section "Saving Data for a Horse" describes an identity column solution different from the one described in Chapter 4. The solution described in Chapter 4 makes use of the identity column function in SQL Server. The problem with the identity column solution is that ADO doesn't let you ask for the value assigned by the IDENTITY property in SQL Server, at least not if you use client-side cursors. And you must use client-side cursors in a distributed application because you can't disconnect a recordset from the database if that recordset uses server-side cursors. This makes SQL Server identity columns less well suited for use in Windows DNA applications because such applications are by definition distributed.

Some people might try to convince you that SQL Server identity columns work well in many situations because you don't need to ask for the value assigned to an identity column. But beware! What's not obvious today might still happen tomorrow. Perhaps a new type of transaction, unknown to you when you design your first version of an application, one day needs to ask for that value. If that day is far away, many transactions and applications might depend on the IDENTITY column property. If so, it might not be all that easy to live up to the new requirement. So before you take a stand for using the SQL Server IDENTITY column property, think about it once more. Using the solution presented in Chapter 13 might be a better way.

The Chapter 13 solution makes use of a GetANumber method that solves the problem of the SQL Server identity column. It returns, to a data-modifying client component that's using it, a unique ID number for the table specified by the client. The GetANumber method assigns this number to the record to be inserted, and also to any child record, if applicable. Furthermore, the solution to the identity key column problem put forward in Chapter 13 is a general solution, possible to use for all tables that use a synthetic key.