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 |
|
midavis
Starting Member
23 Posts |
 Posted - 2010-07-14 : 09:27:19
|
| I have two tables, Table1 and Table2. The primary key for Table1 is a varchar that holds values like '01001' and '13001'. Table2 has a foreign key relationship to Table1 on this varchar column. Table2 has a clustered index on the primary key and a non-clustered index on Table1's primary key. For some reason when I do a select statement from Table2 using Table1's primary key I get wierd results. If I do (assuming col1 is the varchar primary key from Table1)select * from Table2 where col1 = '12345'The execution plan uses the clustered index from Table2 and says I a missing an index on col1. The index does exist though. If I run the following query below I get different results.Select * from Table2 where col1 = 12345The execution plan uses the non-clustered index on Table2. I can't really use the second one because I need to use values like '01001' and '03005' and taking off the quotes actually tripled my reads. I have a similar index on Table3 that does use the non-clustered index in the execution plan. I have no idea why it works in one table but not the other. Are there any reason why the execution plan says I am missing an index when it actually exists? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-07-14 : 09:58:27
|
| can you compare the SQL columns definitinos for each table? joins will (generally) only consider using indices if both sides of a join ahve the same column definitions. |
 |
|
|
midavis
Starting Member
23 Posts |
Posted - 2010-07-14 : 10:59:09
|
| The definitions are the same. I can understand if the execution plan chooses a different index for some reason, but why does it say I don't even have one on the table when it exists? |
|
|
|
|
|
|
Query Execution showing missing index.