AutoNumber in SQL 2005

Hello friends,

I am using Sql Server 2005 in my web application. In one scenario I had made one field of a table as Autonumber by keeping its datatype as int and then setting its Identity flag to true. Now is there any inbuild function that can give us the next number tat will be generated if a new record will be inserted. This is needed in the case if last 1 or 2 records are deleted for e.g.

last record has value 7 and if the record is deleted still for the new record the number generated will be 8 i.e. after 6 there is 8. So by any means can we get the next number, or can we keep that autonumber to move serially even in case some record is deleted it should go for the max + 1 number.

Please let me know.

Thanks & Regards
Girish Nehte

sql server doesn't guarantee sequential order numbers, though there are some tricks to force it (reseeding the table via DBCC checkident). I'd recommend that you not rely on a function that assumes the max+1, but instead use scope_identity to return the actual value -http://www.sqlteam.com/article/understanding-identity-columns andhttp://www.sqlteam.com/article/creating-a-sequential-record-number-field are worth reading to detail you some options.

|||

Why should you care what the next number will be? If this is important then there is something conceptually wrong with your design. Identity columns are inherently dataless, they have no intrinsic meaning. What are you trying to accomplish?

|||

Hi dbland,

Actually I have to save two records in two different tables and after one records save I have to take fetch the identity of that record and set that identity aas the fireign key for the second records but as both the records we are entering are in the same transaction i.e. after both the queries executed we commit the transaction due to which before commiting we are unable to get the identity key of first record which we will be needing in entering the second record.

I dont know whether I was able to make clear my requirements or not but if you please got it let me know any better way to accomplish it.

Thanks & Regards
Girish Nehte

|||

i think if you are still going to implement this then use max in the query to retrive the highest figure in the column.like select max(id) as maximum from ur table.

|||

Thanks, Girish, for that clarification. What you want to do is very common and easily accomplished using the SCOPE_IDENTITY() function (seehttp://msdn2.microsoft.com/en-us/library/ms190315(SQL.90).aspx). Here's an example

declare @.LastId int
begin tran
insert ... into table1
select @.LastId = SCOPE_IDENTITY()
insert ... @.LastId,... into table2 (...FK_field,...)
commit tran

In my example, I save SCOPE_IDENTITY() to a variable and use the variable, this is not necessary, I just consider it good programming practice to always save @.@. fields and the like into temp variables so they don't get overwritten.

If you rollback the identity counters will still have been incremented, but it doesn't matter.

BTW, note in the MSDN article how SCOPE_IDENTITY() differs from @.@.identity