Table of Contents

UTF16 vs. UTF8: VARCHAR vs. NVARCHAR

The Silent Performance Killer: Implicit String Conversions in SQL Server

If database queries are suddenly running slow despite perfect indexes, the culprit might be a silent performance killer: implicit data type conversion.

Here is how it happens, why it destroys performance, and how to resolve it across C# (.NET), Node.js, and SQL Server database design.

The Root Cause: UTF-16 vs. UTF-8 (NVARCHAR vs. VARCHAR)

By default, modern application runtimes handle strings as UTF-16. Because of this, database drivers and ORMs automatically send string parameters to SQL Server as NVARCHAR (Unicode).

However, if the SQL Server database column is defined as VARCHAR (ANSI/Non-Unicode), SQL Server cannot directly compare the two. According to SQL Server's data type precedence rules, NVARCHAR has a higher precedence than VARCHAR.

Instead of converting the incoming parameter downward, SQL Server is forced to convert every single value in the database column upward to match the parameter.

The Performance Consequences

When this implicit conversion happens on a column, it triggers a cascade of performance penalties:

  • Index Elimination: SQL Server completely ignores any existing indexes built on that VARCHAR column.
  • Table/Index Scans: Instead of a lightning-fast Index Seek (finding a needle in a haystack), SQL Server must perform an Index Scan or Table Scan (reading the entire haystack, line by line).
  • High CPU Overhead: The database server's CPU spikes because it wastes cycles converting thousands or millions of database rows during every single query.

The most permanent and bulletproof solution is to update the database schema. If the application layer naturally passes Unicode strings (NVARCHAR), changing the underlying database column from VARCHAR to NVARCHAR eliminates the data type mismatch entirely.

This ensures that the parameter and the column share the same data type, restoring the ability to use Index Seeks without changing any application code.

SQL Migration Example:

-- Alter the column to match the application's native NVARCHAR type
ALTER TABLE Users 
ALTER COLUMN Email NVARCHAR(255) NOT NULL;

Note: Before applying this change, verify the database storage requirements, as NVARCHAR uses 2 bytes per character compared to 1 byte for VARCHAR (unless utilizing SQL Server's data compression features).

Solution 2: Fixing the Code Layer in .NET (C#)

If modifying the database schema is not an option, the data provider must explicitly be instructed to send the parameter as a standard ANSI string (VARCHAR).

1. Raw ADO.NET / SqlClient

Force the parameter type manually:

C#

// Forces the parameter to be sent as VARCHAR instead of NVARCHAR
command.Parameters.Add("@Param", SqlDbType.VarChar).Value = myString;
2. Dapper

Wrap the string in a DbString object and set IsAnsi = true:

C#

// Wraps the string in a DbString mapping configured for ANSI (VARCHAR)
connection.Query("SELECT * FROM Users WHERE Email = @Email",
    new { Email = new DbString { Value = myString, IsAnsi = true } });
3. Entity Framework Core (EF Core)

In EF Core, this can be configured globally or per-property inside the DbContext using the Fluent API, ensuring EF generates the correct VARCHAR parameter.

C#

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Fix for a single property
    modelBuilder.Entity<User>()
        .Property(u => u.Email)
        .IsUnicode(false); // false tells EF Core to use VARCHAR instead of NVARCHAR

    // Optional: Globally configure ALL strings to be VARCHAR by default
    /*
    foreach (var entity in modelBuilder.Model.GetEntityTypes())
    {
        foreach (var property in entity.GetProperties().Where(p => p.ClrType == typeof(string)))
        {
            property.SetIsUnicode(false);
        }
    }
    */
}

Solution 2: Fixing the Code Layer in Node.js

Node.js strings are also natively UTF-16. If using popular SQL Server clients like mssql (Tedious) or ORMs like TypeORM, they will default to NVARCHAR unless told otherwise.

1. Raw Node-MSSQL (mssql package)

Explicitly assign the VarChar type when adding parameters to the request:

JavaScript

const sql = require('mssql');

const request = new sql.Request();
// Force the parameter to be VarChar
request.input('Email', sql.VarChar, myString);
const result = await request.query('SELECT * FROM Users WHERE Email = @Email');
2. TypeORM

In TypeORM, specify the exact database type directly within the @Column decorator of the entity metadata. This ensures that generated queries treat the parameter as a VARCHAR.

TypeScript

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;

    // Force TypeORM to map and query this as a VARCHAR column
    @Column({ type: "varchar", length: 255 }) 
    email: string;
}
Edit this page