Differences between char, nchar, varchar, and nvarchar in SQL Server

For general string fields in the program, SQL Server has char, varchar, nchar, and nvarchar types. What are the differences between these four types? Here we will make a comparison.

1. Fixed Length or variable length

The so-called fixed length is fixed length. when the length of the data to be saved is insufficient, an English space will be automatically filled behind it to make the length reach the corresponding length. If there is a var prefix, it indicates that the actual storage space is dynamically changed. For example, varchar and nvarchar variable-length data are not filled with spaces.

2. Unicode or non-Unicode

In the database, only one byte is required for English characters, but two bytes are required for Chinese characters and many other non-English characters. If both English and Chinese characters exist, the occupied space may lead to confusion, leading to garbled characters. Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The prefix n indicates Unicode characters, such as nchar and nvarchar. These two types Use the Unicode Character Set.

3. maximum storage capacity of several data types

Char and varchar can contain up to 8000 English letters and 4000 Chinese Characters

Nchar and nvarchar can store a maximum of 4000 characters, regardless of English or Chinese Characters

Copy codeThe Code is as follows: -- create a table
Create table TempTable (
Id int primary key,
CharField CHAR (10 ),
VarcharField VARCHAR (10 ),
NvarcharField NVARCHAR (10)
)

Insert into TempTable VALUES (1, 'wfth ')
Insert into TempTable VALUES (2, 'wind-free listening hai ')
Insert into TempTable VALUES (3 ,'','','')
Insert into TempTable (id) VALUES (4)
Insert into TempTable VALUES (5, '20160301', '20160301', '20160301 ')

Select datalength (charField) AS charFieldLen,
DATALENGTH (varcharField) AS varcharFieldLen,
DATALENGTH (nvarcharField) AS nvarcharFieldLen
FROM temptable WHERE id = 1

Select datalength (charField) AS charFieldLen,
DATALENGTH (varcharField) AS varcharFieldLen,
DATALENGTH (nvarcharField) AS nvarcharFieldLen
FROM temptable WHERE id = 2

Select datalength (charField) AS charFieldLen,
DATALENGTH (varcharField) AS varcharFieldLen,
DATALENGTH (nvarcharField) AS nvarcharFieldLen
FROM temptable WHERE id = 3

Select datalength (charField) AS charFieldLen,
DATALENGTH (varcharField) AS varcharFieldLen,
DATALENGTH (nvarcharField) AS nvarcharFieldLen
FROM temptable WHERE id = 4

Select datalength (charField) AS charFieldLen, charField,
DATALENGTH (varcharField) AS varcharFieldLen, varcharField,
DATALENGTH (nvarcharField) AS nvarcharFieldLen, nvarcharField
FROM temptable WHERE id = 5