Results 1 to 5 of 5

Thread: locks for a max value

  1. #1
    Join Date
    Nov 2002
    Posts
    261

    locks for a max value

    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?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Why don't you use IDENTITY column and let SQL Server manage id increment.

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  4. #4
    Join Date
    Nov 2002
    Posts
    261
    sorry can't use identity

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •