Retrieve the next auto-increment number

HI,

I have one master table and multiple detail tables. The primary key of the master table is an auto-increment number, which is a foreign key in those detail tables. I am wondering if SQL Server allows us to get the next available auto-increment number of the master table up front. Thanks a lot.

No, you can only get it after it is created. To do this you would have to lock the table until you used the value or you could get collisions if two users were doing the same thing simultaneously.

Use scope_identity() to get the last inserted identity value.

|||hi ywchen,

You can obtain the next no. in advance by using the ff:
SELECT @.nxtId = MAX(RecId) + 1 FROM MasterTable

However, you will run into problems when multiple users will be inserting in your master table as you might get the same Id.

Since your primary is an auto-increment field, the next identity will only be available upon insert of row. From then on you will be able to get the current Id using the ff :
1.) @.@.IDENTITY - will give you the last identity inserted of any table
2.) IDENT_CURRENT(<table>) - will give you the last identity insert of a specified table, but the scope of insert is of global.i.e. You don't have control where that insert originated.
3.) Scope_Identity() - will give you the identity of a table when there is a row inserted, BUT on its current scope. i.e. Your calling insert procedure scope, regardless of any insert from other insert procedure.

HTH|||

>>1.) @.@.IDENTITY - will give you the last identity inserted of any table

This is not completely true, @.@.IDENTITY will give you the identity of the original table or any tables used for inserts in triggers on the original table

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

yup sorry for not elaborating.. and AFAIK that's the only place(triggers) where @.@.IDENTITY is useful anymore as it is supplanted by the two 2 functions for SQL2K and above...

|||Of course you have to make sure that the trigger doesn't cause another trigger to fire, again invalidating the @.@.identity :)|||Another issue that you need to be aware of is if an insert into the master table fails (eg a transaction rolls back, or an insert violates a constraint), then the identity field will still be incremented, and thus will be two greater than the max value of that column. This is due to the nature of an identity column, there is no way around this, and I would presume this would break any code that is dependent on knowing what the IDENTITY value is before it is generated.