SQL字符类型介绍

SQL字符类型介绍

一，名词定义
char为 定长字符型，如char(10)，实际数据是‘hello’，这时Sql  Server   会自动补充空格，填满10位。varchar：可变长字符，没有上述问题，是多长就保存多长的数据。另外, char  和 varchar 在存储数据时,每个英文字符和数字都占一个字节,而一个中文的字符会占两个字节.

nchar 与 nvarchar 的用法基本相同,只不过在存储数据时采用的是 Unicode元码, 一个英文字符和一个中文字符所占的空间是相同的.都是两个字节.

二、名词解释

1、当使用非unicode时慎用以下这种查询：
select f from t where f = N’xx’
原因：无法利用到索引，因为数据库会将f先转换到unicode再和N’xx’比较

2、char 和相同长度的varchar处理速度差不多（后面还有说明）

3、varchar的长度不会影响处理速度（看后面解释）

4、索引中列总长度最多支持总为900字节，所以长度大于900的varchar、char和大于450的nvarchar,nchar将无法创建索引

5、text、ntext上是无法创建索引的

6、O/R Mapping中对应实体的属性类型一般是以string居多，用char[]的非常少，所以如果按mapping的合理性来说，可变长度的类型更加吻合

7、一般基础资料表中的name在实际查询中基本上全部是使用like ‘%xx%’这种方式，而这种方式是无法利用索引的，所以如果对于此种字段，索引建了也白建

8、其它一些像remark的字段则是根本不需要查询的，所以不需要索引

9、varchar的存放和string是一样原理的，即length {block}这种方式，所以varchar的长度和它实际占用空间是无关的

10、对于固定长度的字段，是需要额外空间来存放NULL标识的，所以如果一个char字段中出现非常多的NULL，那么很不幸，你的占用空间比没有NULL的大，这时候，你可以使用特殊标识来存放，如：’NV’

11、同上，所以对于这种NULL查询，索引是无法生效的，假如你使用了NULL标识替代的话，那么恭喜你，你可以利用到索引了

12、char和varchar的比较成本是一样的，现在关键就看它们的索引查找的成本了，因为查找策略都一样，因此应该比较谁占用空间小。在存放相同数量的字符情况下，如果数量小，那么char占用长度是小于varchar的，但如果数量稍大，则varchar完全可能小于char，而且要看实际填充数值的充实度，比如说varchar(3)和char(3)，那么理论上应该是char快了，但如果是char(10)和varchar(10)，充实度只有30%的情况下，理论上就应该是varchar快了。因为varchar需要额外空间存放块长度，所以只要length(1-fillfactor)大于这个存放空间（好像是2字节)，那么它就会比相同长度的char快了。

13、nvarchar比varchar要慢上一些，而且对于非unicode字符它会占用双倍的空间，那么这么一种类型推出来是为什么呢？对，就是为了国际化，对于unicode类型的数据，排序规则对它们是不起作用的，而非unicode字符在处理不同语言的数据时，必须指定排序规则才能正常工作，所以n类型就这么一点好处。

三、名词总结
1、如果数据量非常大，又能100%确定长度且保存只是ansi字符，那么char
2、能确定长度又不一定是ansi字符或者，那么用nchar；
3、不确定长度，要查询且希望利用索引的话，用nvarchar类型吧，将它们设到400；
4、不查询的话没什么好说的，用nvarchar(4000)
5、性格豪爽的可以只用3和4，偶尔用用1，毕竟这是一种额外说明，等于告诉别人说，我一定需要长度为X位的数据

 

浅谈SQL设计中的外键
 
 
外键的作用：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！

例如：

a b 两个表

a表中存有客户号，客户名称

b表中存有每个客户的订单

有了外键后

你只能在确信b 表中没有客户x的订单后，才可以在a表中删除客户x

建立外键的前提： 本表的列必须与外键类型相同(外键必须是外表主键)。

指定主键关键字： foreign key(列名)

引用外键关键字： references <外键表名>(外键列名)

事件触发限制: on delete和on update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值）,set Default（设默认值）,[默认]no action

例如：

outTable表 主键 id 类型 int

创建含有外键的表：

create table temp(id int,name char(20),foreign key(id) references outTable(id) on delete cascade on update cascade);

说明：把id列 设为外键 参照外表outTable的id列 当外键的值删除 本表中对应的列筛除 当外键的值改变 本表中对应的列值改变。

外键的作用:

外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。

