What are the main performance differences between varchar and nvarchar SQL Server data types?

I'm working on a database for a small web app at my school using SQL Server 2005.
I see a couple of schools of thought on the issue of varchar vs nvarchar:

1. Use varchar unless you deal with a lot of internationalized data, then use nvarchar.
2. Just use nvarchar for everything.

I'm beginning to see the merits of view 2. I know that nvarchar does take up twice as much space, but that isn't necessarily a huge deal since this is only going to store data for a few hundred students. To me it seems like it would be easiest not to worry about it and just allow everything to use nvarchar. Or is there something I'm missing?

Solution 1:

Disk space is not the issue... but memory and performance will be. Double the page reads, double index size, strange LIKE and = constant behaviour etc

Do you need to store Chinese etc script? Yes or no...

And from MS BOL "Storage and Performance Effects of Unicode"

Edit:

Recent SO question highlighting how bad nvarchar performance can be...

SQL Server uses high CPU when searching inside nvarchar strings

Solution 2:

Always use nvarchar.

You may never need the double-byte characters for most applications. However, if you need to support double-byte languages and you only have single-byte support in your database schema it's really expensive to go back and modify throughout your application.

The cost of migrating one application from varchar to nvarchar will be much more than the little bit of extra disk space you'll use in most applications.

Solution 3:

Be consistent! JOIN-ing a VARCHAR to NVARCHAR has a big performance hit.

Solution 4:

nvarchar is going to have significant overhead in memory, storage, working set and indexing, so if the specs dictate that it really will never be necessary, don't bother.

I would not have a hard and fast "always nvarchar" rule because it can be a complete waste in many situations - particularly ETL from ASCII/EBCDIC or identifiers and code columns which are often keys and foreign keys.

On the other hand, there are plenty of cases of columns, where I would be sure to ask this question early and if I didn't get a hard and fast answer immediately, I would make the column nvarchar.