question

Katie 1 avatar image
Katie 1 asked

Using a index in a query

All, here is the table structure organisation (orgid, specialID, name adddress etc) a customers(orgid, exptnumber, exptename) b organisation.orgid = customers.orgid All the queries are written in this format of select a.column name, b.columnname etc FROM a, b where a.orgid = b.orgID 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.
sql-server-2008query
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image Sharma commented ·
You written in your question that Orgid is Identity column and Primary Key, Is it in both table or any one? If it is identity column in both table then how are you using it as reference key of other table?
0 Likes 0 ·
Martin Schoombee avatar image
Martin Schoombee answered
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?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image
Sharma answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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: SELECT a.column name, b.columnname, etc FROM a JOIN b ON a.orgid = b.orgID; WHERE ...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.