Function Based Indexes In Sql Server

Function Based indexes in SQL Server All 6 messages in topic - view as tree Neil Phillips

Jan 6 2003, 7:25 pm Newsgroups: comp.databases.ms-sqlserver From: [email protected] (Neil Phillips) - Find messages by this author Date: 6 Jan 2003 06:25:47 -0800 Local: Mon, Jan 6 2003 7:25 pm Subject: Function Based indexes in SQL Server Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse

Hi Does anyone know if the use of a function on an indexed column invalidates that index and forces the optimiser to do a full table scan instead? As an example, I have a table with a column in it called which has a normal, non-unique/non-clustered index on it. When I execute the query SELECT * FROM TABLE A WHERE COL_1 LIKE 'H%' SQL Server does an index scan. But if I execute SELECT * FROM TABLE A WHERE COL_1 LIKE rtrim('H%') it does a full table scan. (This was executed on SQL Server 7.0). In Oracle, you must create a function based index on COL_1 if the index is to be used when it is queried using a function such as trim. I understand that the option to create function-based indexes doesnt exist in sql server 7. Does it exist in 2000? If not, is there another way to get the optimiser to use the index when a function such as rtrim is used against it as part of a query? (or is creating a psuedo-column with data already passed through the function and querying that column instead the only option?) Many thanks Neil

Erland Sommarskog Jan 6 2003, 8:41 pm Newsgroups: comp.databases.ms-sqlserver From: Erland Sommarskog <[email protected]> - Find messages by this author Date: Mon, 6 Jan 2003 15:42:50 +0000 (UTC) Local: Mon, Jan 6 2003 8:42 pm Subject: Re: Function Based indexes in SQL Server Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse

Neil Phillips ([email protected]) writes: > Does anyone know if the use of a function on an indexed column > invalidates that index and forces the optimiser to do a full table > scan instead? > As an example, I have a table with a column in it called > which has a normal, non-unique/non-clustered index on it. When I > execute the query SELECT * FROM TABLE A WHERE COL_1 LIKE 'H%' SQL > Server does an index scan. But if I execute SELECT * FROM TABLE A > WHERE COL_1 LIKE rtrim('H%') it does a full table scan. (This was > executed on SQL Server 7.0).

The key issue is here: when is a non-clustered index useful and when is it not? Answer: a non-clustered index is useful if you can expect to hit less than a certain percentage of the rows. To take an example

in the extreme end: if you are to read all rows in the table, you don't want to this through a non-clustered index, because you will get at least as twice as many page reads as there are rows in the table, while a table scane gives you as many pages reads as there are pages in the table. The exact number where the optimizer decides to use table scan is unknown to me, but it is rarely above 10%. The optmizer is fairly conservative, and sometimes picks table scan, when you would expect it not to. For the expression "col_1 LIKE 'H%'" SQL Server should not perform an Index Scan, but an Index Seek. Or a Table Scan. Which it chooses depends on the information in the statistics for the table. If the statistics say that not too many entries start in H it will use the index, else not. When you say "col_1 LIKE rtrim('H%')"... Let's see, I don't think there should be any fundamental difference. I tried this on a table I and I found that I got a Clustred Index Scan (= Table Scan) when I used a column with a nonclustered index, but when instead used a column with a with a clustered index I did get a Clustered Index Seek. And "col_1 LIKE 'H' + '%'" worked with the non-clustered index too. So I don't think the function as such invalidates the index. But it may be a design decision in the optimizer not to evaluate it for non-clustered indexes. On the other, if you apply the function on the column itself, or involve it some other expression, yes, then you can kiss the index bye-bye. > In Oracle, you must create a function based index on COL_1 if the > index is to be used when it is queried using a function such as trim. > I understand that the option to create function-based indexes doesnt > exist in sql server 7. Does it exist in 2000? If not, is there another > way to get the optimiser to use the index when a function such as > rtrim is used against it as part of a query? In SQL2000 you can come around the problem with the column being involved in an expression, by adding a computed column to the table, and then index that column. It may be better to post your actual problem, so that people can find other possible angles. -Erland Sommarskog, SQL Server MVP, [email protected]

BP Margolin Jan 7 2003, 12:16 am Newsgroups: comp.databases.ms-sqlserver From: "BP Margolin" - Find messages by this author Date: Mon, 6 Jan 2003 14:18:51 -0500 Local: Tues, Jan 7 2003 12:18 am Subject: Re: Function Based indexes in SQL Server Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse

Erland,

