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?
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.
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.
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 ).
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.
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.
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
)
Is the table clustered on that column? Can you use Top 1:
SELECT TOP 1 [ID]
FROM [Table]
order by ID desc
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With