Mapping to varchar and nvarchar in hibernate
Probably you already solved this, but I had a similar problem.
I'm using jTDS JDBC driver and I solved the index scan problem by adding:
;sendStringParametersAsUnicode=false;prepareSQL=0to the end of the jTDS connection string.
Probably it would not had solved your problem because by doing this, jTDS will only use VARCHAR (no NVARCHAR anymore).
Also, I had to disable the prepared SQL, because Hibernate is using 'like' instead of '=' when generating the queries and by using 'like' combined with a variable (SELECT ... WHERE column LIKE @var) causes an index scan (MSSQL 2000).
I'm assuming you're talking about NHibernate rather than Hibernate because the latter does not use nvarchar in its default SqlServer dialect.
The way to solve your problem is to specify column type as "AnsiString" in your mapping:
<property name="Code" type="AnsiString"/>Take a look at this post for more details.