Non clustered Index Creation

Question

Post reply

Non clustered Index Creation

pramod bura

SSC Rookie

Points: 39
More actions
August 3, 2009 at 6:48 am

#103795

Hi,
I have a table say Request with columns Id(Prim key), BeneficiaryId ,InstructorId,CreatedDate, CreatedBy.
I have created 2 non clustered index on this table, BeneficiaryId and InstructorId.
NOw when I do query like Select columns_list from REQUEST where BeneficiaryId = 'somedata' OR InstructorId= 'somedata'.
When I check the execution plan, It is scan the entire table like "Clustered Index scan."
So, although I have created indexes on BeneficiaryId and InstructorId, i can still see "Clustered Index scan." whereas I was expecting to see "Index seek" OR "Clustered Index seek".
Is this happening because of OR condition in where clause. If yes then could anyone tell me how to replace the OR condition from where clause.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Is this happening because of OR condition in where clause.

Yes

Try

Select columns_list from REQUEST where BeneficiaryId = 'somedata'

UNION

Select columns_list from REQUEST where InstructorId= 'somedata'

pramod bura SSC Rookie Points: 39 More actions · Answer 2

Is there no performance hit because of using UNION?

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

Is there no performance hit because of using UNION?

SQL Server is obviously having to run 2 queries instead of one... but each of these queries should consume less resources than your original query.

There is a slight performance hit because doing a UNION then has to filter out duplicate rows (i.e. those returned from both parts of the UNION)

I assume you didn't want the same row repeated if the BeneficiaryId and InstructorId matched. If you don't mind duplicate rows, then use UNION ALL, and the overhead of filtering out duplicates is removed.

Hopefully the savings when running 2 queries that use index seeks (as opposed to a clustered index scan) should far outweigh the disadvantages of having to filter out duplicate rows.