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.

Menu