Why Bad Data Type Choices Kill Performance

Choosing a data type seems simple…

If you want to store text, you have a few choices: VARCHAR, NVARCHAR, CHAR, NCHAR.

If you’re storing dates, you pick DATE, DATETIME or DATETIME2 depending on precision.

These seem obvious, yet I still see people storing dates in CHAR(8), routinely.

So what actually happens when you get your data types wrong?

1. Implicit Conversions (aka CONVERT_IMPLICIT)

This one often goes unnoticed for a long time. SQL Server politely hides the mistake from you. How kind!

Take this example:

‘Brendan’ stored as VARCHAR is not the same thing as ‘Brendan’ stored as NVARCHAR.

• In VARCHAR, the letter B is stored using 8 bits.
• In NVARCHAR, the same B consumes 16 bits.

SQL Server must convert one side of the comparison so the types match.

And since NVARCHAR is 16-bit Unicode, SQL Server can’t down-convert it to 8-bit.

So guess which side gets converted?

Correct: the VARCHAR column gets hit every time.

That becomes a very big deal when you’re using C# or .NET and dealing with predominantly ASCII data.

Most developers think: “We only store ASCII, let’s use VARCHAR everywhere to save space.”

But by default, .NET sends all string parameters to SQL Server as NVARCHAR, even if every character is ASCII.

So SQL Server sees:

• VARCHAR in the column
• NVARCHAR in the query parameter

…and is forced to convert the entire column to NVARCHAR before it can evaluate the predicate.

Yes, SQL Server must evaluate and convert each candidate row, which usually means every row in the table.

A real-world story

I once worked on an application years ago with this exact issue on its most frequently executed query: the login query.

• Username column → VARCHAR
• Application parameter → NVARCHAR

This ran fine for years, until the business grew. More users. More logins. More load.

Then one day, the entire application ground to a halt.

• CPU pegged at 100%
• Login queries taking 20–30 seconds
• The app essentially dead in the water
• And this wasn’t a “small” SQL Server, either.

What happened after fixing the implicit conversions?

CPU dropped under 20%, login queries fell to milliseconds, and the app was running faster than ever.

Implicit conversions are silent, deadly, and everywhere.

2. Queries Become Non-SARGable

Once SQL Server is forced to convert the column, your beautiful index becomes useless.

Example: WHERE Username = @Username — but types don’t match

If SQL Server converts the column, not the parameter, the index becomes non-SARGable.

That means:

• No index seeks
• Full scans
• High I/O
• Long runtimes

All because a data type didn’t match.

3. Oversized Data Types Waste Space and Break Memory Grants

Now let’s talk about another common mistake: choosing data types that are way larger than needed.

Example: Defining a column as NVARCHAR(2000) when NVARCHAR(50) would have been plenty.

You might think: “It only stores what’s actually used, right, what’s the harm?”

The harm is hidden in the query optimizer.

When SQL Server generates a query plan, it must estimate how much memory is needed before execution. It must do this so the memory is already available once it starts reading the data from disk. It does this by predicting the average length of variable-length columns.

If you declare a column as NVARCHAR(2000), SQL Server may assume a large average row size based on the declared max length, which is often around 50% of the declared length. In this example, that would be average length of ~1,000, even if most rows contain no more than 25-50 characters.

That leads to:

• Huge memory grants
• Excessive RAM consumption
• Buffer pool flushes
• Lower buffer cache hit ratio
• More reads going to slow disk instead of memory

A single oversized data type can push SQL Server into thinking it needs 2 GB of RAM for a query that actually needs 500 MB.

And all the data it flushes from memory?

Well, other queries likely still need it, but it’s now on disk.

In Summary

Bad data types don’t always hurt you immediately.

They hurt you when:

• Your workload grows
• Your data grows
• Your concurrency grows
• Your business grows

All queries run fast in Dev, when you only have a 1000 rows in a test table. But when those tables grow in production, that’s when the pain is begins.

Three things to remember:

1. Implicit conversions silently destroy performance and CPU.
2. Non-SARGable queries eliminate index usage.
3. Oversized data types inflate memory, I/O, and query cost.

Get your data types right early, and avoid serious downtime later.

---

If you like what you’ve read, please subscribe so you don’t miss my latest posts…