Geek Answers Handbook

Get maximum value for identity column without table scan

I have a table with an Identity Id column.

When I execute:

  select max(Id) from Table

SQL Server performs table scans and thread aggregation.

My question is, why can't he just look for the last value assigned by Id? This is a identity , so you need to track information, right?

Can I view it manually?

+6
sql sql-server sql-server-2008
source share
6 answers

You can use IDENT_CURRENT to find the last authentication value to be inserted, e.g.

 IDENT_CURRENT('MyTable')

However, be careful when using this feature. A transaction failure can still increase this value and, as indicated by Quassnoi , this line could have been deleted.

It probably scans the table because it cannot guarantee that the last authentication value is a MAX value. For example, an identifier may not be a simple incremental integer. You can use the decrementing whole as your personality.

+16
source share

What if you deleted the last entry?

The IDENTITY value will no longer match the actual data.

If you need a quick search for MAX(id) , you should create an index on it (or perhaps declare it PRIMARY KEY )

+5
source share

Is the table grouped in this column? Can you use Top 1:

 SELECT TOP 1 [ID] FROM [Table] order by ID desc
+3
source share

I am sure that you can configure the index in this field in descending order, and it will use it to find the largest key. He must be fast.

+1
source share

You can run this next statement and delete 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'
+1
source share

Is the identifier a primary key or is it indexed? It seems that in these cases he should be looking.

0
source share

More articles:


All Articles