here is the table structure
orgid is the identity column and primarykey, and the exptnumber has the UNIQUE INDEX. Can I modify this query to make it use the unique index ??? without making much changes to the structure.
It is possible to force the query to use the index, but why would you want to do that?
If that field (exptnumber) is not used to join the tables or referenced in the where-clause of the query then it wouldn't make sense to try and use that index....and that is probably what the query engine has decided in this case anyways.
What is your reason for trying to force the usage of that index?
answered Jan 04, 2012 at 02:12 PM
Both tables have index on column “OrgID” because of primary key and in your select query you are joining the tables from column “OrgID” so already you are getting Index benefit in your query.
As per your current query you do not need to enforce select query to use Unique Index because it is not required.
answered Jan 04, 2012 at 08:50 PM
The only way it will use that index is if you put a WHERE clause in that referenced the exptnumber column, or if you referenced that column as part of the JOIN (but you shouldn't do that since it isn't part of the JOIN).
Also, in general, I'd really get out of the habit of using ANSI 89 syntax on the JOINs. If you have to do an outer join in 2008R2 or greater, it won't work at all. Best to just use the new syntax everywhere:
answered Jan 05, 2012 at 03:31 AM
Grant Fritchey ♦♦