Isn't this:

CONVERT(char(8), Hit.AddDate, 112)

the same as your expression

CONVERT(CHAR(4),YEAR(Hit.AddDate))+REPLACE(STR(MONTH(Hit.AddDate),2),' ','0') + REPLACE(STR(DAY(Hit.AddDate),2),' ','0')

?


>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.