Index Usage

I have a table with 1.3 million records and i have a clustered index and a additional index on it. I'm querying on the non-clustered field, but it is not using the index. The same set up on another machine does use the index. I'm pulling out my hair on this, cant get to the bottom of it. From Query analyser i could use the index hint to force the query to use the index. But the original query in a stored procedure which is wrapped around on some logic and it wouldnt be easy to use a hint there. Some more info on my non-clustered index.

Field is varchar
Explain plan tries to use Convert function. I think that is why it is not using the index. But why does it try to convert?

When i try this, it doesnt do the right hand side Convert, ie. doesnt do Convert(@1) from above plan, but does the convert on left side and does the index scan as before. I'm rebuilding the database with different scripts to see any difference. Meanwhile, is there any microsoft support site where i can directly get the help from Microsoft for issues like this? Or, should we be in a product support agreement and all that kind of stuff with microsoft to avail this facility?

i am wondering if DD_DRIVER_NO is an nvarchar, hence the CONVERT is there because '010000011' is varchar,
the following should not have a CONVERT, but i think it should also replace the index scan with a seek.
SELECT * FROM DRIVER_DETAILS WHERE DD_DRIVER_NO = N'010000011'

Finally we are there. The culprit was the database character set. There was a mix up between SQL_Latin_General_CP1_CI_AS and Latin_General_CP1_CI_AS. The table was created with one character set, index was using the default character set which is the other, so it had to convert when a query is raised and the index was therefore skipped. Not sure how the mix up happened. Suppose SQL_Latin.. should be the default collation?

Ah, the dreaded default collation! I've been hoping for some time now that the behaviour of SQL Server regarding assumed collations will be changed from server-based to database-based, but I guess that is just not going to happen.

By the way, the SQL_Latin_General_CP1_CI_AS is the collation that SQL installs itself with when the Windows locale of the server is US English. If the locale is different, you get Latin_General_CP1_CI_AS. (This information is buried somewhere in BOL - can't seem to find it today.)