I saw the following question posted on a forum and left the answer
for the poster. I thought I would share it with you all as I am
in the process of the Database Basics posts, and this ties in.
“What is the difference between nvarchar and varchar data types?”
The difference in varchar and nvarchar datatypes is simple. Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is your choice. Varchar stores ASCII data and should be your data type of choice for normal use.
UNICODE requires 2 bytes for each character you store. ASCII
only requires 1 byte for each character. This is important
because of the row size limitations of Sql Server is the same as the
page size limit, which is 8060 bytes. This means a single row of
a single varchar column can be varchar(8000), but a single row of a single nvarchar column can only be nvarchar (4000).
Now, you can actually go and create a table with 5 columns of varchar(5000),
and Sql Server will let you do that. But once you go to put data
into it, its going to puke down your leg. This goes back to the
size of the data page, which is 8K. Rows cannot span pages (this
does not apply to Sql Server 2005), so until the page size is
increased, you have to adhere to that limitation.
Also, you might bring up the argument of Text and Image
fields. This data is stored seperately and a 24 byte pointer is
stored in the original data page.