If you use VARCHARs for database tables and index those columns either (as a primary key or not) , then you will find that without this option
sendStringParametersAsUnicode=false
set on the JDBC driver string, all queries will use a table scan rather than the index to access rows. This is somewhat annoying with large tables (as you can imagine). The reason is that the JDBC driver marks the string as an NVARCHAR so when the execution plan is created, it includes the conversion of the row's VARCHAR to an NVARCHAR. This stops SQL Server from using an index and you end up with a very slow query.
What gets even more annoying (and potentially confusing) is that this does not happen with direct queries, only prepared statements (hence the issue with hibernate). It might be wise to make note of this fact in the FAQ or documentation. I only found 1 article on google in a news group that mentions this (and I am indebted to that person for enlightening me...)
The article:
http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&threadm=060201c2cee4%24246fd790%248ef82ecf%40TK2MSFTNGXA04&rnum=2&prev=/groups%3Fq%3DPrepared%2Bstatement%2Bindex%2Bscan%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26safe%3Doff%26selm%3D060201c2cee4%2524246fd790%25248ef82ecf%2540TK2MSFTNGXA04%26rnum%3D2
- Gary
------------------------------------------------------- This SF.net email is sponsored by Dice.com. Did you know that Dice has over 25,000 tech jobs available today? From careers in IT to Engineering to Tech Sales, Dice has tech jobs from the best hiring companies. http://www.dice.com/index.epl?rel_code=104 _______________________________________________ hibernate-devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/hibernate-devel
