asked on
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
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
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
enrique_aeo
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
ASKER
c1, c2 and c3 are nonclustered index
the user enters the search parameters from webform.
The parameter are: firstname, lastname, nationalidnumber are varchar
the user enters the search parameters from webform.
The parameter are: firstname, lastname, nationalidnumber are varchar
what then is c1, c2 and c3?
ASKER
c1= firstname
c2= lastname
c3= nationalidnumber
c2= lastname
c3= nationalidnumber
ASKER
ok
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.