Using a index in a query


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.

more ▼

asked Jan 04, 2012 at 12:08 PM in Default

avatar image

Katie 1
1.4k 132 164 205

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?

Jan 04, 2012 at 09:00 PM Sharma
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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?

more ▼

answered Jan 04, 2012 at 02:12 PM

avatar image

Martin Schoombee
611 4 4

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 04, 2012 at 08:50 PM

avatar image

1.4k 90 93 96

(comments are locked)
10|1200 characters needed characters left

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, 
 FROM a 
 JOIN b 
 ON a.orgid = b.orgID;
 WHERE ...
more ▼

answered Jan 05, 2012 at 03:31 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 04, 2012 at 12:08 PM

Seen: 767 times

Last Updated: Jan 04, 2012 at 01:03 PM

Copyright 2018 Redgate Software. Privacy Policy