Compare the application's indexed lookup with SSMS, and the visible value can look identical. The sendStringParametersAsUnicode property is one place to investigate when a Microsoft JDBC request disagrees with your SSMS test.
Follow the Type Across the Connection
A string in application memory does not specify the SQL Server parameter type by itself. The driver chooses a representation when sending the value. The column type and parameter type then participate in SQL Server's comparison rules.
The Microsoft JDBC driver defaults sendStringParametersAsUnicode to true for the relevant non-national character setters. A setString call can therefore reach SQL Server as a Unicode parameter. Compare that behavior with an indexed varchar column before blaming the index definition.
Unicode types have higher data type precedence than corresponding non-Unicode types. SQL Server can introduce an implicit conversion during the comparison. Depending on collation and optimization, converting the column can complicate an efficient seek.
This is not a promise that every such comparison scans. Windows and SQL collations can produce different access behavior. Inspect the actual plan and reads for the specific database, driver configuration, and query.
Build a Small Indexed Example
Use a separate test database for the following setup. The column deliberately uses a SQL collation to make the comparison behavior worth inspecting. The generated data is a workload input, rather than a report of measured performance.
DROP TABLE IF EXISTS dbo.CustomerLookup;
CREATE TABLE dbo.CustomerLookup
(
CustomerId int NOT NULL PRIMARY KEY,
CustomerCode varchar(20)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CustomerName nvarchar(100) NOT NULL
);
CREATE UNIQUE INDEX IX_CustomerLookup_Code
ON dbo.CustomerLookup(CustomerCode);
WITH Digits AS
(
SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Numbers AS
(
SELECT a.n + 10*b.n + 100*c.n + 1000*d.n + 1 AS n
FROM Digits AS a CROSS JOIN Digits AS b
CROSS JOIN Digits AS c CROSS JOIN Digits AS d
)
INSERT dbo.CustomerLookup(CustomerId, CustomerCode, CustomerName)
SELECT n,
'C' + RIGHT('00000' + CONVERT(varchar(5), n), 5),
N'Test customer'
FROM Numbers;The query selects only the key and indexed code to avoid introducing a lookup comparison. Keep the same value in both executions. Changing the value and the type together would make the result harder to interpret.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXEC sys.sp_executesql
N'SELECT CustomerId, CustomerCode
FROM dbo.CustomerLookup WHERE CustomerCode = @Code;',
N'@Code nvarchar(20)', @Code = N'C05000';
EXEC sys.sp_executesql
N'SELECT CustomerId, CustomerCode
FROM dbo.CustomerLookup WHERE CustomerCode = @Code;',
N'@Code varchar(20)', @Code = 'C05000';
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Include actual execution plans in SSMS for this test. In my SQL Server 2025 run, the nvarchar parameter produced an index scan, and the varchar parameter produced a seek. Look for CONVERT_IMPLICIT involving CustomerCode and compare seek predicates with residual predicates. A warning or scan is evidence to interpret, rather than the entire diagnosis.
Set sendStringParametersAsUnicode Deliberately
The following Java example uses the official Microsoft JDBC driver. It assumes the driver is already available on the application's classpath. Supply credentials through approved environment configuration, rather than embedding a password in source code.
// Java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
public class CodeLookup {
public static void main(String[] args) throws Exception {
SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName(System.getenv("SQL_SERVER"));
ds.setDatabaseName(System.getenv("SQL_DATABASE"));
ds.setUser(System.getenv("SQL_USER"));
ds.setPassword(System.getenv("SQL_PASSWORD"));
ds.setEncrypt("true");
ds.setTrustServerCertificate(false);
ds.setSendStringParametersAsUnicode(false);
try (Connection cn = ds.getConnection();
PreparedStatement ps = cn.prepareStatement(
"SELECT CustomerId FROM dbo.CustomerLookup " +
"WHERE CustomerCode = ?")) {
ps.setString(1, "C05000");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) System.out.println(rs.getInt(1));
}
}
}
}A connection-string configuration can express the same property as sendStringParametersAsUnicode=false. Apply it when creating the physical connection, then rebuild the affected pool through normal application procedures. Existing pooled connections do not acquire new settings merely because a configuration file changed.
I check the effective connection configuration before reviewing an application's SQL text. I also check the setter method beside every important string parameter. An index cannot negotiate a different type with an application that keeps sending the wrong one.
Keep Unicode Data Safe With sendStringParametersAsUnicode
Setting this property to false is appropriate only when the affected non-national parameters match the intended data representation. Characters outside the selected code page need separate attention. A performance improvement that corrupts a customer name fails the actual requirement.
For nvarchar and nchar data, use national character methods such as setNString. These methods send Unicode regardless of the property setting. A mixed schema can therefore use explicit setters rather than forcing every string through one assumption.
A connection-wide change deserves a review of all string bindings using that connection. Include inserts, updates, predicates, and stored procedure parameters in that review. Test round trips with representative characters, not only plain letter-and-digit codes.
If the business requires broader character support, changing the column to nvarchar can be the better design. Match stored procedure parameters and application bindings to the new column type. Review indexes, storage, constraints, and comparison semantics before deploying that schema change.
Inspect Cached Plans Without Calling Every Conversion Harmful
Cached plan XML can help locate candidates during an investigation. The following query searches a bounded set of recent cached statements. Appropriate server performance permissions are required, and the result is only a candidate list.
SELECT TOP (25)
qs.last_execution_time,
qs.execution_count,
st.text AS BatchText,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE CONVERT(nvarchar(max), qp.query_plan) LIKE N'%CONVERT_IMPLICIT%'
ORDER BY qs.last_execution_time DESC;This text search does not classify the converted expression or its runtime cost. A conversion on the parameter can be harmless for index access. Open the relevant plan and connect the expression to the operator and predicate.
Cache eviction and recompilation remove evidence, while execution counts describe the cached entry's lifetime. Capture the actual application parameter metadata when possible. The SSMS reproduction must use the same types and lengths to be meaningful.
Verify the Fix From Data Through Execution
Does the application preserve every required character after the configuration change? Test that first, then compare reads and plans for representative values. Include selective and unselective values so a single favorable lookup does not define the conclusion.
A small table can justify a scan even with perfectly matched types. Parameter sensitivity, statistics, and index coverage remain separate considerations. Correct parameter typing removes one source of uncertainty without guaranteeing one particular operator.
Repeat the comparison from a fresh physical connection using each approved setting. Capture the first execution separately from subsequent executions. That distinction exposes compilation differences without clearing the shared production cache.
Check procedure declarations when the application calls a stored procedure instead of a direct prepared statement. A varchar column compared with an nvarchar procedure parameter still contains a type mismatch. Changing the connection property cannot rewrite the procedure definition.
Include NULL values and maximum supported lengths in the application test. Verify that long inputs are rejected or handled according to the business contract. An accidental truncation can turn an apparently successful lookup into a different lookup entirely.
Treat collation changes as their own schema decision, rather than a quick substitute for correcting bindings. Collation affects equality, ordering, and uniqueness across existing data. Review those semantics before interpreting a changed access plan as an unconditional improvement.
Document the column type, collation, setter method, driver configuration, and measured comparison. That record makes the decision understandable when another developer changes the binding later. Keep the data contract and performance evidence together.
Related reading on this blog: Implicit Conversions That Quietly Turn Seeks Into Scans and How to Fix CONVERT_IMPLICIT Warnings?.
A string parameter is not just text, it is a typed value that must match the database's data contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
