CODEWITHSUNDEEP
sqlsql-servert-sqltransactions
DanM
DanM

Reputation: 7197

SQL Server: reasonable "auto increment" techniques?

I'm using SQL Server 2005 for the first time, having mostly worked with MySQL in the past. I'm used to using auto_increment to create unique IDs in tables.

Anyway... I'm working in a java app, and need to do the following. Presume my table has two columns: itemID (int) and itemValue(int).

This is basically what I want to do (the dbconn methods are just pseudocode):

dbconn.execSQL("begin tran");
int nextID = dbconn.execSQLSelect("select max(itemID)+1 from itemTable");
dbconn.execSQLInsert("insert into itemTable values " + nextID + ", 1000");
dbconn.execSQL("commit tran");

Will the begin/commit tran statements deal with the possible race condition between lines 2 and 3? Or is there some TSQL equivalent of MySQL's "lock table" that I need to do?

Upvotes: 0

Views: 669

Answers (2)

SLaks
SLaks

Reputation: 887479

You should make an IDENTITY column.

Upvotes: 2

Ender
Ender

Reputation: 949

Just make the column itemID IDENTITY ON and let MS SQL engine handle it. Handling identity incrementation on your own is very very unstable.

Upvotes: 7

Related Questions