Last id value in a table. SQL Server

Question

Is there a way to know the last nth id field of a table, without scanning it completely? (just go to the end of table and get id value)

table
id   fieldvalue
1    2323
2    4645
3    556
...  ...
100000000  1232

So for example here n = 100000000 100 Million

--------------EDIT----- So which one of the queries proposed would be more efficient?

if all you want is the id number and not the whole record, mine is the quickest - which is why i posted it as it answered the question as posed. Now if you want the WHOLE record, it would be what michael suggested. And on a sidenote: if it is the id and primary key, it should already be indexed by default (I believe) — rayman86, Feb 25 '11 at 04:26
Tough to say, an answer on this post seems to indicate that IDENT_CURRENT is the fastest, but I'm having a hard time confirming that anywhere else: http://stackoverflow.com/questions/590079/for-autoincrement-fields-maxid-vs-top-1-id-order-by-id-desc. IDENT_CURRENT works over any session and any scope, so it may be preferable if there is alot of activity on this table. — rsbarro, Feb 25 '11 at 04:29
Just for fun, I ran both methods against a test DB I have while running SQL Profiler. Both are really fast, but IDENT_CURRENT is showing 0's for CPU/Reads/Duration while MAX(ID) does cause a slight number of reads. I think IDENT_CURRENT is faster, but chances are either will work fine as far as performance is concerned. — rsbarro, Feb 25 '11 at 04:39

score 24 · Accepted Answer · answered Feb 25 '11 at 04:02

24

SELECT MAX(id) FROM <tablename>

answered Feb 25 '11 at 04:02

rayman86

1,385
10
9

Yeah sometimes the answer really is as simple as you think.. other times it can be so obscure though! – rayman86 Feb 25 '11 at 04:04
Yes, thaks for the immediate answer – edgarmtze Feb 25 '11 at 04:05
1

Bear in mind that this makes the assumption that the `id` value is a monotonically increasing value (the OP does not say anything about this being an `identity` column, though by all appearances it is). – Adam Robinson Feb 25 '11 at 16:25

score 5 · Answer 2 · edited Sep 25 '17 at 19:43

5

Assuming ID is the IDENTITY for the table, you could use SELECT IDENT_CURRENT('TABLE NAME').

See here for more info.

One thing to note about this approach: If you have INSERTs that fail but increment the IDENTITY counter, then you will get back a result that is higher than the result returned by SELECT MAX(id) FROM <tablename>

edited Sep 25 '17 at 19:43

Regular Jo

5,190
3
25
47

answered Feb 25 '11 at 04:07

rsbarro

27,021
9
71
75

1

Actually, it would have to be an `INT IDENTITY` field - not a primary key (that's irrelevant) – marc_s Feb 25 '11 at 05:48
2

One thing to note about this approach, if you have INSERTs that fail but increment the IDENTITY counter, then you will get back a result that is higher than the result returned by SELECT MAX(id) FROM . – rsbarro Apr 06 '11 at 15:09

score 1 · Answer 3 · answered Oct 19 '15 at 12:58

1

You can also use system tables to get all last values from all identity columns in system:

select
    OBJECT_NAME(object_id) + '.' + name as col_name
    , last_value 
from 
    sys.identity_columns 
order by last_value desc

answered Oct 19 '15 at 12:58

kosmo

101
2
9

score 1 · Answer 4 · answered Sep 21 '17 at 14:20

In case when table1 rows are inserted first, and then rows to table2 which depend on ids from the table1, you can use SELECT:

INSERT INTO `table2` (`some_id`, `some_value`)
VALUES ((SELECT some_id
        FROM `table1`
        WHERE `other_key_1` = 'xxx'
            AND `other_key_2` = 'yyy'),
        'some value abc abc 123 123 ...');

Of course, this can work only if there are other identifiers that can uniquely identify rows from table1

score 0 · Answer 5 · answered Feb 25 '11 at 04:20

First of all, you want to access the table in DESCENDING order by ID.

Then you would select the TOP N records.

At this point, you want the last record of the set which hopefully is obvious. Assuming that the id field is indexed, this would at most retrieve the last N records of the table and most likely would end up being optimized into a single record fetch.

score 0 · Answer 6 · answered Aug 22 '18 at 19:02

0

Select Ident_Current('Your Table Name') gives the last Id of your table.

answered Aug 22 '18 at 19:02

Md Shahriar

2,072
22
11

Last id value in a table. SQL Server

6 Answers6