I have an application which uses MS SQL Server 2005 as the DBMS and jTDS as the JDBC driver. All the columns storing text are of type VARCHAR. A sendStringParametersAsUnicode=false parameter has been specified for the driver in order to prevent it sending all strings as unicode (which would cause an index scan instead of index seek for indexed columns, thus hurting performance badly).
Now I have a need to store Unicode text in the database. My problem is that if I just change the column type to NVARCHAR, Hibernate still sends a non-Unicode string to the DB (without the N prefix) and because of the above parameter the driver will not change it to Unicode. The obvious solution would be to change all the text columns to NVARCHAR and remove the parameter. However, I have almost 100 text columns so changing all of them at once is very difficult and I would like to do it column by column. Also, there are some columns that ideally should remain CHAR or VARCHAR, since no Unicode text will ever be written to them.
So, is there a way I can tell Hibernate to send values for certain columns with the N prefix without affecting the other columns still using VARCHAR? To my understanding this should solve the problem. Or if you can suggest some other way to handle this problem, it would also be highly appreciated.
Edit: In the end, I opted to change all my text columns to NCHAR or NVARCHAR. What's interesting is that the size of the database grew to more than two times the original size. I don't know how that is possible, but I suppose I have to live with that.