This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Database Basics Quick Note - The difference in Varchar and Nvarchar data types Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

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.