How to know the next ID ?

Hi all,

is it possible to know the next record's identifier of a

table without inserting it?

I tried with SELECT MAX(ArticleID) FROM Articles but imagine that there is no record in the table (but there was of it), the SELECT MAX statement return a null value...

Thanks in advance,

Gilles

Why do you need the next id ? What datatype is the column ? Does it have the identity property ? Normally there is no need to get the next id prior to a transaction.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||If you just want to know the next value, then try the DBCC CHECKIDENT statement. Here's an example:

DBCC CHECKIDENT ('dbo.TableName', NORESEED )

Have a look here for details: http://msdn2.microsoft.com/en-US/library/ms176057(SQL.90).aspx

If you need this value programatically, then I might suggest that you may be doing something incorrectly, as this value shouldn't be required... (this functionality is meant to be hidden by the system - it's a key value only, and shouldn't be manipulated).|||

Hi,

You are right both... But, in fact, it's just to display an information. 'cause my application can add an "estimate" (I don't know the correct word in english) or an invoice and I'd like to display the next identifier so that it serf of reference... But it's a detail...

Thank you for your contribution

Gilles

|||If you really need to do that, then I'd suggest using something like this:

SELECT (ISNULL(MAX(IdntityColumnName), 0) + 1) AS NextId FROM [TableName]

... of course keeping in mind that if two people are using your app at the same time, they'll both get the same 'estimated' ID. This example is also assuming that your identitiy records are incremented by 1 (the default, but not the necessity).|||You have to keep in mind that this estimated id is only "safe" in a oneuser enviroment, due to the already mentioned reasons by Christoph. It is NOT preferable to use that in these days, because normally more than one user will connect to a database to manipulate the data (if not now, perhaps later, themost application are brought to a wider usergroup over the time and therefore ported from a one user enviroment, and its the heck to correct all this when your application is established and stable)

Better use Transaction, whereas you get a transaction scope the next ID, while locking the table for other users to prevent the concurrency conflicts.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Yes, that's right... but i think about what you write and, initialy, it will be a single user application... The problem is I don't have your level about programming SQL. I can work with ADO.Net but I don't know enough to do advanced stored procedure...

Thank you very much for your support. Now I know that such things exist and I'll be able to be interested in it later...

Thanks and have a good nigth...

PS : Can you just tell me where I can watch to study the things that you talked about... Thanks

Gilles

|||For most Microsoft development products, simply do a Google search for "Quickstart Tutorial" - there's usually something there. Tutorials are an easy way to learn.

For ADO.NET / SQL, have a look at:
http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/ADOPlusOverview.aspx|||

Hi Gilles,

Try this instead: "SELECT IDENT_CURRENT('tableName') AS currentIdentity"

That must work.