Billy
Reputation: 15706
Auto-increment the primary key without setting the identity
I am using the following syntax to insert new records (I don't want to have duplicate keys):
insert into tbl(key) values select max(key)+1 from tbl
Someone says it will have concurrency problem.
Is that right?
SELECT -> LOCK table -> INSERT
or
LOCK table -> SELECT -> INSERT
Which one of the above is correct?
Upvotes: 3
Views: 354
Answers (3)
mevdiven
Reputation: 1902
If you're doing replication, your best bet is to use GUID's as a primary key and also "please" do not create clustered index on that column.
Upvotes: 1
Otávio Décio
Reputation: 74270
The select will lock the table, and you are doing everything in a single statement, looks like you should be fine (other than maybe performance to calculate the max(key)).
Upvotes: 0
Related Questions
- Adding auto increment column other than primary key
- Auto-increment primary key in SQL tables
- auto increment on composite primary key
- Auto increment a primary key column
- Auto increment field not primary key on SQL
- How to increment a column value in a table automatically without identity and primary key in SQLServer
- How can I auto-increment a column without using IDENTITY?
- Set autoincrement on a primary key without recreating the tables
- SQL Server auto increment a column without primary key
- Auto Increment Primary Key Integrity