Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get max value for identity column without a table scan

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?

like image 494
Blorgbeard Avatar asked Mar 31 '11 14:03

Blorgbeard


People also ask

Can we specify maximum value for a identity property?

The maximum value will be the largest value that a type can support. Suppose you are using an Int type as IDENTITY, in 32 bit system the max number will be 2^32-1. When this number is reached, the next attempt to insert a value will result in a overflow error and will fail.

How can I get max value in SQL without using max function?

You can do that as: select MIN(-1 * col)*-1 as col from tableName; Alternatively you can use the LIMIT clause if your database supports it.

How do I get maximum ID records in SQL?

To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).

How do I get the max value in a column in SQL?

To find the max value of a column, use the MAX() aggregate function; it takes as its argument the name of the column for which you want to find the maximum value. If you have not specified any other columns in the SELECT clause, the maximum will be calculated for all records in the table.


3 Answers

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.

like image 163
RB. Avatar answered Oct 23 '22 15:10

RB.


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)

like image 20
Quassnoi Avatar answered Oct 23 '22 14:10

Quassnoi


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

SELECT TOP 1 [ID]     
FROM [Table]
order by ID desc
like image 2
Tomas Avatar answered Oct 23 '22 16:10

Tomas