> For the expression "col_1 LIKE 'H%'" SQL Server should not perform an > Index Scan, but an Index Seek. An Index Scan is not **necessarily** equivalent to a table scan. Check out the section "Index Scan" (optimsql.chm::/odp_tun_1_35im.htm). BPM "Erland Sommarskog" <[email protected]> wrote in message

news:[email protected]...

- Show quoted text -

BP Margolin Jan 7 2003, 12:25 am Newsgroups: comp.databases.ms-sqlserver From: "BP Margolin" - Find messages by this author Date: Mon, 6 Jan 2003 14:27:32 -0500 Local: Tues, Jan 7 2003 12:27 am Subject: Re: Function Based indexes in SQL Server Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse

Neil, > Does anyone know if the use of a function on an indexed column > invalidates that index and forces the optimiser to do a full table > scan instead? In most cases, Yes. > If not, is there another > way to get the optimiser to use the index when a function such as > rtrim is used against it as part of a query? Try creating a computed column equal to rtrim (COL_1) and create an index on the computed column. Then query by the computed column. (Note that creating an index on a computed column requires SQL Server 2000). For example: use pubs go create table x_authors ( au_id id NOT NULL, au_lname varchar (40) NOT NULL, au_fname varchar (20) NOT NULL, x_lname as rtrim (au_lname) ) go insert into x_authors select au_id, au_lname, au_fname

from authors go create index i_x_lname on x_authors (x_lname) go declare @x_lname varchar (40) set @x_lname = rtrim ('Ringer ') select * from x_authors where x_lname = @x_lname go -- clean up drop table x_authors ------------------------------------------BP Margolin Please reply only to the newsgroups. When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated. "Neil Phillips" wrote in message

news:[email protected]...

- Show quoted text -

Erland Sommarskog

Jan 7 2003, 3:16 am Newsgroups: comp.databases.ms-sqlserver From: Erland Sommarskog <[email protected]> - Find messages by this author Date: Mon, 6 Jan 2003 22:17:09 +0000 (UTC) Local: Tues, Jan 7 2003 3:17 am Subject: Re: Function Based indexes in SQL Server Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse

BP Margolin ([email protected]) writes: >> For the expression "col_1 LIKE 'H%'" SQL Server should not perform an >> Index Scan, but an Index Seek. > An Index Scan is not **necessarily** equivalent to a table scan. Check out > the section "Index Scan" (optimsql.chm::/odp_tun_1_35im.htm). An Index Scan is only the same as a Table Scan, if the index covers the entire table. Neil's posting made a clear distinction between Index Scan and Table Scan, but he might have appeared to muddled Index Seek (= search a value in an index, by using the index keys) with Index Scan (= search a value in an index by searching the entire index from left to right). A query for which SQL Server would use Index Scan is:

SELECT col_1 LIKE '%H' -Erland Sommarskog, SQL Server MVP, [email protected]

Doug Miller

Jan 9 2003, 4:16 am Newsgroups: comp.databases.ms-sqlserver From: [email protected] (Doug Miller) - Find messages by this author Date: 8 Jan 2003 15:16:52 -0800 Local: Thurs, Jan 9 2003 4:16 am Subject: Re: Function Based indexes in SQL Server Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse

[email protected] (Neil Phillips) wrote in message ... > Hi > Does anyone know if the use of a function on an indexed column > invalidates that index and forces the optimiser to do a full table > scan instead? > As an example, I have a table with a column in it called > which has a normal, non-unique/non-clustered index on it. When I > execute the query SELECT * FROM TABLE A WHERE COL_1 LIKE 'H%' SQL > Server does an index scan. But if I execute SELECT * FROM TABLE A > WHERE COL_1 LIKE rtrim('H%') it does a full table scan. (This was > executed on SQL Server 7.0). So if you look closer at your question and your example, they don't match. You ddin't do a function against the indexed column. You performed a function against a static variable, and then looked for that in a column. Compilers are funny critters. The poor compiler is trying to take goofy human notions about things work, and one, figure out what they really meant, and two how to do it fastest. You provide a straightforward static variable, and it provides a straight forward index lookup. You provide a goofy functional expression that might possibly change for all it knows for every value in the table, and it will give up and do a scan looking for "true" expressions. For instance, lets say your example instead said: WHERE COL1 = substr(COL_1,1,1) Now what should the compiler do? "Common sense" (which isn't that common) would say to interpret it instead to: WHERE len(col1) = 1, but even that isn't maybe true always (like if nulls are involved). Anyway, the interpreter/compiler thing is really a black box to folks outside of the microsoft internals. There are two tricks to figuring out how to make things work quicker. The first is to realize that slightly different syntax can result in VERY different processing speeds. The second is learning and remembering tidbits that help the compiler be more efficient. -doug miller