It sounds to me like what they're referring to is not using an identity field but rather using an "ID Factory" table. In effect, you have a table called "id_factory" or something similar with Table_Name and Next_ID fields. The table name is populated with the names of the tables and the next_id holds the integer value for the next ID to be used by that table. When CF adds a record to a table using this scheme there are three calls to the database, one to get the ID (select next_id from id_factory where table_name='Users', one to update it (update id_factory set next_id = next_id + 1 where table_name = 'Users') and then your actual insert (insert into users (user_id, ...) values (#GetID.Next_id#, ...)) and it needs to be wrapped with a transaction for obvious reasons.
I wasn't aware that MSSQL Server identity fields needed any kind of maintenance. I asked our DBA and his comment was that if you're doing replication or bulk inserts then you need to handle the identity fields. Hope this helps. Hatton Erika L. Walker-Arnold wrote: > For "us" peoples that use an integer ID field and then use @@identity to > get the values for related tables ... Can someone explain what this > paragraph is talking about: > > "In everyday practice, it is desirable to have a "next ID" generator. > SQL Server provides such a facility, but you must be scrupulous in > applying database maintenance to get any benefit from it." > > Particularly the maintenance part .... What maintenance? Aside from > normal indexing maintance ... Am I just being brain dead today or what? > > Erika > With a K > > ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
