Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spock
Starting Member
35 Posts |
 Posted - 2003-10-27 : 22:41:20
|
| Suppose i have two tables like with the indexes createdas followscreate table #temp_table1(ename varchar(30), empid int)create table #temp_table2(ename varchar(30), empid int)CREATE INDEX tmp_indx ON #temp_table1(empid)CREATE INDEX tmp2_indx ON #temp_table2(empid)If i run a join query which has some function(like coalesce or select case) on the joining column, will the indexes be used ? I was not able to figure out from the query analyser if the indexes were getting used in such a case.e.g queryselect * from #temp_table1 a inner join #temp_table2 b on coalesce(a.name,cast(a.empid as varchar(10))) = coalesce(b.ename,cast(b.empid as varchar(10))will the indexes created above really get used during execution of the above query?Thanks in advanceKaushik |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-27 : 22:56:26
|
| You can display the execution plan in Query Analyzer. Hovering the mouse over the various icons and lines will display the information about them. You'll be able to glean from this whether indexes were used or not.Generally speaking, indexes will be used to process joins and WHERE clause conditions on the indexed column(s), EXCEPT if the indexed columns are not particularly selective (meaning that most of the values are the same, or have a low number of distinct values) In that case the optimizer will determine that it is easier to scan the entire table instead of looking up rows in the index and then performing lookups.Books Online has a great entry on this under the heading "Statistical Information". It presents everything I've said here in more detail. |
 |
|
|
|
|
|
Question on Join and indexes