nvarchar vs. varchar in SQL Server, BEWARE

I discovered one of the scariest performance problems with SQL Server I have ever seen today. This problem is so easy to over look, yet extremely detrimental to database performance.

Consider the following table.

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Users
     (
     id INT NOT NULL,
     username VARCHAR(50) NOT NULL
     )
GO
ALTER TABLE dbo.Users ADD CONSTRAINT
     PK_Users_id PRIMARY KEY CLUSTERED
     (
     id
     )
 
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_Users_username ON dbo.Users
     (
     username
     )
GO
COMMIT

Now look at the query plans of the following queries, that differ by a single character.

If you look closely, the query that uses an nvarchar parameter does an index scan while the one that users varchar does an index seek. This is very important, because an index seek is orders of magnitude faster than a scan.

The reason this occurs is because the parameter and column have different collation sets.

7 thoughts on “nvarchar vs. varchar in SQL Server, BEWARE

  1. nvarchar is for unicode.

    Really, it doesn’t matter what type you use, just that the column and parameter types are the same.

  2. Based on what I’ve read, nvarchar takes up twice the space as compared to varchar

  3. Great article – aligns completely with an issue we just ran into. Thanks for the heads up and also the sanity check, good stuff.

  4. We would need your suggestion here.

    We are having two MQTs (one of them is having large volume of data) . We are going to join these two MQTs on three fields and these fields are having datatype as varchar(30) in one MQT and in another as varchar(9).

    How much it will impact performance of queries if these fields have same column width means varchar(9) in both MQTs ?

    Please suggest !

Comments are closed.