There're no composite indexes in SQL Server. You have to include each column into the where clause separatelly.

... WHERE CharacterField1 = CharValue1 
      AND DateTimeField = DateTimeValue
      AND CharacterField2 = CharValue2 

>I have a composite index which consist of character field, DateTime field, character field.
>
>When I try to select record by using CharField + CAST(DateTimeField AS CHAR(8)) + CharField2 expression, it doesn't seem to be optimized.
>