FOREIGN KEY 约束的主要目的是控制存储在外键表中的数据，但它还可以控制对主键表中数据的修改。例如，如果在 publishers 表中删除一个出版商，而这个出版商的 ID 在 titles 表中记录书的信息时使用了，则这两个表之间关联的完整性将被破坏，titles 表中该出版商的书籍因为与 publishers 表中的数据没有链接而变得孤立了。FOREIGN KEY 约束防止这种情况的发生。如果主键表中数据的更改使之与外键表中数据的链接失效，则这种更改是不能实现的，从而确保了引用完整性。如果试图删除主键表中的行或更改主键值，而该主键值与另一个表的 FOREIGN KEY 约束值相关，则该操作不可实现。若要成功更改或删除 FOREIGN KEY 约束的行，可以先在外键表中删除外键数据或更改外键数据，然后将外键链接到不同的主键数据上去。

外键是用来控制数据库中数据的数据完整性的

就是当你对一个表的数据进行操作

和他有关联的一个或更多表的数据能够同时发生改变
 
 

例如有两个表

A(a,b) :a为主键，b为外键（来自于B.b)

B(b,c,d) :b为主键   

如果我把字段b的外键属性去掉，对编程没什么影响。

如上面，A中的b要么为空，要么是在B的b中存在的值，有外键的时候，数据库会自动帮你检查A的b是否在B的b中存在。

1、外建表达的是参照完整性：这是数据固有的，与程序无关。因此，应该交给DBMS来做。

2、使用外建，简单直观，可以直接在数据模型中体现，无论是设计、维护等会有很大的好处，特别是对于分析现有的数据库的好处时非常明显的--前不久我分析了一个企业现有的数据库，里面的参照完整性约束有的是外键描述，有的是用触发器实现，感觉很明显。当然，文档里可能有，但是也可能不全，但是外键就非常明显和直观。

3、既然我们可以用触发器或程序完成的这个工作（指参照完整性约束），DBMS已经提供了手段，为什么我们要自己去做？而且我们做的应该说没有RDBMS做得好。实际上，早期的RDBMS并没有外键，现在都有了，我认为数据库厂商增加这个功能是有道理的。从这个角度来说，外键更方便。

4、关于方便，根据我带项目的情况来看，程序员确实有反映，主要是在调试时输入数据麻烦：如果数据可以违反参照完整性，那么就是说参照完整性本身就不对名誉业务冲突，此时也不应该用触发期货程序实现；否则，说明数据是错误的，根本就不应该进入数据库！而且，这也应该是测试系统的一个内容：阻止非法数据。实际上，前台程序应该对这种提交失败做出处理。数据是企业的而非程序的，储程序要尽量与数据分离，反之亦然。

最后说一下,建键几个原则:

1、 为关联字段创建外键。

2、 所有的键都必须唯一。

3、避免使用复合键。

4、外键总是关联唯一的键字段。

 

 

SQL Server 2005 数据类型归纳为：

1.二进制数据类型包括 binary、varbinary和image。
　　binary数据类型既可是固定长度的(binary)，也可是变长度的。binary[(N)]是n位固定的二进制数据。其中n取值范围从1到 8000。其存储大小是n+4个byte。此类型存储-9223372036854775808~9223372036854775807的整数，占用 8byte存储空间。
　　varbinary[(N)]是n位变长度的二进制数据。其中n的取值范围是从1到8000。其存储窨的大小是n+4个字节，不是n个字节。此数据类型存储可变长的二进制数据，可在创建时指定其具体长度，也可不指定。
　　image 数据类型中存储的数据是以位字符串存储的，不是由 SQL Server解释的，必须由应用程序来解释。例如应用程序可以使用BMP、TIEF、GIF和JPEG格式把数据存储在Image数据类型中。

2.字符数据类型包括char、varchar和text。
    字符数据是由任何字母、符号和数字任意组合而成的数据。
　　varchar是变长字符数据，其长度不超过 8KB。此数据类型可存储最大值为8000个字符的可变长字符串。可变长字符串的最大长度在创建时指定，如varchar(50)，每个字符占用1byte存储空间。
　　char是定长字符数据，其长度最多为 8KB。此数据类型可存储1~8000个定长字符串，字符串长度在创建时指定；如未指定默认为char(1)。每个字符占用1byte存储空间。
　　text数据类型可存储最大值为2147483647个字符的变长文本，并且无需指定其初始值，每个字符占用1byte存储空间，一般用来存储大段的文章。 text数据类型实际上是一个Large Object数据类型，默认情况下，此类型的数据不是存储在数据行内，而是存储于独立的Large Object数据页上。另外，text数据类型不能做为函数、存储过程或触发器中的参数来用。

3.Unicode 数据类型 nchar、nvarchar和ntext。
    在SQL Server中，传统的非Unicode数据类型允许使用由特定字符集定义的字符。在SQL Server安装过程中，允许选择一种字符集。使用Unicode数据类型，列中可以存储任何由Unicode标准定义的字符。使用Unicode数据类型是使用非Unicode数据类型所占用大小的两倍。在 Unicode 标准中包括了以各种字符集定义的全部字符。
