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

Varchar occupies 2 bytes for each English (ASCII) character, and only two bytes for one Chinese Character
Char1 byte for English (ASCII) characters and 2 bytes for one Chinese Character
The varchar type is not filled with spaces, such as varchar (100). However, if its value is "Qian", its value is "Qian"
WhileCharFor exampleChar(100), its value is "Qian", but in fact it is "Qian" in the database (there are 96 spaces after Qian,
Is to fill it with 100 bytes ).

BecauseCharIt is a fixed length, so it will be much faster than varchar! However, it is a little troublesome for the program to process it. We need to use functions such as trim to remove spaces on both sides!

1,Char.CharIt is convenient to store fixed-length data,CharHigh Field indexing efficiency, such as definitionChar(10), no matter whether the data you store reaches 10 bytes, it occupies 10 bytes of space.

2. varchar. Variable-length data is stored, but the storage efficiency is notCharHigh. If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as varchar (10. The actual length of the varchar type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length.

Before modification (Char)

After modification (varchar)

 

The above two figures are posted by netizens in the forum. We can see that the modified pw_user "Weight Loss" effect is quite obvious.

 

In terms of space, it is appropriate to use varchar; in terms of efficiencyCharThe key is to find a balance point based on the actual situation.

3. Text. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.

4. nchar,NvarcharAnd ntext. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, 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. Nchar,NvarcharThe length is between 1 and 4000. AndChar, Varchar comparison, nchar,NvarcharA maximum of 4000 characters can be stored, whether in English or Chinese.CharVarchar can store up to 8000 English letters and 4000 Chinese characters. We can see that nchar,NvarcharThe data type is convenient because you do not have to worry about whether the entered characters are English or Chinese characters.
So generally, if it contains Chinese characters, use nchar/NvarcharIf it is a pure English text or number, useChar/Varchar.
Set the value types of all tables fromChar, Change varchar to nchar,NvarcharStored Procedures
/* -- Convert all the tables and values fromChar, Change varchar to nchar,NvarcharStored Procedures

-- Producer build 2004.02 (reference please keep this information )--*/

/* -- Call example:
Exec p_set
--*/
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_set] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_set]
Go

Create procedure p_set
As
Declare TB cursor
SelectSQL= 'Alter table ['+ D. Name
+ '] Alter column [' + A. Name + '] N'
+ B. Name + '(' + Cast (A. length * 2 as varchar) + ')'
From syscolumns
Left join policypes B on A. xtype = B. xusertype
Inner join sysobjects D on A. ID = D. id and D. xtype = 'U' and D. Name <> 'dtproperties'
Where
B. Name in ('Char', 'Varchar ')
And
Not exists (select 1 from sysobjects where xtype = 'pk' and name in (
Select name from sysindexes where indid in (
Select indid from sysindexkeys where id = A. ID and colid = A. colid
) -- The primary key cannot be modified.
Order by D. Name, A. Name

Declare @SQLVarchar (1000)
Open TB
Fetch next from TB @SQL
While @ fetch_status = 0
Begin
Exec (@SQL)
Fetch next from TB @SQL
End
Close TB
Deallocate TB
Go