Which is faster: MAX() or IDENT_CURRENT?

Question

Which is faster: Select Max(<IdentityKeyColumn>) + 1 OR Select Ident_Current('<tablename>') +1?

I have a table with 4.5 million rows and an old stored procedure uses: select @MaxID = Max(ID) + 1 from tablename.

I want to update it to use: Select @MaxID = ident_current('tablename') + 1

however, I'm not sure where ident_current gets the table's last identity value from. Is it queried from a system table in the master or msdb databases or does it do some sort of max aggregation on the table's data?

I need to make a case that ident_current should be faster but I have not found details on how ident_current works.

Instead of guessing or asking, why don't you just test both options and see which is faster? Although if you're changing the code anyway you may want to consider rethinking it completely, and using `SCOPE_IDENTITY()` instead. But you haven't said anything about why you (apparently) want to get the identity value before inserting instead of afterwards. — Pondlife, Apr 16 '13 at 16:45
What are you using your `@MaxID` value for? It's possible that you could get subtly different results from `MAX` and `IDENT_CURRENT` against the same table. Without knowing what you're trying to do it's difficult to say which, if any, is appropriate. (I appreciate that you're asking which is *faster*, but it's important to consider *correctness* first.) — LukeH, Apr 16 '13 at 16:45
It's faster to just let SQL Server generate the identity values and capture them with `SCOPE_IDENTITY` or an Output clause. A lot more reliable too. — RBarryYoung, Apr 16 '13 at 16:51
you should start studying "How to use Identity Columns" instead of fetching next id. — Allan Chua, Apr 16 '13 at 16:51
Instead of asking us "what is the fastest way to accomplish x if I use one of these two methods?" why don't you ask us "what is the fastest way to accomplish x?" Unfortunately you haven't defined x so you need to elaborate in your question. Whatever x is, neither `MAX()` not `IDENT_CURRENT()` is the solution, so which of them is faster is largely irrelevant. You should focus on *correct* first, and then worry about performance *if you observe a performance problem.* — Aaron Bertrand, Apr 16 '13 at 16:54
I know what Ident_Current() does. I just don't know how it does it. Scope_Identity will not work in my scenario b/c the sp has not inserted any recs into the table in question. I use the @MaxID value to identify a batch of inserts that are performed later in the stored proc. — bsivel, Apr 16 '13 at 16:55
Aaron, Rbarry, Pondlife, Luke, - Can you answer the question of "HOW does IDENT_CURRENT get the last identity value"? — bsivel, Apr 16 '13 at 17:00
So perform the insert before you proceed. If you take the max or ident_current and then someone else in some other session inserts a row, your max is no longer correct. Why do you need to know what number you will insert before you insert it? Please reconsider your logic, because currently it is flawed. — Aaron Bertrand, Apr 16 '13 at 17:03
Aaron - you make a lot of assumptions before you write your comments. Please refrain from making additional comments unless you can answer the question of HOW ident_current works. — bsivel, Apr 16 '13 at 17:07
IDENT_CURRENT does not take an aggregate over the table. It uses internal (system) tables to determine what the current seed is. It wouldn't make sense to take an aggregate over the table because when the table is empty it still returns a valid value (MAX will not). — Aaron Bertrand, Apr 16 '13 at 17:12
Aaron - thanks for the info - do you know what system table(s) hold this information and how can I query them manually to make my case? — bsivel, Apr 16 '13 at 17:17
@user1130761 for what it's worth, Aaron is a well-known SQL Server MVP and RBarryYoung is a well known SQL Server expert and consultant. You're getting their help at a discount of hundreds of dollars an hour, you probably should be nice to them ;) — JNK, Apr 16 '13 at 17:22

score 10 · Accepted Answer · edited Jun 20 '20 at 09:12

The actual method that IDENT_CURRENT() uses to retrieve the current identity value is not documented as far as I'm aware. It's possible it retrieves this directly from memory using internal functions you aren't allowed to run. You can imagine what it probably does and simulate that to some degree, e.g.

SELECT COALESCE(last_value, seed_value)
  FROM sys.identity_columns
  WHERE [object_id] = OBJECT_ID('dbo.tablename'));

You can compare the performance yourself, on your system, with code like this:

SELECT SYSDATETIME();
GO
DECLARE @i INT = IDENT_CURRENT('dbo.tablename');
GO 10000
SELECT SYSDATETIME();
GO
DECLARE @i INT = (SELECT MAX(column) FROM dbo.tablename);
GO 10000
SELECT SYSDATETIME();
GO
DECLARE @i SQL_VARIANT = (SELECT COALESCE(last_value,seed_value)
  FROM sys.identity_columns
  WHERE [object_id] = OBJECT_ID('dbo.tablename'));
GO 10000
SELECT SYSDATETIME();

(Note that seed_value and last_value are of type SQL_VARIANT, since IDENTITY columns can be implemented using a number of numeric types, so that is why @i is not an INT in that case.)

My guess is you aren't going to see any negligible differences there, except that the MAX approach should take longer as the table gets bigger (which is why I'm not going to bother posting my results from this test - I have no idea how many rows are in your table, how wide is the index that will be used for the MAX, etc). You're going to have to determine on your own which of these approaches is faster in your scenario, using your hardware and your data, or whether it really isn't going to matter.

HOWEVER...

...as I've stated in the comments, which of these is faster is irrelevant, because the logic you're using in the first place to decide between any of them is flawed. Insert the row, grab the value using SCOPE_IDENTITY() or the OUTPUT clause, and then move on. Don't try to game the system and predict what IDENTITY value will eventually be inserted, because I guarantee you this will fail. This is not an assumption - it has been proven over, and over, and over, and over...

For example:

For the last time, NO, you can't trust IDENT_CURRENT().

Thanks for taking the time to help - it is appreciated. I'll work on phrasing future questions better. I marked the question as answered. — bsivel, Apr 16 '13 at 18:03

Which is faster: MAX() or IDENT_CURRENT?

1 Answers1

HOWEVER...

Linked

Related