Thank you guys...

This time I pressurized the client to consider adding unique keys to the tables those don't have one already. And I think they are considering, because they...

Yes I know, but my problem is... we have a whole DB's tables to be migrated and client is reluctant to creating extra identity columns for DB size issues when...

I think, that won't work... As I already said that the table_ name comes from a user input. I am do not know which table it is going to ask...

Gail,

I tried everything, but nothing is faster or slower... everything is taking equal ms.. some times...

What I checked from the execution plan in the DEV environment is that, it is taking a clustered index scan for the NOT IN operation. whose cost is 27%. There...

Thank you Grant for the prompt response.

Regards,

Snigdha

This column has some 350 distinct values among 7000 rows in total. Is it a good candidate for creating a index on it? It also has around 3000 NULL values....

Thanks Gail...

Regards,

Snigdha

Hi everybody,

Thanks for your replies. I will make the scenario little bit clear now....

Thank you Sir!

As you said, that's an old tradeoff :). I will have to monitor the index size...

Thanks a lot guys for your responses!

I tested every option, the third one only gives an index seek for every required search condition. the second one gave me either index...

Try this...

select ILE.[Item No]

Item.[Item Name]

ILE.[Customer No]

Customer.[Customer Name]

ILE.[Location Code]

Location.[Location Name]

ILE.[Country Code]

Country.[Country Name]

ILE.[State Code]

State.[State Name]

Dimension.[dimCategory Code]

Dim Category.[dimCategory Name]

from ILE

left outer join Items on (ILE.[Item No] = Items.[Item No])

left outer join...