VARCHAR VS NVARCHAR

What are the differences between VARCHAR and NVARCHAR [VARCHAR VS NVARCHAR]

Which data type should use for defining a column in a table (VARCHAR VS NVARCHAR)?

	VARCHAR	NVARCHAR
Bytes	It consumes 1 byte per character.	It consumes 2 bytes per character.
Data Length	The maximum number of characters for this data type can hold up to 8000 characters by defining as VARCHAR(8000) OR  unlimited by defining as VARCHAR(MAX).	The maximum number of characters for this data type can hold up to 4000 characters by defining as NVARCHAR (4000)  OR unlimited by defining as NVARCHAR(MAX). MAX indicates that the maximum storage size is (2147483647)  2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes.
Data Storage Type	Used to store variable length value as String.	Used to store variable length value as String.
Storage	Content is dynamic (Variable Length Size Variable) and null, manage storage space efficiently with VARCHAR.	Content is dynamic (Variable Length Size Variable), has UNICODE and null, manage storage space efficiently with NVARCHAR.
Use When?	Use VARCHAR data type when content (data) size is variable, does not have any UNICODE characters and/or  NULL. VARCHAR  exactly occupies 1 byte.	Use NVARCHAR data type when content (data) size is variable,   has any UNICODE characters and/or  NULL. Note: Unicode Example: ¥ ¼ Ñ ñ (Japanese, Korean, etc.) NVARCHAR  exactly occupies 2 bytes.
Optional Parameter value	The optional parameter value N [VARCHAR(N)] is not specified in the variable declaration or column definition then it is considered as 1. DECLARE @VarChar varchar = 'VarChar' PRINT DATALENGTH(@VarChar) OUTPUT: 1	The optional parameter value N [NVARCHAR(N)] is not specified in the variable declaration or column definition then it is considered as 2. DECLARE @VarChar nvarchar = '¥arChar' PRINT DATALENGTH(@VarChar) OUTPUT: 2
Data Conversion	DECLARE @Name VARCHAR(50) = 'Govindaraj Kanniappan' SELECT CAST(@Name AS VARCHAR) Name, DATALENGTH(CAST(@Name AS VARCHAR)) NameLength OUTPUT : Name NameLength Govindaraj Kanniappan 21	DECLARE @Name NVARCHAR(50) = 'Govindaraj Kanniappan' SELECT CAST(@Name AS NVARCHAR) Name, DATALENGTH(CAST(@Name AS NVARCHAR)) NameLength OUTPUT : Name NameLength Govindaraj Kanniappan 42
Example	DECLARE @Name VARCHAR(MAX) = '' DECLARE @Loop INT = 1 WHILE @loop <= 8000 BEGIN       SET @Name += 'A'       SET @Loop += 1 END PRINT DATALENGTH(@Name) OUTPUT: 8000	DECLARE @Name NVARCHAR(MAX) = '' DECLARE @Loop INT = 1 WHILE @loop <= 8000 BEGIN       SET @Name += 'A'       SET @Loop += 1 END PRINT DATALENGTH(@Name) OUTPUT: 16000

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF