nvarchar vs. varchar in SQL Server, BEWARE
by bhartsock on Dec.14, 2008, under Uncategorized
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. Thankfully, I ran across this article which helped me solve the problem as it related to NHibernate.
December 15th, 2008 on 10:44 am
How do the query plans turn out if the table is created with an nvarchar instead of a varchar?
December 15th, 2008 on 10:48 am
Is there any advantage to ever using nvarchar over varchar?
December 15th, 2008 on 7:39 pm
nvarchar is for unicode.
Really, it doesn’t matter what type you use, just that the column and parameter types are the same.
June 10th, 2009 on 3:06 am
Based on what I’ve read, nvarchar takes up twice the space as compared to varchar
September 16th, 2009 on 5:59 pm
Great article – aligns completely with an issue we just ran into. Thanks for the heads up and also the sanity check, good stuff.