How do I find the highest Unique Identifier?

[stacey]

I'd like to know the current value of my uniqueID column before I
create a new record.

Is there a way to find out this value?
It is numeric in my case, but I can't just look for the MAX value,
since some records may have been deleted, and the value for the
uniqueID still stays at the higher value.

Is there a way to read this internally kept value?

[Stu]

Stacey,

It sounds like you are using an IDENTITY column; if that's the case,
then you can use IDENT_CURRENT to find the last generated IDENTITY
value.

HTH,
Stu

[--CELKO--]

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. I hope you know never to use
IDENTITY as a key.

[stacey]

Stu - Thanks!
That was exactly what I was looking for!

And CELKO - I'm sure everybody reading this thread will appreciate your
useful contribution...

[David Portas]

Do NOT assume that the next IDENTITY value will equal IDENT_CURRENT +
1. In a multi-user system you cannot reliably predict the next IDENTITY
value to be inserted. Nor should it be necessary to do so.

Stacey, if you explain your requirement fully I'm sure we can help you
with a better solution.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Stu (st**************@gmail.com) writes:

It sounds like you are using an IDENTITY column; if that's the case,
then you can use IDENT_CURRENT to find the last generated IDENTITY
value.

But beware of that IDENT_CURRENT is not safe from other processes. That is,
if you call IDENT_CURRENT before you insert a row, and the call
scope_identity() to see what you actually got, they may not be the same.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp