create index to c1, c2 and c3

hi have this dinamic query

depending on the parameters selected the query could be
--query 1
select .. from .. where c1=1

--query 2
select .. from .. where c1=1 and c2='lop'

--query 3
select .. from .. where c1=1 and c3=10

--query 4
select .. from .. where c2='pol' and c3=90

as I create the index? to column c1, c2 and c3
Who is Participating?
Pawan KumarConnect With a Mentor Database ExpertCommented:
Well in this case you will have to create NON Clustered Index on all the three columns as you are searching at least on 2 parameters.

You can also create combination of 2 - 2 Nonclustered indexes but too many indexes will hamper the performance of the system.

Also, When you write this of kind query consider parameter sniffing in mind. Always use below approach.

 SELECT @spexecutesqlStr = @spexecutesqlStr
 + N' AND [C].[CustomerID] = @CustID';
 SELECT @ExecStr = @ExecStr
 + N' AND [C].[CustomerID] = CONVERT(BIGINT, ' + CONVERT(NVARCHAR(30),@CustomerID) + N')';

Enjoy !

Pawan Kumar Khowal
Ryan ChongCommented:
is that your question asking whether or not should we create the indexes for column c1, c2 and c3?

if this is the question, then the answer probably is yes. you can do that.

or if this is not the question you asking for, pls elaborate further for your question.
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
queries 1,2 and 3 may all 3 profit from an index on c1, but it mainly depends on the cardinality of the values of c1 if it's really profitable.

from what you wrote, c1 actually does not "look" like a good candidate, so I would create (at least9 the following indexes:
index1: C3  + C2
index2: C1  + C2
that will support with a minimal number of indexes most cases.

note: the order of the fields in the index is important!
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Since your query is very dynamic I would create 3 indexes, each one for each field.
Index on C1
Index on C2
Index on C3

NOTE: By your sample C1 might be a bit field? If so, then don't create any index for C1 but for C2 and C3 only.
Scott PletcherSenior DBACommented:
In general terms:

What percentage of the time do you query on c1?  If you query on c1 most of the time, then cluster the table first on c1, assuming it is at all reasonably selective.

If you do that, then you can create a nonclustered index on:
(c2, c3) or
(c3, c2)
Index first on whichever one you search on most often.

Of course to be sure about clustering you'd need to look at index stats, including index missing, index usage and index operational stats.
enrique_aeoAuthor Commented:
c1, c2 and c3 are nonclustered index

the user enters the search parameters from webform.
The parameter are: firstname, lastname, nationalidnumber are varchar
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what then is c1, c2 and c3?
enrique_aeoAuthor Commented:
c1= firstname
c2= lastname
c3= nationalidnumber
enrique_aeoAuthor Commented:
All Courses

From novice to tech pro — start learning today.