Identity specification

Hi,

How do I determine what the next "id" will be in a collumn where the Identity Specification is turned on with Identity Increment?

I have two tables, the one contains product information and the other is a photo album. When I create a new product, then I automatically create an associated photo album. It all works fine, but I have found some problems after using this setup for a while to manage products.

For example, say I have created 10 products (id's 1 to 10) , and then I decide to remove the last 5 products from the list. The next time I add a new product, it will be product number 6, but the id will be 11 (seeing that the id's will always be unique). How do I determine what the next Identity value will be seeing that the actual last id might not be the last one that was allocated?

Regards
Jan

You can't know in advance what the next id field will be, but you can easily find out what the id was generated and use it in your proc. SCOPE_IDENTITY () returns the last id that was generated within your scopehttp://msdn2.microsoft.com/en-us/library/ms190315.aspx and would be used as follows

SET NOCOUNT ON
SET XACT_ABORT ON // guarantees that an error rolls back the transaction

declare @.lastId as int

begin tran
insert into table1...
select @.lastId = SCOPE_IDENTITY()

insert into table2 values (@.lastId,...)
end tran

|||

Hi David

Thanks for your response. I have read everything I could lay my hands on, and the long and short of my story is that I will have to rethink my whole implementation.

Thanks in any case - I have learned something that I am sure will be helpfull in future.

Regards

Jan