Next identity

[PLV]

I'm using sql server 7.0...

How can I extract the next identity of a table column?

the best solution I've got is:

dbcc checkident(tbl_PHA_Info) 'Refresh the field

select autoval from syscolumns where id = object_id('tbl_PHA_Info') and name = 'id'


The result looks like this:
0x20000000010000000100000082

I don't know how to extract the 0x20 (wich is the 32 I need)from the result to get the last value of the identity.

Can someone help me convert this varbinary value to get in integer format 0x20(32) 0x01(1) 0x01(1) 0x82(doesn't matter!)?

Can someone confirm me that this method will work all the time?

Is there a better way to do this?

Thx in advance,
Chris

 

[MeanGreen]

From BOL, the @@Identity is used to get your most recent Identity column. Example:

This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

You can read more about it in BOL under the @@IDENTITY section.

Hope this helps.

 

[PLV]

The trouble with this is that if someone inserts in another table or there is delete in the table, this value becomes rapidly not accurate.

Or it is a trouble if the las inserted record in another table was from a table having a lower key the the maximal value of the table I want to insert into....

 

[MeanGreen]

Using this method, provided there is not a trigger against the table, you will have the identity column created for your input. It works well even in a multi-user environment. Once an identity column has been incremented it will not reallocate that number. So if you do a rollback, it won't matter, as the identity column has moved on and it will not go back to pick up that number.

Hope this helps.

 

[tlbroadbent]

If you use SQL 2000, there are three functions that return identity values, SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY. See SQL BOL for details and examples of the use of these functions. The following comes from the topic "SCOPE_IDENTITY" in BOL.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

@@identity may not return the value you want if a trigger fires and also inserts an identity value. In this case, SCOPE_IDENTITY would be the more appropriate function. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.