How to read automatic increase filed befor inserting to database

Question

Hi

I have [UnicLine int identity(1,1)] filed that increase his value

How I can read its value befor inserting new record to database ?

thank's in advance

Answer 1

You should never need this, what you can is to output the newly created id for example like:

INSERT INTO [table]
SELECT field1, field2;
SELECT SCOPE_IDENTITY();

and get that ID in your ADO/ORM.

But to answer your question you can do this:

SELECT top 1 [UnicLine] + 1 as newIDValue FROM [table] ORDER BY [UnicLine] DESC;

or simple

SELECT MAX([UnicLine]) + 1 as newIDValue FROM [table]

---

not as part of the question but read about

SCOPE_IDENTITY() vs @@IDENTITY vs IDENT_CURRENT('tablename')

to know the differences and choose the right one to use_

Answer 2

If you just want to see the max value that is currently in the table, you can do this:

select max(UnicLine) from mytable

However, this will not necessarily tell you what the next value will be in the identity field. If there were inserts that were rolled back, the identity would have been incremented for each, so your next insert might get a value that is greater than the current max + 1. I agree with balexandre, there usually isn't really a reason why you'd need this.