RE: View index not being used

SSC Guru

Points: 323504

August 1, 2013 at 11:50 am

andybellenie (8/1/2013)
CREATE UNIQUE CLUSTERED INDEX [IX_campaignContacts] ON [dbo].[campaignContacts]
(
campaignId ASC,
contactId ASC
)

The index you created above would be used if you had a statement like this:

SELECT contactId

FROM campaignContacts

WHERE campaignId = @p1

AND contactId = @p2

so the index specifically supports WHERE statements..

if you don't have a WHERE statement, the optimizer would ignore that index, and use something different.

does that help?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!