图片 1

char varchar nchar nvarchar 四者的区别是什么(为何SQL Server自动给字符串末尾加空格)

复制代码 代码如下: create table tb(id
int, value varchar(10)) insert into tb values(1, ‘aa’) insert into tb
values(1, ‘bb’) insert into tb values(2, ‘aaa’) insert into tb values(2,
‘bbb’) insert into tb values(2, ‘ccc’) go create function
[dbo].[f_str](@id int) returns nvarchar(1000) as begin declare @str
nvarchar(1000) set @str = ” select @str = @str + ‘,’ + cast(value as
nvarchar(900)) from tb where id = @id set @str = right(@str , len(@str)

本着低碳的原则将几个变量声明为nchar,结果发现尾巴上每次都多一大串空格,C#中不得不多次Trim劳心费神易出错。上网一查原来四种字符串看似相近其实讲究很多,其中以本帖最为全面,特此转发。

SQL Server char nchar  varchar nvarchar的区别

 

今天在论坛里看到有人激烈讨论这几个数据类型的区别跟实际使用情况,很多人都搞不清楚究竟哪个场景使用哪个数据类型

现在就摘录一下sql2005联机丛书里的关于这几个数据类型的解释

 

字符数据类型(nchar 长度固定,nvarchar 长度可变)和 Unicode 数据使用
UNICODE UCS-2 字符集。

nchar [ ( n ) ]

n 个字符的固定长度的 Unicode 字符数据。n 值必须在 1 到 4,000
之间(含),n指的是字符的长度。存储大小为两倍 n
字节,存储大小为8000字节,单位是字节,固定字节大小的前提下(8000字节)所存储的字符个数由排序规则/字符集而定,。nchar
的 SQL-2003 同义词为 national char 和 national character。

由于存储的是Unicode数据,所以字符数不等于字节数,可能多个字节只能存储一个字符

 

nvarchar [ ( n | max ) ]

可变长度 Unicode 字符数据。n 值在 1 到 4,000 之间(含),n指的是字符的长度。max
指示最大存储大小为 2^31-1 字节。存储大小是所输入字符个数的两倍 + 2
个字节 8002字节,单位是字节,固定字节大小的前提下(8000字节)所存储的字符个数由排序规则/字符集而定。所输入数据的长度可以为
0 个字符。nvarchar 的 SQL-2003 同义词为 national char varying 和
national character varying。

由于存储的是Unicode数据,所以字符数不等于字节数,可能多个字节只能存储一个字符**

备注 如果没有在数据定义或变量声明语句中指定 n,则默认长度为
1。如果没有使用 CAST 函数指定 n,则默认长度为 30。

如果列数据项的大小可能相同,请使用 nchar。

如果列数据项的大小可能差异很大,请使用 nvarchar。

sysname 是系统提供的用户定义数据类型,除了不以为零外,在功能上与
nvarchar(128) 相同。sysname 用于引用数据库对象名

 

 

固定长度或可变长度的字符数据类型。

char [ ( n ) ]

固定长度,非 Unicode 字符数据,长度为 n 个字节。n 的取值范围为 1 至
8,000,存储大小是 n 个字节 ,由于只能存储ASCII数据,所以字符数等于字节数。char
的 SQL 2003 同义词为 character。

varchar [ ( n | max ) ]

可变长度,非 Unicode 字符数据。n 的取值范围为 1 至 8,000。max
指示最大存储大小是 2^31-1 个字节。存储大小是输入数据的实际长度加 2
个字节

n 的取值范围为 1 至 8,000,存储大小是 n
个字节 ,由于只能存储ASCII数据,所以字符数等于字节数

可变长度的偏移阵列  VarOffset  
2*VarCount(每一列都要另外加上2个自己的偏移量)。所输入数据的长度可以为 0
个字符。SQL-2003 中的 varchar 就是 char varying 或 character varying。

备注 如果未在数据定义或变量声明语句中指定 n,则默认长度为 1。如果在使用
CAST 和 CONVERT 函数时未指定 n,则默认长度为 30。

将为使用 char 或 varchar 的对象指派数据库的默认排序规则,除非使用
COLLATE
子句指派了特定的排序规则。该排序规则控制用于存储字符数据的代码页。

如果站点支持多语言,请考虑使用 Unicode nchar 或 nvarchar
数据类型,以最大限度地消除字符转换问题。如果使用 char 或
varchar,建议执行以下操作:

如果列数据项的大小一致,则使用 char。

如果列数据项的大小差异相当大,则使用 varchar。

如果列数据项大小相差很大,而且大小可能超过 8,000 字节,请使用
varchar(max)。

本人测试了一下这几个数据类型的区别,以下是测试脚本跟结果:

 1 DECLARE @a CHAR(6)
 2 SET @a='您啊啊啊啊是'
 3 PRINT 'char:'+@a
 4 
 5 DECLARE @b CHAR(6)
 6 SET @b='abcdefg'
 7 PRINT 'char:'+@b
 8 
 9 DECLARE @c CHAR(6)
10 SET @c='123456'
11 PRINT 'char:'+@c
12 -----------------------
13 
14 DECLARE @d NCHAR(6)
15 SET @d=N'您啊啊啊啊是'
16 PRINT 'nchar:'+@d
17 
18 DECLARE @e NCHAR(6)
19 SET @e=N'abcdefg'
20 PRINT 'nchar:'+@e
21 
22 DECLARE @f NCHAR(6)
23 SET @f=N'123456'
24 PRINT 'nchar:'+@f
25 ------------------------------
26 
27 DECLARE @g VARCHAR(6)
28 SET @g='您啊啊啊啊是'
29 PRINT 'varchar:'+@g
30 
31 DECLARE @h VARCHAR(6)
32 SET @h='abcdefg'
33 PRINT 'varchar:'+@h
34 
35 DECLARE @i VARCHAR(6)
36 SET @i='123456'
37 PRINT 'varchar:'+@i
38 --------------------------------
39 DECLARE @j NVARCHAR(6)
40 SET @j=N'您啊啊啊啊是'
41 PRINT 'nvarchar:'+@j
42 
43 DECLARE @k NVARCHAR(6)
44 SET @k=N'abcdefg'
45 PRINT 'nvarchar:'+@k
46 
47 DECLARE @l NVARCHAR(6)
48 SET @l=N'123456'
49 PRINT 'nvarchar:'+@l

 

结果:

char:您啊啊
char:abcdef
char:123456
nchar:您啊啊啊啊是
nchar:abcdef
nchar:123456
varchar:您啊啊
varchar:abcdef
varchar:123456
nvarchar:您啊啊啊啊是
nvarchar:abcdef
nvarchar:123456

 

最后说一下,NCHAR 
NVARCHAR都要在字符前面加N,避免出现乱码!!

 

nvarchar
、nchar、char、varchar实际占用存储空间比较

nvarchar [ ( n | max )
]:unicode格式编码保存字符,所以每个字符占用两个字节,加上2个字节的行偏移
最大4000

nchar [ ( n ) ]:            
unicode格式编码保存字符,所以每个字符占用两个字节  最大4000

char [ ( n ) ]:               是多少就是多少 最大8000

varchar [ ( n | max ) ]:  加上2个字节的行偏移 最大8000

 

就是说大家的实际占用存储空间都是8000字节的前提下

nvarchar  
 每个字段都加上2个字节的行偏移,而且每个字符占用两个字节,即使用不到两个字节都占用两个字节

nchar         每个字符占用两个字节,即使用不到两个字节都占用两个字节

varchar      每个字段都加上2个字节的行偏移

char          每个字符占用一个字节

 

 

SQL
Server不能像Oracle那样分别设置数据库字符集和国家字符集,而是通过设置排序规则指定数据库字符集和国家字符集

SQL
Server的排序规则规定了数据库字符集的代码页(国家字符集统一使用unicode编码,不需要另外设置)
Chinese_PRC_CI_AS:基于中文排序,不区分大小写,区分重音字符

中文Windows环境下,服务器默认排序规则为Chinese_PRC_CI_AS

CREATE DATABASE testcol COLLATE Latin1_General_CI_AS

USE testcol
CREATE TABLE tt2 (id INT,
a CHAR(1),  iso_1
b VARCHAR(1), iso_1
c NCHAR(1),  UNICODE
d NVARCHAR(1))  UNICODE

USE [testcol]
SELECT [TABLE_NAME],[COLUMN_NAME],[COLLATION_NAME],[CHARACTER_SET_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME]='tt2' AND [TABLE_CATALOG]='testcol'


---------------------------------------------------------------------------------------------------------

CREATE DATABASE test COLLATE Chinese_PRC_CI_AS
CREATE TABLE tt9(
id INT ,
a CHAR(6),   cp936
b NCHAR(6),   UNICODE
c VARCHAR(6),  cp936
d NVARCHAR(7))   UNICODE


USE [test]
SELECT [TABLE_NAME],[COLUMN_NAME],[COLLATION_NAME],[CHARACTER_SET_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_NAME]='tt9' AND [TABLE_CATALOG]='test'

 

char和varchar用的是cp936,nchar和nvarchar用的是UNICODE,cp936是Windows系统对GBK1.0实现

图片 1

 

  • 1) return @str end go –调用函数 select id , value = dbo.f_str(id)
    from tb group by id 运行结果:
    本来在上面的函数中所有的nvarchar都是varchar类型的,并且上面函数的红色处在调用cast方法时,并未指定长度。朋友测试后发现,结果会在30个字符
    时截断,原来以为是varchar和nvarchar的区别,我试着将varchar改成了nvarchar,朋友测试的结果是在54个字符处截断。我查了下,是varchar的默认长度问题,见sql
    server联机丛书中下面的说明: char 和 varchar 固定长度 (char) 或可变长度
    (varchar) 字符数据类型。 char[(n)] 长度为 n 个字节的固定长度且非
    Unicode 的字符数据。n 必须是一个介于 1 和 8,000 之间的数值。存储大小为 n
    个字节。char 在 SQL-92 中的同义词为 character。 varchar[(n)] 长度为 n
    个字节的可变长度且非 Unicode 的字符数据。n 必须是一个介于 1 和 8,000
    之间的数值。存储大小为输入数据的字节的实际长度,而不是 n
    个字节。所输入的数据字符长度可以为零。varchar 在 SQL-92 中的同义词为
    char varying 或 character varying。 注释
    如果没有在数据定义或变量声明语句中指定 n,则默认长度为 1。如果没有使用
    CAST 函数指定 n,则默认长度为 30。 将为使用 char 或 varchar
    的对象被指派数据库的默认排序规则,除非用 COLLATE
    子句另外指派了特定的排序规则。该排序规则控制用于存储字符数据的代码页。
    支持多语言的站点应考虑使用 Unicode nchar 或 nvarchar
    数据类型以尽量减少字符转换问题。如果使用 char 或 varchar:
    如果希望列中的数据值大小接近一致,请使用 char。
    如果希望列中的数据值大小显著不同,请使用 varchar。 如果执行 CREATE TABLE
    或 ALTER TABLE 时 SET ANSI_PADDING 为 OFF,则一个定义为 NULL 的 char
    列将被作为 varchar 处理。
    当排序规则代码页使用双字节字符时,存储大小仍然为 n
    个字节。根据字符串的不同,n 个字节的存储大小可能小于 n 个字符。

原帖:

 

原帖很长排版有点散(从里到外都很欠“Trim”一下),看下面的整理吧:

总结:

   1、varchar:可变长度的非Unicode   数据,最长为8,000个字符。   

   2、nvarchar:可变长度Unicode 数据,其最大长度为4,000字符。   

   3、char:固定长度的非Unicode字符数据,最大长度为 8,000 个字符。 

   4、nchar:固定长度的 Unicode 数据,最大长度为 4,000 个字符。 

   5、char和varchar都是字符串类型的,用Unicode编码的字符串,结果是字符的整数值

nchar(n)

包含n个字符的固定长度Unicode字符数据。n的值必须介于1与4,000之间。存储大小为n字节的两倍。

nchar在SQL-92中的同义词为national char和national character。

nvarchar(n)

包含n个字符的可变长度Unicode字符数据。

n的值必须介于1与4,000之间。字节的存储大小是所输入字符个数的两倍。所输入的数据字符长度可以为零。

nvarchar 在 SQL-92 中的同义词为 national char varying 和 national
character varying。

注释 如果没有在数据定义或变量声明语句中指定 n,则默认长度为
1。如果没有使用 CAST 函数指定 n,则默认长度为 30。