August 25, 2009 at 8:33 am
Hi Guys,
I was wondering if there is a more efficient way of getting the max value from an identity column instead of using the code below:
Select max(IDValue) from table.
This function must be able to run outside an insert. As I know one can use the @@identity value, but this can only be done within an insert.
Thanks.
August 25, 2009 at 8:40 am
With the amount of info you're giving us, it's fairly hard to tell. What is it you're trying to do?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 25, 2009 at 8:55 am
I have a function which selects max(ID) from a table.
The column ID is an identity column, I am wondering if there is a built in function in SQL that will get the MAX(identity column (ID)) from a table.
So in effect, is there another way to rewrite
Select MAX(ID) from table
using a built in identity function, if it exists.
Thanks.
August 25, 2009 at 9:16 am
I am not sure what you are attempting but
SELECT IDENT_CURRENT('table')
will return the last identity value generated for a given table.
If the last insert was rolled back, this will not be the same as MAX(ID).
August 25, 2009 at 9:26 am
I do not prefer IDENT_CURRENT as it may give you wrong results (as it's on table across scope). The scenario I fear is a peak load time when 50 transactions are in middle of a transaction and at the same time you fetch the value and use it. The transaction may rollback and you get an identity value that doesn't exist.
Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENTITY_INCR because of insertions performed by other sessions.
August 25, 2009 at 9:39 am
rjv_rnjn (8/25/2009)
I do not prefer IDENT_CURRENT as it may give you wrong results (as it's on table across scope). The scenario I fear is a peak load time when 50 transactions are in middle of a transaction and at the same time you fetch the value and use it. The transaction may rollback and you get an identity value that doesn't exist.Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENTITY_INCR because of insertions performed by other sessions.
All of the alternatives in 2000 ( IDENT_CURRENT(), SCOPE_IDENTITY() and @@identity) had weaknesses that would allow them to return incorrect values. I usually found SCOPE_IDENTITY() to be the best in my scenarios, but they could all still have an issue, and all could return an "invalid" identity if the insert were to be rolled back.
Any of these methods are ultimately risky if you plan on using them to figure out what the next ID should be.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 25, 2009 at 9:54 am
Matt Miller (8/25/2009)
Any of these methods are ultimately risky if you plan on using them to figure out what the next ID should be.
I doubt he is looking to generate the next ID, but the risk is there, no doubt. I generally use these ID columns to form the basis of parent-child relations and just that. I've found SCOPE_IDENTITY to be the thing to do in those scenarios; and thus my point.
August 25, 2009 at 10:01 am
Even to SQL2008, SCOPE_IDENTITY() etc still have a parallelism bug.
August 25, 2009 at 10:02 am
This would also return the max value, but only testing can tell which is faster for your situation.
Select top 1 IDValue from MyTable order by IDValue desc
As others have pointed out, there is no guarantee that it will stay the max value for long. Can you explain what you are trying to do?
August 25, 2009 at 10:09 am
Ken McKelvey (8/25/2009)
Even to SQL2008, SCOPE_IDENTITY() etc still have a parallelism bug.
...which is why once you get to 2005 or above, the only thing you should be using is the OUTPUT command for these kinds of things. And, never, ever, try to "control" or predict which identites get created.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 25, 2009 at 10:13 am
Guys,
I am not looking for the next identity column, I only need the last one on the table or the MAX one.
create table #test
(
ID int identity(1,1),
code varchar(1)
)
insert into #test (code)
select 'A'
union
select 'B'
union
select 'C'
select max(ID) from #test
Look at the code above, I am looking for an alternative to
select max(ID) from #test
This alternative should always return the value 3 as per the example below, and it has to be as consistent as doing a select max(ID) from #test. I know if I select the max(ID), the table may be open to locking issues, but i still need a consistent/correct value to be returned back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply