QueryEntity uses NVARCHAR(4000) in query even though our data is VARCHAR(50)

Question

Hi there, 
 Our business is having a problem when using a queryEntity in Appian for a certain field. It converts that field to an NVARCHAR(4000) (I'm assuming because it's a text field) even though our Database and Data Type have it listed as VARCHAR(50). In SQL Server, when comparing an NVARCHAR to a VARCHAR, it causes an implicit conversion of all data in the VARCHAR column which is a big performance hit for us. 
 Is there any way to make sure that the parameter uses VARCHAR(50) instead of NVARCHAR(4000)? Thank you

Tyler - APM Associates · Answer

You can leverage your connection string to your db to prevent the connection from generating your queries with N prefixes for strings. 
 sendStringParametersAsUnicode=false for SQL Server 
 By default, this is set to true. 
 If the sendStringParametersAsUnicode property is set to true , which is the default value, string parameters are sent to the server in UNICODE format. If sendStringParametersAsUnicode is set to false string parameters are sent to the server in an ASCII/MBCS format, not in UNICODE. 
 You should not use this setting if your application / db stores unicode data or could do so. If you want to have the best of both worlds, you can leverage a second data source that has this set to false for tables and views where unicode support is not needed, and the other where the default / true is set for tables and views that should support unicode. 
 According to Appian's supported JPA annotations there is no support for @Nationalized which if supported may allow you to properly set this flag at the connection level and then have your nationalized columns prepared with nationalized columns. 
 I will note that hibernate does support this annotation, so it stands to reason this would work as Appian uses (or did use) hibernate, but I have not tested this. 
 If storage space is less of a concern, an alternative to this connection based approach would be to convert your columns to nationalized versions which would avoid your implicit conversion performance issues. If you do that only on columns you index or search against then you should be able to find a middle ground between storage costs and performance at scale. As always I strongly recommend you test all of these options yourself to determine the best solution for your individual needs. 
 Reference Material: 
 
 https://learn.microsoft.com/en-us/sql/connect/jdbc/reference/setsendstringparametersasunicode-method-sqlserverdatasource?view=sql-server-ver16 
 docs.jboss.org/.../Nationalized.html