T-SQL: Why not builtin function of indexes

Index columns or indexED columns.
Index columns are used as reference to the row and there is no need to calculate
something using the values there.
In multuuser environment it is a bad idea to figure out the next key value using query like this:

select max(my_index) + 1 into v_next_id
from my_table;

Because simultenously other user can use the same algorithm and the first call
will struck an occupied value ...

So Oracle uses either natural keys or surrogate keys via sequence generator (Oracle Sequences).
Using Oracle sequences you can easily get the next value.

select sequence_name.nextval into v_next_id from dual;

If you need the last occupied value use:

select sequence_name.currval into v_curr_id from dual;

But why you place the question in Oracle thread?
It is MS MSQ question in his origin!

Sorry, I thought it was a common question to both worlds. I actually am interested in the SQL server oriented opinions.

In SQL Server you don't need to perform a calculation to look up the last inserted value for an IDENTITY ("auto increment") column. You use SCOPE_IDENTITY or IDENT_CURRENT for that. Example:

SELECT col1, col2, col3
FROM tbl
WHERE id = SCOPE_IDENTITY();

Maybe I didn't ask well my question. My intention is to retreive the last record based on some criteria, NOT the last inserted record. Up to now I was checking the date if such column is defined and compulsory but I realized that there could be more than one value on the same date.

Actually what I was using was this:
SELECT MAX(datecolum) from table
GROUP BY foreignKeyColumn

And now I want to use SELECT MAX(IdColumn) GROUP BY foreignKeyColumn

Because this is the only way to pick the most recent value as each a single foreignKeyValue can have more than one value in the table. That's why my concern about performance because I heard that it is not a good practice to perform calculations on indexed columns.

So to abuse, but I would like to know in which conditions there is a table or index scan instead of a index seek?

Thanks