[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

T-SQL: Why not builtin function of indexes

Posted on 2008-11-05
10
Medium Priority
?
225 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:karakav
  • 5
  • 3
  • 2
10 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 22884596
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
 
LVL 48

Expert Comment

by:schwertner
ID: 22884599
But why you place the question in Oracle thread?
It is MS MSQ question in his origin!
0
 
LVL 4

Author Comment

by:karakav
ID: 22884638
Sorry, I thought it was a common question to both worlds. I actually am interested in the SQL server oriented opinions.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 22

Expert Comment

by:dportas
ID: 22888114
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
 
LVL 4

Author Comment

by:karakav
ID: 22888563
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
 
LVL 22

Accepted Solution

by:
dportas earned 2000 total points
ID: 22888686
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
 
LVL 4

Author Comment

by:karakav
ID: 22888815
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
 
LVL 22

Assisted Solution

by:dportas
dportas earned 2000 total points
ID: 22889211
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
 
LVL 4

Author Comment

by:karakav
ID: 22893065
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
 
LVL 4

Author Closing Comment

by:karakav
ID: 31513456
Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question