第9章 SQL server 2008列数据类型和属性
数据类型是数据的一种属性，是数据所表示信息的类型。在SQL Server 2008系统中，表中的每一列都有一个与之对应的特定数据类型和列数据属性，例如允许空，或者不允许空。
9.1 数据类型
SQL Server 2008系统提供了36种数据类型，它们每种数据类型的固有特性，我们可以将其分为8大类，下面分别对每一类数据类型的属性和含义展开叙述。 1．数字数据类型
数字数据类型包括：bigint、int、smallint、tinyint、bit、decimal、numeric、money、smallmoney、float、real等11种数据类型。根据数值的精度，数字数据类型又可以分为精确数字类型和近似数字类型两大类。这些数据类型的数字可以用于各种数值运算。它们的具体功能解释如表3-1所示。
表3-1 数字数据类型 数据类型 精 确 类 型 int bigint 63功能解释 该数据类型中存储容量最大的一种，长度是8个字节，每个字节的长度是8位，且可以存储正负数字。bigint数据类型可以存储从-2（-9 223 372 036 854 775 808）到2-1（9 223 372 036 854 775 807）范围的所有正负数。 它的长度是4字节，每个字节长度是8位，且可以存储正负数。以int数据类型存储的每个值占用4个字节，共32位，其中31位用作存储数字，1位用于表示正负。int数据类型的取值范围是-2（-2 147 483 648）到2-1（2 147 483 647）。实际上，int数据类型是最常用的数据类型，只用当int数据类型表示的数据长度不足时，才考虑使用bigint数据类型。 313163smallint 它的长度是2字节，共16位，也可以存储正负数，其中15位用作存储数字，以为用于表示负数。因此，其取值范围是-2（-32 768）到2-1（32 767）。 1515tinyint Bit 该类型数据可以存储从0到255范围内的所有数据。以tinyint数据类型存储的每个值占用1个字节。 可以存储0、1或者Null数据的数据类型。这种数据类型主要用于一些条件逻辑判断。也可以把TRUE和FALSE数据存储到Bit数据类型中，这时需要按照字符格式存储TRUE和FALSE数据。
money 由money数据类型存储的货币值有两个4字节整数构成。前面的一个4字节表示货币值的整数部分，后面的一个4字节表示货币值的小数部分。money数据类型存储货币值的范围从-2（-922 337 203 685 477.5808）到2-1（+922 337 203 685 477.5807），可以精确到万分之一的货币单位。 smallmoney 由smallmoney数据类型存储的货币值由两个2字节整数构成，前面的一个2字节整数表示货币值的整数部分，后面一个2字节整数表示货币值的小数部分。以smallmoney存储的货币值的范围从-214 748.3648到214 748.3647，也可以精确到万分之一的货币单位。 近 似 类 型 float Decimal/numeric decimal数据类型的精度是变化的，因此该数据类型的长度是不定的。decimal数据类型的取值范围是-10+1至10-1。 该类型数据范围为–1.79E＋308~–2.23E–308、0和2.23E–308~1.79E＋308。利用float来声明变量和表中列时，可以指定用来存储按科学计数法记录的数据尾数的bit数。如float（n），n的范围是1~53。当n的取值为1~24时，float类型数据可以达到的精度是7位，用4个字节来储存；当n的取值范围是25~53时，float类型数据可以达到的精度是15位，用8个字节来储存。 real 该数据类型可以存储正的或者负的十进制数值，最大可以有7位精确位数。他的存储范围从-3.40E-38~3.40E+38。每个real类型的数据占用4个字节的存储空间。 383863632．字符数据类型
字符数据类型也是SQL Server中最常用的数据类型之一，用于存储固定长度或可变长度的字符数据。在SQL Server 2008系统中，提供了char类型、varchar类型、text类型、nchar类型、nvarchar类型和ntext类型6种。其中，前3种数据类型是非Unicode字符数据类型，后3种是Unicode字符数据类型。它们的用法和功能解释如表3-2所示。
表3-2 字符数据类型
char 使用char数据类型存储数据时每个字符占用一个字节的存储空间。使用固定长度来存储字符，最长可以容纳8000个字符。使用它定义表中列的数据类型时，应该给定数据的最大长度。如果实际数据的字符长度小于给定的最大长度时，则多余的字节会被空格填充；如果实际数据的字条长度大于给定的最大长度时，则超过的字符将会被截断。在使用字符型常量为字符数据类型赋值时，必须使用双引号或单引号将字符型常量括起来。 varchar 该数据类型的使用方式与char数据类型的使用方式基本相同。SQL Server利用varchar数据类型来存储最长可以达到8000字符的变长字符型数据。与char数据类型不同，varchar数据类型的存储空间，随存储在表列中的每一个数据的字符数的不同而自动调整。
text nchar 该数据类型主要就是用来存储预计超过8000字符的变长字符数据，最大长度可以达到2-1个字符。 与char数据类型类似，不同的是nchar数据类型n的取值为1~4000。nchar数据类型采用Unicode标准字符集，Unicode标准用两个字节为一个存储单位，其一个存储单位的容纳量就大大增加了，可以将全世界的语言文字都包括在内，在一个数据列中就可以同时出现中文、英文、法文等，而不会出现编码冲突。 nvarchar 与varchar数据类型类似，nvarchar数据类型也采用Unicode标准字符集，n的取值范围为1~4000。在SQL Server 2008中，nvarchar还可以定义为nvarchar（max），可以存储数量巨大的变长字符串，数据最大长度可以达到2-1个字符，并且完全兼容所有的SQL Server内置的字符串函数。微软公司建议使用nvarchar（max）替代ntext类型。 ntext 与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。ntext数据类型采用Unicode标准字符集，最大长度可以达到2-1个字符。 3030313．二进制数据类型
二进制数据类型用于存储二进制的数据，包括binary、varbinary和image数据
类型。其中，binary用于存储固定长度的二进制数据，varbinary用于存储可变长度的二进制数据。binary（n）和varbinary（n）的数据长度由n值来决定，n的取值范围是1~8000。image数据类型用于存储图像信息。SQL Server的早期版本使用长二进制数据的image数据类型。在SQL Server 2008系统中，这个数据类型已由varbinary（max）数据类型取代。只有在数据的字节数超过了8KB的情况下，才使用image数据类型，其中max可以达到的最大存储字节数为231-1。
binary（n）和varbinary（n）的默认值是1。如果要存储的二进制数据的大小比较一致，建议使用binary（n）数据类型。如果将要存储的二进制数据之间的大小差别比较大，则应该使用varbinary（n）数据类型。如果将要存储的二进制大于8000字节，就必须使用varbinary（max）数据类型。
image数据类型和varbinary类型相似的原因是他也是一种可变长度的二进制数据类型，区别在于可保存的数据长度不同，image类型可以存储高达2GB的数据；数据保存的物理位置不同，varbinary类型的数据与行中其他列上的数据在物理上是被保存在一起的，而对于image数据类型列的数据与行的其他部分是分开保存的，行上只保存了一个指针，SQL Server通过这个指针找到数据。image类型通常用于保存图像二进制文件和二进制对象。
同使用字符类型的方法一样，如果某个数据的值超过了数据定义时规定的最大长度，则多于的值会被服务器自动截去。如果是使用 binary数据类型，则数据长度不够时服务器会在多于的字节上补充0。
4．日期和时间数据类型
在SQL Server 2008以前的版本中，日期和时间数据类型只有datetime和smalldatetime两种类型，这两种类型的差别在于其表示的日期和时间范围不同，时间精确度不同。
而在SQL Server 2008系统中，日期和时间类型的最大转变就是在datetime和smalldatetime两种类型的基础上又引入了4种日期和时间数据类型，分别为date、time、datetime2和datetimeoffset；此外还增加了新的日期和时间函数功能。这些数据类型的用法和功能解释如表3-3所示。
表3-3 日期和时间数据类型
数据类型 date time 功能解释 该数据类型只存储日期型数据类型，不存储时间数据。date数据类型的取值范围从0001-01-01到9999-12-31。 该数据类型就像日期数据类型一样，如果你只想存储时间数据而不需要存储日期部分就可以利用time数据类型。其取值范围从00:00:00.0000000到23:59:59.9999999。 datetime2 该数据类型也是一种数据时间混合的数据类型，不过其时间部分秒数的小数部分可以保留不同位数的值，比原来的datetime数据类型取值范围要广。用户可以根据自己的需要通过设置不同的参数来设定小数位数，最高可以设到小数点后七位（参数为7），也可以不要小数部分（参数为0），以此类推。 datetimeoffset 该数据类型是由年、月、日、小时、分钟、秒和小数秒组成的时间戳结构。小数秒的最大小数位数为7。该数据类型具有时区偏移量。此偏移量指定时间相对于协调世界时（UTC）偏移的小时和分钟数。系统使用时区偏移量获取本地时间。时区偏移量必须包括符号（加或减）以表示是用UTC加上还是减去偏移量。偏移量的有效小时数介于-14和+14之间。分钟偏移量的符号取决于小时偏移量的符号：如果小时偏移量的符号为负，则分钟偏移量必须为负或零；如果小时偏移量的符号为正，则分钟偏移量必须为正或零；如果小时偏移量的符号为零，则分钟偏移量可以为-0.59到+0.59之间的任何值。 datetime smalldatetime 该数据类型所存储的日期范围是从1753年1月1日开始到9999年12月31日结束，时间精确度是3.33毫秒。 该数据类型所存储的日期范围是1900年1月1日开始到2079年12月31日结束，时间精确度是1分钟。 5．空间数据类型
SQL Server 2008支持用于存储空间数据的平面空间数据类型（geometry）和地理空间数据类型（geography）。这些类型支持用来创建、比较、分析和检索空间数据的方法和属性。两种数据类型都是作为SQL Server中的.NET公共语言运行时（CLR）数据类型实现的。
geometry数据类型支持平面或欧几里得（平面球）数据。geometry数据类型符
合适用于SQL规范的开放地理空间联盟（OGC）简单特征1.1.0版。另外，SQL Server支持geography数据类型，该数据类型可存储诸如GPS纬度和经度坐标之类的椭圆体（圆球）数据。
geometry和geography数据类型支持十一种空间数据对象或实例类型。但是，这些实例类型中只有七种“可实例化”；可以在数据库中创建并使用这些实例（或可对其进行实例化）。这些实例的某些属性由其父级数据类型派生而来，使其在GeometryCollection中区分为Points、LineStrings、Polygons或多个geometry或geography实例。两种空间数据类型的行为经常非常相似，但在数据存储方式和操作方式上存在某些重要的差别。
SQL Server 2008在使用geography数据类型时具有以下限制：
储。
使用开放地理空间联盟（OGC）熟知文本（Well-Known Text, WKT）或熟知二进制
（Well-Known Binary, WKB）表示形式并且会产生大于一个半球的对象的任何geography实例都会引发一个ArgumentException异常。
如果方法的结果不能容纳在单个半球内部，则需要输入两个geography实例的
geography数据类型方法（如STIntersection()、STUnion()、STDifference()和STSymDifference()）将返回Null。如果输出超过单个半球，STBuffer()也将返回Null。
下图3-9描述了geometry和geography数据类型所基于的geometry层次结构。geometry
和geography的可实例化类型以灰色表示。
Geometry每个geography实例必须能够容纳在单个半球的内部。任何大于半球的对象都无法存
PointCurveSurfaceGeomCollectionLineStringPolygonMultiSurfaceMultiCurveMultiPointMultiPolygonMultiLineString 图3-9 geometry类型的层次结构
如图4-5所示，geometry和geography数据类型的7种可实例化类型为Point、MultiPoint、LineString、MultiLineString、Polygon、MultiPolygon和GeometryCollection。只要特定实例的格式正确，即使未显式定义该实例，geometry和geography类型也可识别该实例。例如，用户如果用STPointFromText()方法显式定义了一个Point实例，
只要方法输入的格式正确，geometry和geography便将该实例识别为Point。如果使用STGeomFromText()方法定义了相同的实例，则geometry和geography数据类型都将该实例识别为Point。 6．hierarchyid数据类型
hierarchyid数据类型是SQL Server 2008系统中一种新增的一种长度可变的系统数据类型。该数据类型是系统提供的。使用hierarchyid作为数据类型来创建具有层次结构的表，或引用位于另一个位置的数据层次结构。类型为hierarchyid的列不会自动表示树。由应用程序来生成和分配hierarchyid值，使行与行之间的所需关系反映在这些值中。hierarchyid数据类型的值表示树层次结构中的位置。hierarchyid的值具有以下属性：
非常紧凑
在具有n个节点的树中，表示一个节点所需的平均位数取决于平均端数（节点的平均子级数）。端数较小时(0-7)，大小约为6*logAn位，其中A是平均端数。对于平均端数为6级、包含100,000个人的组织层次结构，一个节点大约占38位。存储时，此值向上舍入为40位，即5个字节。
按深度优先顺序进行比较
给定两个hierarchyid值a和b，a 支持任意插入和删除 使用GetDescendant方法，始终可以在任意给定节点的右侧、左侧或任意两个同级节点之间生成同级节点。在层次结构中插入或删除任意数目的节点时，该比较属性保持不变。大多数插入和删除操作都保留了紧凑性属性。但是，对于在两个节点之间执行的插入操作，所产生的hierarchyid值的表示形式在紧凑性方面将稍微降低。 编码限制 hierarchyid类型中所用的编码限制为892个字节。因此，如果节点的表示形式中包含过多级别，以至于892个字节不足以容纳他，则该节点不能用hierarchyid类型表示。 hierarchyid类型对层次结构树中有关单个节点的信息进行逻辑编码的方法是：对从树的根目录到该节点的路径进行编码。这种路径在逻辑上表示为一个在根之后被访问的所有子级的节点标签序列。表示形式以一条斜杠开头，只访问根的路径由单条斜杠表示。对于根以下的各级，各标签编码为由点分隔的整数序列。子级之间的比较就是按字典顺序比较由点分隔的整数序列。每个级别后面紧跟着一个斜杠。因此斜杠将父级与其子级分隔开。例如，以下是长度分别为1级、2级、和3级的有效hierarchyid路径： / /1/ /1/3/ 可在任何位置插入节点。插入在/1/2/之后、/1/3/之前的节点可表示为/1/2.5/。插入在0之前的节点的逻辑表示形式为一个负数。例如，/1/1/之前的节点可表示为/1/-1/。 7．其他数据类型 除了前面介绍的数据类型之外，SQL Server 2008系统还提供了sql_variant、timestamp、uniqueidentifier、xml等4种特殊用途的数据类型。使用这些数据类型可以完成特殊数据对象的定义、存储和使用。它们的用法和含义解释如表3-4所示。 表3-4 其他数据类型 数据类型 sql_variant 功能解释 sql_variant是一种特殊的数据类型，他可以用来存储SQL Server 2008系统支持的各种数据类型（不包括text、ntext、image、timestamp和sql_variant）的值。类型为sql_variant的列可能包含不同数据类型的行。 sql_variant的最大长度可以是8016个字节。这包括基类型信息和基类型值。实际基类型值的最大长度是8000个字节。对于sql_variant数据类型，必须先将他转换为其基本数据类型值，然后才能参与诸如加减这类运算。可以为sql_variant分配默认值。该数据类型还可以将NULL作为其基础值，但是NULL值没有关联的基类型。而且，sql_variant不能以另一个sql_variant作为他的基类型。惟一键、主键或者外键可能包含类型为sql_variant的列，但是，组成指定行的键的数据值的总长度不应大于索引的最大长度。该最大长度是900个字节。一个表可以包含任意多个sql_variant列。 timestamp timestamp数据类型与时间和日期无关。timestamp值是二进制数值，表明数据库中的数据修改发生的相对顺序。实现timestamp数据类型最初是为了支持SQL Server恢复算法。每次修改页时，都会用当前的@@DBTS值对该页做一次标记，@@DBTS值每次递增1。这足以帮助恢复过程确定页被修改的相对顺序，但是timestamp值与时间没有任何关系。 uniqueidentifier uniqueidentifier是一个特殊的数据类型，他是一个具有16字节的全局惟一性标志符，用来确保对象的惟一性。可以在定义列或者变量时使用该数据类型，这些定义的主要目的是在合并复制和事务复制中确保表中数据行的惟一性。 xml 用来保存整个XML文档。用户可以像使用int数据类型一样使用xml数据类型。另外，xml数据类型还提供一些高级功能，比如借助Xquery语法执行搜索。 8．自定义数据类型 用户自定义数据类型并不是真正的数据类型，他只提供一种加强数据库内部元素和基本数据类型之间一致性的机制。用户基于系统的数据类型而设计并实现的数据类型就称为用户自定义数据类型。 当创建用户定义的数据类型时，必须提供三个参数：数据类型的名称、所基于的系统数据类型和是否允许空。创建用户定义数据类型有两种方法：一种是通过图形化界面创建；另一种是使用系统存储过程sp_addtype创建用户定义数据类型。具体制作过程，这里不再详细赘述。 9.2 列的其他属性 对于一个数据库管理员来说，要设计好字段，还需要考虑更多的问题，例如那些字段是记录中必不可少的、那些字段的内容是不可重复的等等。这就要求掌握列更多的属性，以便实现更复杂的功能。 1．NULL与NOT NULL 列的为空性决定表中的行是否可为该列包含空值。空值（或NULL）不同于零（0）、空白或长度为零的字符串（如\"\"）。出现NULL通常表示值未知或未定义。NOT NULL则是不允许为空值，该列必须输入数据。 如果插入了一行，但没有为允许Null值的列包含任何值，除非存在DEFAULT定义或DEFAULT对象，否则，数据库引擎将提供NULL值。用关键字NULL定义的列也接受用户的NULL显式输入，不论它是何种数据类型或是否有默认值与之关联。NULL值不应放在引号内，否则会被解释为字符串NULL而不是空值。 指定某一列不允许空值（NOT NULL）有助于维护数据的完整性，因为这样可以确保行中的列永远包含数据。如果不允许空值，用户向表中输入数据时必须在列中输入一个值，否则数据库将不接收该表行。 定义了PRIMARY KEY约束或IDENTITY属性的列不允许空值。 2 指定列的默认值 当表中的某个字段在每条记录中的大部分值都保持不变的时候，可以指定列的默认值来简化数据的输入。如果插入行时没有为该列指定值，默认值则指定列中使用什么值。默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。 在SQL Server 2008系统中，指定列的默认值有两种方法：可以在表设计器定义时指定，也可以在使用CREATE TABLE语句及ALTER TABLE语句时使用DEFAULT关键字定义。 使用表设计器指定列的默认值 （1）从【开始】菜单中选择【程序】|Microsoft SQL Server 2008|SQL Server Management Studio命令，打开Microsoft SQL Server Management Studio窗口，并 使用Windows或SQL Server身份验证建立连接。 （2）在【对象资源管理器】窗格中展开服务器，然后展开【数据库】节点。 （3）在【工资管理系统】数据库节点上双击或者单击前面的+号按钮，展开该数据库，然后右击【表】节点，从展开的节点中选择【新建表】命令。 在第2章中已经对数据库的创建方法进行介绍，这里【工资管理系统】数据库的创建方法不在介绍。 （4）在其中输入字段的名称和类型后，展开表设计器下面【列属性】里的【常规】节点，在【默认值或绑定】后面的文本框中输入其默认值即可。这里对【公司福利】表中的“福利工资”设默认值为200。如图4-8所示。 图3-10 表设计器的默认值 使用DEFAULT关键字 可以在使用CREATE TABLE及ALTER TABLE语句时使用DEFAULT关键字指定列的默认值。DEFAULT关键字的语法格式如下： DEFAULT 例如，对【公司福利】表中“福利工资”添加默认值可以使用如下语句： USE 工资管理系统 GO CREATE TABLE 公司福利 ( 福利编号 int IDENTITY, 福利工资 int DEFAULT'200', 部门编号 int NULL, 职位 nvarchar (20) NULL ) 3．IDENTITY的应用 在前面小节中，在【公司福利】表中定义“福利编号”字段的时候，并没有使用NOT NULL关键字，而是使用了IDENTITY关键字，使用IDENTITY关键字定义的字段又叫标识字段，一个标识字段是惟一标识表中每条记录的特殊字段，当一个新记录添加到这个表中时，这个字段就被自动赋给一个新值。默认情况下是按+1递增。 每个表可以有一个标识字段，也只能有一个标识字段。使用tinyint型数据，只能向表中添加255个记录。 在SQL Server 2008系统中，定义标识字段有两种方法：可以使用表设计器定义标识字段，也可以在使用CREATE TABLE语句及ALTER TABLE语句时使用IDENTITY关键字定义。 使用表设计器定义标识字段 （1）从【开始】菜单中选择【程序】|Microsoft SQL Server 2008|SQL Server Management Studio命令，打开Microsoft SQL Server Management Studio窗口，并使用Windows或SQL Server身份验证建立连接。 （2）在【对象资源管理器】窗格中展开服务器，然后展开【数据库】节点。 （3）在【工资管理系统】数据库节点上双击或者单击前面的+号按钮，展开该数据库，然后右击【表】节点，从展开的节点中选择【新建表】命令。 （4）在其中输入字段的名称和类型后，展开表设计器下面【列属性】里的【标识规范】节点，在【是标识】后面的下拉菜单中选择是。标量增量和标量种子按需要更改即可，如图4-9所示。 图3-11 表设计器的标识规范 使用IDENTITY关键字 IDENTITY关键字是指在表中创建一个标识列。此属性与CREATE TABLE及ALTER TABLE语句时一起使用。IDENTITY关键字的语法格式如下： IDENTITY(seed,increment) 语句中，seed表示标量种子，即用于指定标识列的初始值；increment表示标量增量，即用于指定标识列的增量值。必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值（1,1）。 例如，在【工资管理系统】数据库中新设计一个【部门信息】表，要求“部门编号”为标识列，标量种子为1，标量增量为1，具体代码如下： USE 工资管理系统 GO CREATE TABLE 部门信息 ( 部门编号 int IDENTITY(1,1), 部门名称 nvarchar(20) NULL, 员工姓名 int NULL ) 因篇幅问题不能全部显示，请点此查看更多更全内容