hi
     Is there a way to tell which number is going to be assigned next in an auonumber field in SQL?

SELECT MAX(AutoIDField) As NextID From Table

Thank you shahchi1

This only seems to return the last record id in the table not the next autonumber field to be assigned

SELECT MAX(isnull(AutoIDField,0)) + 1 As NextID From Table

Om Prakash

 SELECT MAX(isnull(AutoIDField,0)) + 1 As NextID From Table

Finds the next nemeric cnumber in the list, but lets say i have 10 records.. id's 1 through 10 and delete the last 5. Then i have records 1 to five, but the next autonumber field is going to be 11 unless i compress the database which i do not want to do.

SELECT MAX(isnull(AutoIDField,0)) + 1 As NextID From Table

--Yes: this will give the max + 1 record from the data. If u delete last 5, then next number is 6, and not 11.

Even if you read the next number as 11, then it is possible that by the time u display the data & add, somebody has populated the data.

You can display the autonumber after inserting the data. which will be 11, if your column is autonumber.


Om Prakash

What Om Prakash told is correct. This method is not thread safe. Not correct also. If last record is deleted, you will get back the id of that record in this method whereas the actual id will be one more than that.

Could you please specify why you want to get that value ? If it is for inserting child records of a different table, there is another method. You can write a stored procedure to do the insertion into the parent table and get back the new id generated. This you can use to fill child record.

A sample stored procedure for this is given.

CREATE PROCEDURE sp_insert_record(@value1 varchar(50), @value2 INT, @id INT OUTPUT)

AS

INSERT INTO table(val1, val2) VALUES (@value1, @value2)

SELECT @id=@@IDENTITY

thanx madhukp
     this is exactly what i was getting at

is there a way to autogenerate a number and insert it into 2 table inside a strored procedure? What i am trying to do is add a record to one table, and the autonumber field from the parent table will automatically go into the child table.