The Dangers of nvarchar(max) in SQL for Entity Framework Developers

Tim Corey

10m 27s

When dealing with nvarchar in SQL, developers often ignore how this data type affects performance - especially when working in C# using Entity Framework. In a focused 10-minute video titled "The Dangers of nvarchar(max) in SQL for Entity Framework Developers", Tim Corey explores the impact of using nvarchar(max) as a default value for string fields in a SQL Server database.

This article is a detailed explanation of Tim's video using his demonstrations and reasoning only, with examples and performance comparisons. If you're relying on nvarchar(max) without understanding how it works under the hood, this will serve as an eye-opener.

Understanding the Problem: Default Behavior in Entity Framework

Tim starts by describing a common Entity Framework scenario where a C# developer defines a model with fields like FirstName and LastName. When the table is automatically created in SQL Server using migrations, the generated schema sets those string fields to nvarchar(max) by default.

As Tim explains, this happens because Entity Framework doesn't know the appropriate string size to assign, so it chooses the safe route - assigning maximum length by default. That means each nvarchar column allows up to 2^31-1 characters, with maximum storage size in the gigabytes.

This decision seems convenient, but it hides dangerous performance costs.

Example Setup with Two Tables: nvarchar(max) vs Fixed Length

To highlight the issue, Tim creates two identical tables:

• Users: with nvarchar(50) for first and last names.
• UsersToTheMax: with nvarchar(max) for the same fields.

At 2:39, Tim explains how he populated both tables with 1 million identical rows using Dapper, ensuring that only the nvarchar data type differs.

This setup allows him to make a consistent comparison between a fixed-length Unicode column and a variable length max column.

Comparing Queries and Execution Plans

Tim uses the following SQL query on both tables:

SELECT * FROM dbo.Users ORDER BY LastName;
SELECT * FROM dbo.UsersToTheMax ORDER BY LastName;

SELECT * FROM dbo.Users ORDER BY LastName;
SELECT * FROM dbo.UsersToTheMax ORDER BY LastName;

At 3:34, he enables the actual execution plan to analyze what SQL Server does internally when executing these queries.

Note: This test isn't about total execution time across machines - Tim emphasizes comparing queries on the same server with the same data, to isolate how the nvarchar(max) affects performance.

The Shocking Results

The execution plans reveal a major difference:

• The query on nvarchar(50) uses just 2% of the batch cost.
• The query on nvarchar(max) uses a whopping 98% of the cost.

As Tim puts it, this means the max query is 50 times more expensive in terms of how SQL Server handles it - even though the column data entries are the same and relatively small.

In terms of CPU time:

• Sorting nvarchar(50) takes 107ms.
• Sorting nvarchar(max) takes 339ms.

But the biggest difference is in a specific parallelism operation:

• Fixed length: 0.43s
• Max length: 22.17s

That's over 50x slower, even with identical data.

Memory Consumption Differences

Tim dives into memory grants - how much memory SQL Server allocates for each query:

• nvarchar(50) query: 340MB
• nvarchar(max) query: 641MB

This alone is a red flag, but when testing uncached columns, the impact is even more dramatic:

• Fixed length on FirstName: 357MB
• Max length on FirstName: 8.5GB

This increase occurs because SQL Server doesn't know how large the nvarchar value could be when defined as max, so it reserves a larger block of memory to accommodate the maximum size.

Why Is nvarchar(max) So Expensive?

At 9:15, Tim explains the underlying reason. The nvarchar(max) data type:

• Supports up to 2^31-1 Unicode characters, consuming up to 2GB of storage space.
• Requires SQL Server to store the value outside the row if it doesn't fit, using a pointer instead of direct in-row storage.
• Cannot be indexed in the same way as fixed-length columns.

As a result:

• You can't index an nvarchar(max) column, which means SQL Server must sort or filter the full dataset without optimization.
• This affects operations like ORDER BY, WHERE, or JOINs on nvarchar(max) fields.

This behavior leads to significant memory use, CPU load, and slowdowns - just from choosing the wrong character data length.

Tim's Final Recommendation

As Tim says in closing:

"In your Entity Framework queries, make sure you specify the size of all strings."

Always define your string properties with a maximum number of characters, like nvarchar(100) or nvarchar(255), depending on the expected data. This minor change ensures:

• Optimized storage space
• Support for indexing
• Reduced query cost
• Better performance consistency

By setting an appropriate length, you make your database schema more efficient and avoid the pitfalls of lazy default settings.

Conclusion

Tim Corey's video delivers a critical lesson: using nvarchar(max) as a default length for string fields in SQL can cripple performance - without you even realizing it. SQL Server will allocate excessive memory, skip indexes, and increase CPU costs, even for normal Unicode text entries like names or addresses.

The takeaway? Understand the nvarchar data type, and avoid max unless you truly need it for fields that might store large documents or variable-length content.

By specifying string size, you not only save bytes and memory - you also make your Entity Framework and SQL code more efficient, scalable, and robust. Following Tim's guidance, you ensure that your application isn't slow by design.

For anyone working with databases in .NET, it's a best practice that should be part of your standard toolkit. Check out Tim's Channel for more SQL related videos.