get next ID number

[ober0330]

In SQL Server 2000, with an autonumber ID field, how do you get the value that will be the ID of the next record (without setting it)?

[mendhak]

SELECT TOP(autonumberidfield) FROM tablename

and add 1 to it!

But keep in mind, if another user INSERTs before you do, you won't get the right number.

[vb_dba]

Use the IDENT_CURRENT() function:

Select IDENT_CURRENT('table_name') + 1

IDENT_CURRENT returns the last identity value added to a table. Incrementing that number by one should return the next number.

[ober0330]

Originally posted by mendhak
But keep in mind, if another user INSERTs before you do, you won't get the right number.

Ok, thanks guys... and that won't be a problem... it's more for display than anything else. They actual update will grab the true value and it isnt' necessary for them to know the value anyways.

[ober0330]

Ok, neither of those work and I just realized that I'm still using my test server (SQL Server 7). I haven't migrated to the SQL 2000 Server yet.

Anyone know how to do it with Server7?

[DeadEyes]

try
SELECT ISNULL(MAX(autonumberidfield),0)+1 AS NextId FROM tablename

[ober0330]

Nope, that doesn't work either. That grabs the next one in sequence, but there have been several deleted and thus the number it should be grabbing should be higher than that. (I'm getting 737 and it should be more like 750 something)

[ober0330]

*bump*

[Mongo]

Not near a 100% solution, since I've no v7 to test or validate this on. But I hope it helps or
gives you an idea or two.

-- As vb_dba says in mssql2k+:   SELECT IDENT_CURRENT('tablename') + IDENT_INCR('tablename')

SELECT name
  FROM sysobjects
  WHERE  type = 'U' AND IDENT_SEED(name) IS NOT NULL

SELECT 
  IDENT_SEED(OBJECT_NAME(id)) AS Seed
, IDENT_INCR(OBJECT_NAME(id)) AS Increment
, OBJECT_NAME(id) 
, autoval
  FROM syscolumns
  WHERE (status & 128) = 128

-- see MSDN Q104829 for sp_hexadecimal 
declare  @binvalue varbinary(256),    @hexvalue varchar(256)
SET  @binvalue = (SELECT autoval FROM syscolumns WHERE name = 'IdentFieldName')
EXEC sp_hexadecimal @binvalue,  @hexvalue  OUTPUT
--print  @hexvalue 

--     curIdent___   Step_______   Seed_______
--  0x FE 08 00 00   01 00 00 00   01 00 00 00 82

DECLARE @hexstr varchar(10), @curIdent int , @NextStep int , @cmdstr nvarchar( 255 )
SET @hexstr = '0x' + substring( @hexvalue, 9, 2 ) + substring( @hexvalue, 7, 2 ) + substring( @hexvalue, 5, 2 ) + substring( @hexvalue, 3, 2 )
SET @cmdstr = N'SELECT @curIdent = convert( int , ' + @hexstr + ' )'
EXEC sp_ExecuteSql @cmdstr, N'@curIdent int out', @curIdent out
--SELECT @curIdent
SET @hexstr = '0x' + substring( @hexvalue, 17, 2 ) + substring( @hexvalue, 15, 2 ) + substring( @hexvalue, 13, 2 )  + substring( @hexvalue, 11, 2 )
SET @cmdstr = N'SELECT @NextStep = convert( int , ' + @hexstr + ' )'
EXEC sp_ExecuteSql @cmdstr, N'@NextStep int out', @NextStep out

SELECT @curIdent + @NextStep NextIdentVal

Ah, the ways I love to not miss pre-2K days!

;-)

[ober0330]

wow... it's not THAT important. Thanks anyways.

[pjones]

Try looking at @@identity in books online, I'm sure that has something to do with it from memory.