Hi,
I'm using hibernate to persist data to bunch of different databases and it's working great. (My thanks). However, I've noticed a strange phenomenon when running against SQL Server and was wondering if there's anything I can do short of creating a custom type to influence it's behavior. I also want to appologize in advance for not supplying the mapping file. I'd have to run it thru compliance and I'm hoping that since I'm not asserting a hibernate bug, it won't matter. If that's not the case, just let me know.

I have a field in my database that is a defined as a nvarchar(50) in the schema. I have one installation where the guys that did the install used a varchar(50) instead for valid reasons. There's an index on that field and when I use hibernate to try and select a single row, the generated SQL makes the query expecting the field to be an nvarchar. When this happens, the database decides there's a type mismatch, ignores the index and does a table scan. (See below and note the leading "N" on the ARTICLE_REFERENCE_ID field in the first query.) If I change the datatype to nvarchar, the query returns instantaneously. My question is, by default hibernate allows me to declare the field a string, yet it clearly distinguishes between varchar and nvarchar when constructing a query. What config bits influence that behavior. IE: how does hibernate decide if the string in the underlying database is an nvarchar v.s. plain old varchar?
I figured it must be looking at metadata, but the behavior I'm seeing doesn't bear that out. The app works, but if I switch the underlying datatype between nvarchar and varchar, hibernate still sends the query with the leading "N" which results in a table scan. Any explanation or pointers to the right spot in the source would be great. I've spent some time trawling through the source and didn't find any likely suspects.

Hibernate version: 2.1.8

Name and version of the database you are using:
Recent rev of SQL Server via jTDS 1.2

The generated SQL (show_sql=true):

Index Scan:

select article0_.ARTICLE_ID as ARTICLE_ID, article0_.VERSION_ID as VERSION_ID, article0_.ARTICLE_REFERENCE_ID as ARTICLE_3_, article0_.RECORD_MODIFIED_DATETIME_UTC as RECORD_M4_,
article0_.RECORD_DELETED_DATETIME_UTC as RECORD_D5_, article0_.VENDOR_DATETIME_UTC as VENDOR_D6_, article0_.HOT_IND as HOT_IND, article0_.RETENTION_EXPIRY_DATETIME_UTC as
RETENTIO8_, article0_.SYSTEM_HASH_KEY as SYSTEM_H9_, article0_.PERMS as PERMS, article0_.PROVIDER_ID as PROVIDE11_ from MDT_ARTICLE article0_ where
(article0_.ARTICLE_REFERENCE_ID= N'NEWS.RSF.20060731.nBW315186a')

Index Seek:

select article0_.ARTICLE_ID as ARTICLE_ID, article0_.VERSION_ID as VERSION_ID, article0_.ARTICLE_REFERENCE_ID as ARTICLE_3_, article0_.RECORD_MODIFIED_DATETIME_UTC as RECORD_M4_,
article0_.RECORD_DELETED_DATETIME_UTC as RECORD_D5_, article0_.VENDOR_DATETIME_UTC as VENDOR_D6_, article0_.HOT_IND as HOT_IND, article0_.RETENTION_EXPIRY_DATETIME_UTC as
RETENTIO8_, article0_.SYSTEM_HASH_KEY as SYSTEM_H9_, article0_.PERMS as PERMS, article0_.PROVIDER_ID as PROVIDE11_ from MDT_ARTICLE article0_ where
(article0_.ARTICLE_REFERENCE_ID= 'NEWS.RSF.20060731.nBW315186a')


Thanks!