NVARCHAR & VARCHAR

     1.difference between varchar & nvarchar

char [ (  n ) ]

Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.

varchar [ (  n |  max ) ]

Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes.The ISO synonyms for varchar are char varying or character varying.

nchar [ ( n ) ]

Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times nbytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character..

nvarchar [ ( n |  max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is twotimes the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

2.use datalength to get the storage size, you can't use LEN because LEN just counts the characters, see here: The differences between LEN and DATALENGTH in SQL Server

--to verify the length of varchar & nvarchar ()

DECLARE @n NVARCHAR(10)
DECLARE @v VARCHAR(10)


SELECT @n = 'A', @v='A'


SELECT  DATALENGTH(@n),DATALENGTH(@v)

SELECT  len(@n),len(@v)

3.Different  in MS SQL version

In MS SQL Server 2000 a row cannot exceed the size of 8000 bytes. In MS SQL Server 2000 a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters. This is a limitation in MS SQL Server 2000 that has been solved in MS SQL Server 2005.

In MS SQL Server 2005 (and 2008) you can use 'MAX' in the length of a VARCHAR, NVARCHAR and VARBINARY field (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)). These data types can hold the 2 GB of data (same as BLOBs can hold).

reference: http://stackoverflow.com/questions/4713608/does-an-nvarchar-always-store-each-character-in-two-bytes