SQLServer2008数据类型
在创建表时，必须为表中的每列指派⼀种数据类型。本节将介绍SQL Server中最常⽤的⼀些数据类型。即使创建⾃定义数据类型，它也必须基于⼀种标准的SQL Server数据类型。例如，可以使⽤如下语法创建⼀种⾃定义数据类型(Address)，但要注意，它基于SQL Server标准的varchar数据类型。
CREATE TYPE Address
FROM varchar(35) NOT NULL
如果在SQL Server Management Studio的表设计界⾯中更改⼀个⼤型表中某列的数据类型，那么该操作可能需要很长时间。可以通过在Management Studio界⾯中脚本化这种改变来观察其原因。Management Studio再创建⼀个临时表，采⽤像tmpTableName这样的名称，然后将数据复制到该表中。最后，界⾯删除旧表并⽤新的数据类型重命名新表。当然，此过程中还涉及其他⼀些⽤于处理表中索引和其他任何关系的步骤。
如果有⼀个包含数百万条记录的⼤型表，则该过程可能需要花费⼗分钟，有时可能是数⼩时。为避免这种情况，可在查询窗⼝中使⽤简单的单⾏T-SQL语句来更改该列的数据类型。例如，要将Employees表中JobTitle列的数据类型改为varchar(70)，可以使⽤如下语法。
ALTER TABLE HumanResources.Employee ALTER COLUMN JobTitle Varchar(70)
说明：
在转换为与当前数据不兼容的数据类型时，可能丢失重要数据。例如，如果要将包含⼀些数据(如15.415)的numeric数据类型转换为integer数据类型，那么15.415这个数据将四舍五⼊为整数。
您可能想对SQL Server表编写⼀个报表，显⽰表中每列的数据类型。完成这项任务的⽅法有很多种，但最常见的⼀种⽅法是连接sys.objects表和lumns 表。在下⾯的代码中，有两个函数可能不太为您所熟悉。函数TYPE_NAME()将数据类型id转换为适当的名称。要进⾏反向操作，可使⽤TYPE_ID()函数。需要注意的另⼀个函数是SCHEMA_ID()，它⽤于返回架构的标识值。在需要编写有关SQL Server元数据的报表时，这是特别有⽤的。
SELECT o.name AS ObjectName,
c.name AS ColumnName,
TYPE_NAME(c.ur_type_id) as DataType
FROM sys.objects o lumns c
ON o.object_id = c.object_id
WHERE o.name ='Department'
and o.Schema_ID = SCHEMA_ID('HumanResources')
该代码返回如下结果(注意，Name是⼀种⽤户定义的数据类型)。
ObjectName ColumnName DataType
---------------------------------------------------
Department DepartmentID smallint
Department Name
Name
Department GroupName Name
Department ModifiedDate
datetime
1. 字符数据类型
4k电视是什么意思字符数据类型包括varchar、char、nvarchar、nchar、text以及ntext。这些数据类型⽤于存储字符数据。varchar和char类型的主要区别是数据填充。如果有⼀表列名为FirstName且数据类型为varchar(20)，同时将值Brian存储到该列中，则物理上只存储5个字节。但如果在数据类型为char(20)的列中存储相同的值，将使⽤全部20个字节。SQL将插⼊拖尾空格来填满20个字符。
如果要节省空间，那么为什么还使⽤char数据类型呢？使⽤varchar数据类型会稍增加⼀些系统开销。例如，如果要存储两字母形式的州名缩写，则最好使⽤char(2)列。尽管有些DBA认为应最⼤可能地节省空间，但⼀般来说，好的做法是在组织中找到⼀个合适的阈值，并指定低于该值的采⽤char数据类型，反之则采⽤varchar数据类型。通常的原则是，任何⼩于或等于5个字节的列应存储为char数据类型，⽽不是varchar数据类型。如果超过这个长度，使⽤varchar数据类型的好处将超过其额外开销。
nvarchar数据类型和nchar数据类型的⼯作⽅式与对等的varchar数据类型和char数据类型相同，但这两种数据类型可以处理国际性的Unicode字符。它们需要⼀些额外开销。以Unicode形式存储的数据为⼀个字符占两个字节。如果要将值Brian存储到nvarchar列，它将使⽤10个字节；⽽如果将它存储为nchar(20)，则需要使⽤40字节。由于这些额外开销和增加的空间，应该避免使⽤Unicode列，除⾮确
实有需要使⽤它们的业务或语⾔需求。
接下来要提的数据类型是text和ntext。text数据类型⽤于在数据页内外存储⼤型字符数据。应尽可能少地使⽤这两种数据类型，因为可能影响性能但可在单⾏的列中存储多达2GB的数据。与text数据类型相⽐，更好的选择是使⽤varchar(max)类型，因为将获得更好的性能。另外，text和ntext数据类型在SQL Server的⼀些未来版本中将不可⽤，因此现在开始还是最好使⽤varchar(max)和nvarchar(max)⽽不是text和ntext数据类型。
表1-1列出了这些类型，对其作了简单描述，并说明了要求的存储空间。
表1-1
数据类型描述存储空间
Char(n)N为1~8000字符之间n字节
Nchar(n)N为1~4000 Unicode字符之间(2n字节)＋2字
节额外开销
Ntext最多为230–1
婴儿大便酸臭
(1 073 741 823)Unicode字符
每字符2字节
Nvarchar(max)最多为230–1
(1 073 741 823)Unicode字符2×字符数＋2字节额外开销
Text最多为231–1
(2 147 483 647)字符
每字符1字节
Varchar(n)N为1~8000字符之间每字符1字节＋2
字节额外开销
Varchar(max)最多为231–1(2 147 483 647)字符每字符1字节＋2
赞美小草的诗句字节额外开销
2. 精确数值数据类型
数值数据类型包括bit、tinyint、smallint、int、bigint、numeric、decimal、money、float以及real。这些数据类型都⽤于存储不同类型的数字值。第⼀种数据类型bit只存储0或1，在⼤多数应⽤程序中被转换为true或fal。bit数据类型⾮常适合⽤于开关标记，且它只占据⼀个字节空间。其他常见的数值数据类型如表1-2所⽰。
表1-2
数据类型描述存储空间
bit0、1或Null1字节(8位)
血栓吃什么食物好
tinyint0～255之间的整数1字节
nba球星排名
smallint–32 768～32 767之间的整数2字节
int–2 147 483 648～
2 147 483 647之间的整数
4字节
bigint–9 223 372 036 854 775 808～
9 223 372 036 854 775 807
之间的整数
8字节
之间的整数
(续表)
数据类型描述存储空间
numeric(p,s)或
decimal(p,s)
–1 038＋1～1 038–1之间的数值最多17字节
money–922 337 203 685 477.580 8～
922 337 203 685 477.580 7
8字节
smallmoney–214 748.3648～2 14 748.36474字节
SQL Server 2008数据类型（2）
如decimal和numeric等数值数据类型可存储⼩数点右边或左边的变长位数。Scale是⼩数点右边的位数。精度(Precision)定义了总位数，包括⼩数点右边的位数。例如，由于14.88531可为numeric(7,5)或decimal(7,5)。如果将14.25插⼊到numeric(5,1)列中，它将被舍⼊为14.3。
3. 近似数值数据类型
这个分类中包括数据类型float和real。它们⽤于表⽰浮点数据。但是，由于它们是近似的，因此不能精确地表⽰所有值。
float(n)中的n是⽤于存储该数尾数(mantissa)的位数。SQL Server对此只使⽤两个值。如果指定位于1~
24之间，SQL就使⽤24。如果指定25~53之间，SQL就使⽤53。当指定float()时(括号中为空)，默认为53。
表1-3列出了近似数值数据类型，对其进⾏简单描述，并说明了要求的存储空间。
表1-3
数据类型描述存储空间
float[(n)]–1.79E＋308~–2.23E–
308,0,2.23E–308~1.79E＋308N< =24－4字节N> 24－8字节
real()–3.40E＋38~–1.18E–
马桶堵住了怎么办38,0,1.18E–38~3.40E＋38
4字节
注意：
real的同义词为float(24)。
4. ⼆进制数据类型
如varbinary、binary、varbinary(max)或image等⼆进制数据类型⽤于存储⼆进制数据，如图形⽂件、Word⽂档或MP3⽂件。其值为⼗六进制的0x0~0xf。image数据类型可在数据页外部存储最多2GB的⽂件。image数据类型的⾸选替代数据类型是varbinary(max)，可保存最多8KB的⼆进制数据，其性能通常⽐image数据类型好。SQL Server 2008的新功能是可以在操作系统⽂件中通过FileStream存储选项存储varbinary(max)对象。这个选项将数据存储为⽂件，同时不受varbinary(max)的2GB⼤⼩的限制。
表1-4列出了⼆进制数据类型，对其作了简单描述，并说明了要求的存储空间。
表1-4
数据类型描述存储空间
Binary(n)N为1~8000⼗六进制数字之间n字节
Image最多为231–1
(2 147 483 647)⼗六进制数位
每字符1字节
Varbinary(n)N为1~8000⼗六进制数字之间每字符1字节
＋2字节额外开销
Varbinary(max)最多为231–1
(2 147 483 647)⼗六进制数字每字符1字节
＋2字节额外开销
5. ⽇期和时间数据类型
datetime和smalldatetime数据类型⽤于存储⽇期和时间数据。smalldatetime为4字节，存储1900年1⽉1⽇~2079年6⽉6⽇之间的时间，且只精确到最近的分钟。datetime数据类型为8字节，存储1753年1⽉1⽇~9999年12⽉31⽇之间的时间，且精确到最近的3.33毫秒。
SQL Server 2008有4种与⽇期相关的新数据类型：datetime2、dateofft、date和time。通过SQL Server联机丛书可找到使⽤这些数据类型的⽰例。datetime2数据类型是datetime数据类型的扩展，有
着更⼴的⽇期范围。时间总是⽤时、分钟、秒形式来存储。可以定义末尾带有可变参数的datetime2数据类型--如datetime2(3)。这个表达式中的3表⽰存储时秒的⼩数精度为3位，或0.999。有效值为0~9之间，默认值为3。
datetimeofft数据类型和datetime2数据类型⼀样，带有时区偏移量。该时区偏移量最⼤为＋/-14⼩时，包含了UTC偏移量，因此可以合理化不同时区捕捉的时间。
date数据类型只存储⽇期，这是⼀直需要的⼀个功能。⽽time数据类型只存储时间。它也⽀持time(n)声明，因此可以控制⼩数秒的粒度。与datetime2和datetimeofft⼀样，n可为0~7之间。
表1-5列出了⽇期/时间数据类型，对其进⾏简单描述，并说明了要求的存储空间。
表1-5
传真怎么用
数据类型描述存储空间
Date9999年1⽉1⽇~12⽉31⽇3字节
Datetime1753年1⽉1⽇~9999年12⽉31⽇，
精确到最近的3.33毫秒
8字节
Datetime2(n)9999年1⽉1⽇~12⽉31⽇
0~7之间的N指定⼩数秒
6~8字节
Datetimeofft(n)9999年1⽉1⽇~12⽉31⽇
0~7之间的N指定⼩数秒＋/–偏移量
8~10字节
SmalldateTime1900年1⽉1⽇~2079年
6⽉6⽇，精确到1分钟
4字节
Time(n)⼩时：分钟：秒.9999999
0~7之间的N指定⼩数秒
3~5字节
6. 其他系统数据类型
还有⼀些之前未见过的数据类型。表1-6列出了这些数据类型。
表1-6
数据类型描述存储空间
Cursor包含⼀个对光标的引⽤和
可以只⽤作变量或存储过程参数
不适⽤
Hierarchyid包含⼀个对层次结构中位置的引⽤1~892字节＋2
Hierarchyid包含⼀个对层次结构中位置的引⽤1~892字节＋2
字节的额外开销SQL_Variant可能包含任何系统数据类
型的值，除了text、ntext、
image、timestamp、xml、
varchar(max)、nvarchar(max)、
varbinary (max)、sql_variant以
及⽤户定义的数据类型。最⼤尺
⼨为8000字节数据＋16字节
(或元数据)
8016字节
Table⽤于存储⽤于进⼀步处理的数
据集。定义类似于Create Table。
主要⽤于返回表值函数的结果集，
它们也可⽤于存储过程和批处理中取决于表定
义和存储的⾏数
Timestamp or Rowversion 对于每个表来说是唯⼀的、⾃
动存储的值。通常⽤于版本戳，
该值在插⼊和每次更新时⾃动改变
8字节
Uniqueidentifier可以包含全局唯⼀标识符
(Globally Unique Identifier，
GUID)。guid值可以从Newid()
函数获得。这个函数返回的值对篮球三秒区
所有计算机来说是唯⼀的。
尽管存储为16位的⼆进制值，
但它显⽰为char(36)
16字节
XML可以以Unicode或⾮Unicode形式存储最多2GB
注意：
cursor数据类型可能不⽤于Create Table语句中。
hierarchyid列是SQL Server 2008中新出现的。您可能希望将这种数据类型的列添加到这样的表中--其表⾏中的数据可⽤层次结构表⽰，就像组织层次结构或经理/雇员层次结构⼀样。存储在该列中的值是⾏在层次结构中的路径。层次结构中的级别显⽰为斜杠。斜杠间的值是这个成员在⾏中的数字级别，如/1/3。可以运⽤⼀些与这种数据类型⼀起使⽤的特殊函数。
XML数据存储XML⽂档或⽚段。根据⽂档中使⽤UTF-16或是UTF-8，它在尺⼨上像text或ntext⼀样存储。XML数据类型使⽤特殊构造体进⾏搜索和索引。第15章将更详细地介绍这些内容。
7. CLR集成
在SQL Server 2008中，还可使⽤公共语⾔运⾏库(Common Language Runtime，CLR)创建⾃⼰的数据类型和存储过程。这让⽤户可以使⽤Visual Basic或C#编写更复杂的数据类型，以满⾜业务需求。这些类型被定义为基本的CLR语⾔中的类结构。第8章将更详细地介绍其管理部分的内容。