　　nchar数据类型可存储1~4000个定长Unicode字符串，字符串长度在创建时指定；如未指定，默认为nchar(1)。每个字符占用2bytes存储空间。
　　nvarchar数据类型可存储最大值为4000个字符可变长Unicode字符串。可变长Unicode字符串的最大长度在创建时指定，如nvarchar(50)，每个字符占用2bytes存储空间。
　　ntext同text数据类型，只是存储的最大值为1073741823个字符的Unicode变长文本，每个字符占用1byte存储空间。
　　说明：无论使用哪种字符串数据类型，字符串值必须放在引号内，推荐使用单引号。

4.日期和时间数据类型包括 datetime 和 smalldatetime 两种类型。
    日期和时间数据类型由有效的日期和时间组成。
例如，有效的日期和时间数据包括4/01/98 12:15:00:00:00 PM和1:28:29:15:01 AM 8/17/98。
　　datetime数据类型是日期在前，时间在后。此数据类型包括datetime所存储的日期范围是从1753年1月1日开始，到9999年12月31日结束(每一个值要求8个存储字节)。
　　smalldatetime数据类型是瞬时在前，日期在后。此数据类型所存储的日期范围是1900年1月1日开始，到2079年12月31日结束(每一个值要求4个存储字节)。

5.数字数据类型包括数字int、smalint、tinyint、decimal、numeric、real和float数据类型，包括正数和负数、小数（浮点数）和整数 。
　　整数由正整数和负整数组成。在SQL Server 中整数存储数据类型是int，smallint和 tinyint。
　　int数据类型存储数据的范围大于smallint数据类型。存储数据的范围从-2147483648到2147483647（每一个值要求4个字节存储空间）。
　　smallint数据类型存储数据的范围从-3276832 767（每一个值要求2个字节存储空间）。
　　tinyint数据类型存储数据的范围是从0 到255（每一个值要求1个字节存储空间）。
　　精确小数据中的数据类型是 decimal和numeric。这种数据所占的存储空间根据该数据的位数后的位数来确定。这两个数据类型功能相同，均为存储精度可变的浮点值。但推荐采用decimal，因其存储的数据“更有说明性”。此种数据类型由两个值来确定 decimal(p,s)，p为精度，s为标量，如decimal(3,2)，其中数值2为小数的位数，那么decimal(3,2)可用来存储如 1.28这样的浮点数。此种数据类型占用的存储空间取决于精度值p。p为1~9，占用5bytes存储空间；p为10~19，占用9bytes存储空间；p为20~28，占用13bytes存储空间；p为29~38，占用17bytes存储空间。
　　近似小数数据的数据类型是 float 和 real。
　  real数据类型存储-3.40E+38~-1.18E-38的负数和1.18E~3.40E+38的正数。占用4bytes存储空间。
　　float数据类型存储 1~53的可变精度的浮点值，精度表示为float(n)，n表示科学记数法的尾数。其存储空间由精度值决定，n为1~24，占用4bytes存储空间；n为25~53，占用8bytes存储空间。取值范围为-1.79E+308~-2.23E-308的负数和 2.23E-308~1.79E+308的正数。例如，三分之一这个分数记作。3333333，当使用近似数据类型时能准确表示。因此，从系统中检索到的数据可能与存储在该列中数据不完全一样。

6.货币数据类型是money和smallmoney。此数据表示正的或者负的货币数量。
　　money数据类型要8bytes存储空间，存储-922337203685477.5808~922337203685477.5807的货币值，精确到小数后4位。
　　smallmoney数据类型4bytes存储空间，存储-214748.3648~214748.3647的货币值，精确到小数后4位。

7.特殊数据类型包括前面没有提过的数据类型。
    即rowversion 、 timestamp、bit 和 uniqueidentifier。
　　rowversion、timestamp 用于表示SQL Server 活动的先后顺序，以二进投影的格式表示。这两种数据类型功能一样，但Microsoft建议在任何情况下尽可能地指明rowversion而不是 timestamp，因为rowversion更加准确地反应了数据类型的真实性质。timestamp 数据与插入数据或者日期和时间没有关系。它表明数据库中的数据修改发生的相对顺序。不要在键（尤其是主键）中使用 timestamp列，因为每次修改行时，timestamp值都会更改。当数据类型为rowversion或timestamp，那么SQL Server会在对表的插入或删除等更新操作时自动生成一个新值，并把这个新值放在合适的字段里。此类型数据占用8bytes存储空间。
　　bit由1或者0组成。当表示真或者假，使用 Bit 数据类型。例如，询问是否是每一次访问的客户机请求可以存储在这种数据类型的列中。
　　uniqueidentifier由16字节的十六进制数字组成，表示一个全局唯一的。当表的记录行要求唯一时，GUID是非常有用。例如，在客户标识号列使用这种数据类型可以区别不同的客户。