T-SQL: Why not builtin function of indexes

I heard it is not a good idea to use queries that perform calculations on index colulmns. Can anyone explain me why? I am asking because in many of my table I put the primary column as an autoincrement int so that when I need to lookup the last record it is easy. Is that a good practice or I need to change things.
LVL 4
karakavAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
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;

0
schwertnerCommented:
But why you place the question in Oracle thread?
It is MS MSQ question in his origin!
0
karakavAuthor Commented:
Sorry, I thought it was a common question to both worlds. I actually am interested in the SQL server oriented opinions.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dportasCommented:
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();
0
karakavAuthor Commented:
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.
0
dportasCommented:
I'm guessing you were using an expression based on a date column. For example, here are two versions of a query:

SELECT col1, col2, col3
FROM tbl
WHERE DATEADD(DAY,1,dt) <= CURRENT_TIMESTAMP;

SELECT col1, col2, col3
FROM tbl
WHERE dt <= DATEADD(DAY,-1,CURRENT_TIMESTAMP);

The second example is potentially better because the DATEADD expression only has to be computed once instead of once for each row. For that reason the second query can take best advantage of an index by seeking to the right row(s) within that index because the values to be found are known at the start of the query.

The WHERE clause expression in the second example has a name. It's called a "sargable" expression and the query is called a sargable query. "Sarg" is short for "search argument". In most cases SQL Server isn't smart enough to transform an expression as in the first query into its sargable equivalent (assuming there is such an equivalent, which there isn't always). That's why sargable expressions are preferred rather than calculations based on columns.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
karakavAuthor Commented:
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.
0
dportasCommented:
Your query has no WHERE clause so it will perform a table or index scan anyway. It's unlikely to gain much benefit from an index unless the index happens to cover the query (all columns included in the same index).

Beware of using MAX() of an IDENTITY column to determine a "latest" row. IDENTITY values get assigned some time before a transaction is committed but you can't always guarantee when they get assigned. In a multi-user system IDENTITY values can even get interleaved between two entirely different transactions. IDENTITY is not serialisable and even if there are no open transactions it is quite possible that the latest inserted row (based on a datetime column for example) will NOT have the highest IDENTITY value.

0
karakavAuthor Commented:
So to abuse, but I would like to know in which conditions there is a table or index scan instead of a index seek?
0
karakavAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.