>How can I create an index which would optimize this query:
>
>

>SELECT CONVERT(CHAR(4),YEAR(Hit.AddDate))+REPLACE(STR(MONTH(Hit.AddDate),2),' ','0')+
> REPLACE(STR(DAY(Hit.AddDate),2),' ','0') AS lcChampNumero,
> COUNT(*) AS Compte,MIN(Hit.AddDate) AS AddDate
> FROM Hit
> WHERE Hit.NoServer=3
> GROUP BY CONVERT(CHAR(4),YEAR(Hit.AddDate))+REPLACE(STR(MONTH(Hit.AddDate),2),' ','0')+
> REPLACE(STR(DAY(Hit.AddDate),2),' ','0')
> ORDER BY 1 DESC 
>

>
>I added an index NoServerAddDate which includes the NoServer and AddDate fields. I see that this has changed the execution plan. It does show that it uses it. However, my query is not any faster.
>
>Is there a way to make it faster?
>
>This is one on a Hit table having about two million records and all I need is a repartition on a daily basis with the count. The result returns presently 28 records.

In SQL Server just use CONVERT(char(6), Hit.AddDate, 112) instead of your complex expression. Index on AddDate should speed this query a bit.
Also, how selective the NoServer column is (e.g. how many values it may have)?