SQL Server - Difference between varchar and nvarchar data type

Wikitechy | 315 Views | sql server | 08 Jun 2016

 

  • How varchar & nvarchar datatypes are similar?
  • Both data type was used to store text/string data in them
  • The amount of space that both use depends on the size of the data that we are putting in it. It grows with every character that you store

Storage capability :

  • Varchar stores data at 1 byte per character.
  • Nvarchar stores data at 2 bytes per character.

Example :

  • declare @wikitechy varchar(100) - > 100 bytes to store
  • declare @wikitechy nvarchar(100) - > 200 bytes to store

Max capability :

  • Varchar supports up to 2GB (max data type) characters.
  • Nvarchar only supports up to 4000 characters.

Example :

  • declare @wikitechy varchar(8000) - > max 8000 accepted in this. 
  • declare @wikitechy varchar(max) - > max allocates maximum of 2GB of data now
  • declare @wikitechy nvarchar(4000) - > 200 bytes to store

Unicode capability :

  • Nvarchar supports Unicode character to store.
For example, Japanese character are Unicode characters which we can store as nvarchar datatype.

  • Varchar supports non-unicode character to store. 
For example: English language are non-Unicode characters.

Applies to sqlserver version : 

  • SQL Server 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Related Tags :

  • Differences,
  • Sql ServerDifference Between VARCHAR and NVARCHAR Data Type 
  • NVARCHAR
  • VARCHAR
  • Varchar and Nvarchar 
  • varchar vs nvarchar