The difference between char nchar varchar nvarchar data types in SQL2005 and the use environment explain _mssql2005

Now excerpt the explanations for these data types in sql2005 Books Online

Character data types (nchar length fixed, nvarchar length variable), and Unicode data use the Unicode UCS-2 character set.

nchar [(N)]

N-Character fixed-length Unicode character data. The n value must be between 1 and 4,000 (inclusive). Storage size is twice times n bytes. The SQL-2003 synonyms for nchar are national char and national character.

nvarchar [(n | max)]

Variable-length Unicode character data. n values are between 1 and 4,000 (inclusive). Max indicates that the maximum storage size is 2^31-1 bytes. The storage size is twice times + 2 bytes of the number of characters entered. The data entered can be 0 characters in length. The SQL-2003 synonym for nvarchar is national char varying and national character varying.

Note If n is not specified in a data definition or variable declaration statement, the default length is 1. If n is not specified using the CAST function, the default length is 30.

If the size of the column data items may be the same, use nchar.

If the size of the column data items may vary widely, use nvarchar.

sysname is a system-supplied user-defined data type that is functionally identical to nvarchar (128), except that it does not think zero. sysname used to reference database object names

A fixed-length or variable-length character data type.

char [(N)]

Fixed length, non-Unicode character data, length n bytes. The value range of n is 1 to 8,000, and the storage size is n bytes. The SQL 2003 synonym for Char is character.

varchar [(n | max)]

Variable length, non-Unicode character data. The value range of n is 1 to 8,000. Max indicates that the maximum storage size is 2^31-1 bytes. Storage size is the actual length of the input data plus 2 bytes

Variable-length offset array varoffset 2*varcount (plus 2 additional offsets for each column). The data entered can be 0 characters in length. The varchar in SQL-2003 is either char varying or character varying.

Note If n is not specified in a data definition or variable declaration statement, the default length is 1. If n is not specified when using the CAST and CONVERT functions, the default length is 30.

The default collation of the database will be assigned to objects that use char or varchar unless a specific collation is assigned using the COLLATE clause. The collation controls the code page that is used to store character data.

If your site supports multiple languages, consider using Unicode nchar or nvarchar data types to minimize character conversion problems. If you use char or varchar, it is recommended that you do the following:

Use char if the column data items are of the same size.

If the size of the column data items varies considerably, use varchar.

If the column data item size varies greatly and the size may exceed 8,000 bytes, use varchar (max).

I tested the difference between these data types, the following is the test script and results:

Copy Code code as follows:

DECLARE @a CHAR (6)
SET @a= ' You ah Ah yes yes '
PRINT ' char: ' +@a

DECLARE @b CHAR (6)
SET @b= ' ABCDEFG '
PRINT ' char: ' +@b

DECLARE @c CHAR (6)
SET @c= ' 123456 '
PRINT ' char: ' +@c
-----------------------

DECLARE @d NCHAR (6)
SET @d=n ' You ah Ah yes yes '
PRINT ' nchar: ' +@d

DECLARE @e NCHAR (6)
SET @e=n ' ABCDEFG '
PRINT ' nchar: ' +@e

DECLARE @f NCHAR (6)
SET @f=n ' 123456 '
PRINT ' nchar: ' +@f
------------------------------

DECLARE @g VARCHAR (6)
SET @g= ' You ah Ah yes yes '
PRINT ' varchar: ' +@g

DECLARE @h VARCHAR (6)
SET @h= ' ABCDEFG '
PRINT ' varchar: ' +@h

DECLARE @i VARCHAR (6)
SET @i= ' 123456 '
PRINT ' varchar: ' +@i
--------------------------------
DECLARE @j NVARCHAR (6)
SET @j=n ' You ah Ah yes yes '
PRINT ' nvarchar: ' +@j

DECLARE @k NVARCHAR (6)
SET @k=n ' ABCDEFG '
PRINT ' nvarchar: ' +@k

DECLARE @l NVARCHAR (6)
SET @l=n ' 123456 '
PRINT ' nvarchar: ' +@l

Results:

Copy Code code as follows:

Char: you ah Ah
Char:abcdef
char:123456
NCHAR: Oh, yes, you are.
Nchar:abcdef
nchar:123456
VARCHAR: you ah ah
Varchar:abcdef
varchar:123456
Nvarchar: Oh, yes, you are.
Nvarchar:abcdef
nvarchar:123456