Hi Amir,
1) If what you need to know is the max value on disk right now, then you
will need to issue a "select max(...)" statement.
2) Maybe, however, all you need to know is the max value that was ever
on disk. Of course, this can be a different number than (1) because you
may have deleted rows at the upper end.
If all you need to know is (2), then you can get the answer from the
system catalogs. Here's a little script which creates a table with an
identity column, inserts and deletes some rows, and then selects the max
value that was ever in the identity column. The last query gives you the
answer to (2):
drop table s;
create table s
(
a int generated always as identity (start with 3, increment by 3),
b int
);
insert into s( b ) values ( 1 ), ( 2 ), ( 3 ), ( 4 );
delete from s where b = 4;
insert into s( b ) values ( 11 ), ( 12 ), ( 13 ), ( 14 );
select * from s order by b;
-- now find the max value that was ever put in the identity column
select c.autoincrementvalue - c.autoincrementinc
from sys.syscolumns c, sys.systables t
where t.tablename = 'S'
and t.tableid = c.referenceid
and c.columnname = 'A';
For more information, please see the Reference Guide section describing
the SYSCOLUMNS system catalog.
Hope this helps,
-Rick
Amir Michail wrote:
Hi,
I was wondering what is the most efficient way to get the max
automatically generated row id.
select max(...) is slow.
Amir
