Printable View
I have a stored proc which will be entering/updating a record into a table. The table's key is an integer field which I may have to increment by one. I know I can use
declare @nextid int
set @netxid = max(id) from table
insert @nextid into table
Is some kind of lock the best way to approach this?
Why don't you use IDENTITY column and let SQL Server manage id increment.
Identity column will be an issue in replication. (if you are going to replicate)
you can create a table which holds only the maximum value
example:
MAXTable (nextvalueofcolumnid) = 101
originaltable (columnid)=100
when inserting into original table read the value from MAXTable, insert into originaltable and Add one to Maxtable.
If you are going to replicate and
If you still like to use identity in replication, then follow this article.
http://databasejournal.com/features/...le.php/3483421
sorry can't use identity
Identity columns are indeed problematic in replication, but it's manageable with proper planning. I would venture to say that identities are less problematic than trying to maintain uniqueness in this manner, especially if you are trying to maintain uniqueness across several replicated servers.
Why can't you use an identity?