or tbl.Field in (N''0100'',N''1002'',N''1201'',N''1202''....This snippet came right out of profiler with no editing...
The N in front of each string forces SQL Server to treat it as unicode, as an NVARCHAR string. The issue with this is that my tbl.Field column is a varchar. So no indexes would ever be used for this query, it was always a clustered index scan.
My workaround was to add an nvarchar column, using a trigger to popuate it with the nvarchar version of the varchar field. Messy, but once I did that, and indexed it, the query performed fine.
I have opened up a Connect suggestion, so please visit that and vote for it. This situation should not exist.