Get max value for identity column without a table scan

Question

I have a table with an Identity column Id.

When I execute:

 select max(Id) from Table

SQL Server does a table scan and stream aggregate.

My question is, why can it not simply look up the last value assigned to Id? It's an identity, so the information must be tracked, right?

Can I look this up manually?

Solution 1

You can use IDENT_CURRENT to look up the last identity value to be inserted, e.g.

IDENT_CURRENT('MyTable')

However, be cautious when using this function. A failed transaction can still increment this value, and, as Quassnoi states, this row might have been deleted.

It's likely that it does a table scan because it can't guarantee that the last identity value is the MAX value. For example the identity might not be a simple incrementing integer. You could be using a decrementing integer as your identity.

Solution 2

What if you have deleted the latest record?

The value of IDENTITY would not correspond to the actual data anymore.

If you want fast lookups for MAX(id), you should create an index on it (or probably declare it a PRIMARY KEY)

Solution 3

Is the table clustered on that column? Can you use Top 1:

SELECT TOP 1 [ID]     
FROM [Table]
order by ID desc

Solution 4

You can run this following statement and remove the last UNION ALL. Run this statement to get the current Identity values.

SELECT 
  ' SELECT '+char(39)+[name]+char(39)+' AS table_name, IDENT_CURRENT('+char(39)+[name]+char(39)+') AS currvalue UNION ALL'
  AS currentIdentity
FROM sys.all_objects WHERE type = 'U'

View more solutions

Comments

[Joe Phillips]

Is the Id also the clustered index?

[Blorgbeard]

No, there's no index on it and it's not the PK

[Blorgbeard]

Would work, but I am not allowed to add an index to this DB just for this

[Cade Roux]

@Blorgbeard Since IDENT_CURRENT() may not reflect the data in your table it cannot be used for MAX() and you have no indexes, so you end up with a table scan.

[Blorgbeard]

It's not clustered on Id. I did try this, but it does a scan/sort which is slower than Max(ID).

[Quassnoi]

@Blorgbeard: sure, I believe you, it's SQL Server that does not.

[Blorgbeard]

Yeah I understand that. Lots of good reasons in this thread that I should forget the whole thing and stick with Max(Id) :)

[dburges]

@Blorgbeard, you do use up an identity every time an insert fails not just when you delete.