SQL Server JDBC – Set sendStringParametersAsUnicode to false
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to explain why you should always disable the sendStringParametersAsUnicode default JDBC Driver setting when using SQL Server.
Database table
Let’s assume we have the following database table:
The PostID
column is the Primary Key, and the Title
column is of the VARCHAR
type and has a secondary index as well:
CREATE INDEX IDX_Post_Title ON Post (Title)
The Post
table contains the following records:
| PostID | Title | |--------|---------------------------------------------| | 1 | High-Performance Java Persistence, part 1 | | 2 | High-Performance Java Persistence, part 2 | | 3 | High-Performance Java Persistence, part 3 | | 4 | High-Performance Java Persistence, part 4 | | .. | .. | | 249 | High-Performance Java Persistence, part 249 | | 250 | High-Performance Java Persistence, part 250 |
As you can see, the Title
column is highly selective since every record has a different title value.
Unexpected CONVERT_IMPLICIT and Clustered Index Scan
When finding a Post
row by its associated Title
column value, we expect an Index Seek operation against the IDX_Post_Title
index, but this is not what we get when using the default SQL Server JDBC settings.
For instance, if we enable the runtime query statistics to retrieve the associated execution plan of the SQL query that filters by the Title
column:
executeStatement(entityManager, "SET STATISTICS IO, TIME, PROFILE ON"); try (PreparedStatement statement = connection.prepareStatement(""" SELECT PostId, Title FROM Post WHERE Title = ? """ )) { statement.setString(1, title); if (statement.execute() && statement.getMoreResults()) { LOGGER.info("Execution plan: {}{}", System.lineSeparator(), resultSetToString(statement.getResultSet()) ); } }
We get the following SQL execution plan:
|StmtText | |----------------------------------------------------------------------------------------------------| |SELECT PostId, Title FROM Post WHERE Title = @P0 | | |--Clustered Index Scan(OBJECT:([high_performance_sql].[dbo].[Post].[PK__Post__AA12603828AEBF55]),| | WHERE:(CONVERT_IMPLICIT(nvarchar(255),[high_performance_sql].[dbo].[Post].[Title],0)=[@P0])) |
The Clustered Index Scan
operation tells us that SQL Server has used the PostId
Clustered Index to scan the leaf pages in search of the Title
value we provided.
The reason why the IDX_Post_Title
index was not used is because of the implicit conversion that was done between the provided NVARCHAR
value and the VARCHAR
value of the Title
column.
Even if we provided the Title
bind parameter value as a VARCHAR
using the setString
method:
statement.setString(1, title);
The SQL Server JDBC Driver behaved as if we used setNString
method instead.
SQL Server JDBC sendStringParametersAsUnicode configuration
By default, SQL Server sends all String
parameter values as NVARCHAR
since the sendStringParametersAsUnicode
configuration is set to true
.
So, if we set the sendStringParametersAsUnicode
configuration value to false
jdbc:sqlserver://localhost;instance=SQLEXPRESS;databaseName=high_performance_sql;sendStringParametersAsUnicode=false;
And, rerun the previous SQL query, we will get the following execution plan:
|StmtText | |--------------------------------------------------------------------------------| |SELECT PostId, Title FROM Post WHERE Title = @P0 | | |--Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[IDX_Post_Title]), | | SEEK:([high_performance_sql].[dbo].[Post].[Title]=[@P0]) ORDERED FORWARD)|
That’s exactly what we were expecting from the start. There’s an Index Seek on the IDX_Post_Title
index, and there’s no implicit conversion happening anymore.
Handing Unicode characters
Now, even if you disable the sendStringParametersAsUnicode
setting, you can still persist Unicode data in NHAR
, NVARCHAR
or NLONGVARCHAR
column.
So, if the Title
column is of the NVARCHAR
type:
CREATE TABLE Post ( PostID BIGINT NOT NULL, Title NVARCHAR(255), PRIMARY KEY (PostID) )
We can set the Title
column using the setNString
PreparedStatement
method:
try (PreparedStatement statement = connection.prepareStatement(""" INSERT INTO Post (Title, PostID) VALUES (?, ?) """ )) { statement.setNString(1, "România"); statement.setLong(2, 1L); assertEquals(1, statement.executeUpdate()); }
And, we can read the Title
column using the getNString
ResultSet
method:
try (PreparedStatement statement = connection.prepareStatement(""" SELECT Title, PostId FROM Post WHERE Title = ? """ )) { statement.setNString(1, "România"); try(ResultSet resultSet = statement.executeQuery()) { if (resultSet.next()) { assertEquals("România", resultSet.getNString(1)); assertEquals(1L, resultSet.getLong(2)); } } }
If you’re using JPA and Hibernate, the NVARCHAR
column needs to be annotated with the @Nationalized
Hibernate annotation to instruct Hibernate that the underlying String
attribute needs to be handled by the StringNVarcharType
, as opposed to the default StringType
:
@Entity(name = "Post") public class Post { @Id @Column(name = "PostID") private Long id; @Column(name = "Title") @Nationalized private String title; }
Awesome, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
If you’re using SQL Server with Java, it’s a good idea to set the sendStringParametersAsUnicode
configuration property value to false
, as otherwise, you can face significant performance issues associated with CHAR
, VARCHAR
or LONGVARCHAR
indexes.
In fact, even the SQL Server JDBC Driver performance tuning page recommends disabling this property, which is set to true
for backward compatibility reasons.

Thank you for inspiring article, but are you sure about your results? I tried the scenario you described and SQL server have chosen index seek operation even in case of sendStringParametersAsUnicode=true.
May be it is dependent of SQL server version. I used MSSQL 2017.
Regards
Pavel Rund
You’re welcome.
This test provides the proof.
Here are the results:
So, you can run the test for yourself and see that it works as explained in the article.