1.2 数据类型
Transact-SQL语言和SQL Server数据库中的数据通常需要定义一个数据类型，数据类型定义了对象可以容纳的数据的种类。下面通过深入研究数据类型的存储来揭示数据类型使用中需要注意的技巧。
1.2.1 哪些对象需要数据类型
下面的对象在使用中需要使用数据类型。
1．表和视图的列
在定义表或视图时，其列需要定义数据类型。
2．存储过程的参数
在定义存储过程的Transact-SQL代码中定义参数的数据类型。
3．变量
如果在Transact-SQL中使用变量，需要定义数据类型。
4．带返回值的函数
返回一个或多个特定数据类型数据值的Transact-SQL函数中的返回值需要定义数据类型。
5．带返回代码的存储过程
具有返回代码（始终为integer数据类型）的存储过程。
1.2.2 如何选择恰当的数据类型
Transact-SQL中提供了丰富的数据类型，足够满足功能繁多的系统的需要。实践中经常发现很多DBA或开发人员对数据类型的使用很随意，比如一个表现人员信息的表中，“姓名”字段使用nchar数据类型，长度为60，这里的数据类型可以选择nvarchar变长，对绝大部分数据库系统而言，长度30就足够了。因为ncahr是一个定长的数据类型，对于大部分中国人的姓名仅仅是3到4个汉字（6～8个字符）的存储就足够了，而如果使用定长数据类型，剩余的大量空间都会被Null值填充。比如在表中使用nchar(60)来存储人的姓名，那么大多数情况下，以3或4个汉字人名为例，剩下的52或54个字符都需要Null来填充。
作者推荐，通常情况下在选择数据类型时可以遵循下列原则。
1．必须
首先根据需要判断数据的种类、需要存储的数据的最大长度和大小，对于数字数据类型还有考虑精度和小数位数，然后在Transact-SQL中挑选备用的数据类型。
2．够用
接下来可以在备用的数据类型中挑选够用即可的类型作为最后的方案。比如要表述“身份证号码”这样的字段，nvarchar和nchar这样的数据类型都可以，最后我们可以使用nchar(18)，因为身份证号码规定为统一的18位，所以选择定长的数据类型。
3．权衡
Transact-SQL定义如此多的数据类型，在使用时需要综合进行权衡。权衡存储空间和效率，权衡数据的存储方式等。
通常情况下，数据类型的长度越小，存储大量数据所需要的存储空间就越小。因此一次I/O读取到内存中的数据记录就会越多，同样大小的内存空间存储的记录数量就会更多，这样Transact-SQL语句使用数据时从内存读取的几率就增大了，所以自然效率就高，尤其是联机事务频繁的系统，这样的设计优点就十分明显。
“细节决定成败”，数据类型的选择恰当与否往往会对数据库系统的性能产生非常大的影响。
1.2.3 基本数据类型
通常情况下，管理员或开发人员在使用数据类型时，可以根据表1-1做出初步的选择，然后经过实践最后确定正确的数据类型。
表1-1 Transact-SQL中的基本数据类型
1.2.4 SQL Server 2008新增数据类型
在SQL Server 2008中新增加了很多数据类型。下面简要进行介绍。
1．日期和时间型
新增的日期和时间数据类型如表1-2所示。
表1-2 Transact-SQL中新增的日期和时间数据类型
2．hierarchyid
新增的数据类型，用于创建层次结构的表，或引用位于另一位置的数据层次结构。
1.2.5 深入研究tinyint数据类型
下面我们来深入研究tinyint数据类型的使用。通过建立一个表，表中仅包含一个字段，该字段的数据类型为tinyint，然后向表中录入数据，通过DBCC PAGE命令来查看数据的物理存储，最后得出tinyint数据类型的物理存储空间分配情况。
1．使用范围
tinyint数据类型用于表示0～255范围的整数，采用固定长度，数据本身需要的存储空间为1字节。
2．创建表
首先使用向导默认参数创建一个名为DBTEST的数据库。然后执行下面的语句创建测试表。
USE [DBTEST]
GO
CREATE TABLE [dbo].[Table_DataType_TINYINT](
[F_D_TINYINT] [tinyint] NOT NULL
) ON [PRIMARY]
GO
源代码文件：\代码\0101.sql。
3．录入数据
执行下面的语句向测试表中录入256条数据。
DECLARE @i tinyint
SELECT @i=0
SET NOCOUNT ON
WHILE(@i<=255)
BEGIN
INSERT INTO [DBTEST].[dbo].[Table_DataType_TINYINT]
([F_D_TINYINT])
VALUES (@i);
IF (@i<=254)
SELECT @i=@i+1
ELSE
BREAK;
END
SET NOCOUNT OFF
源代码文件：\代码\0102.sql。
4．查询表的页分布
执行下面的语句查询给表分配了哪些页。
DBCC TRACEON(3604)
GO
DBCC EXTENTINFO(DBTEST, Table_DataType_TINYINT)
GO
源代码文件：\代码\0103.sql。
执行结果如图1-1所示。说明表的数据页分配了1个，页号为155。当然读者在实践过程中分配的页面可能会稍有不同，这需要和实际环境对照进行理解和阅读。
图1-1 表的页分布
5．查询数据页内容
执行下面的语句查询页的内容。
DBCC TRACEON(3604)
GO
DBCC PAGE(DBTEST,1,155,1)
GO
源代码文件：\代码\0104.sql。
查询结果中，行偏移数组的部分内容如下。说明每行记录在页中都使用了9个字节进行存储。
10 (0xa) -186 (0xba)
9 (0x9) -177 (0xb1)
8 (0x8) -168 (0xa8)
7 (0x7) -159 (0x9f)
6 (0x6) -150 (0x96)
5 (0x5) -141 (0x8d)
4 (0x4) -132 (0x84)
3 (0x3) -123 (0x7b)
2 (0x2) -114 (0x72)
1 (0x1) -105 (0x69)
0 (0x0) -96 (0x60)
6．深入分析
tinyint数据类型存储的数据本身尽管仅需要1个字节，但是当作为表列存储时需要9个字节来存储，这是为什么呢？
这9个字节中：
● 1个字节：数据本身。
● 1个字节：由于没有建立索引，表的数据采用堆存储方式，空余量。
● 3个字节：空位图使用，用于表示列的为空性，计算方法为2+(列的总数+7)/8，向上取整数值，这里1个表列，所以计算结果为3。
● 4个字节：用于存储数据行的行标题。
7．结论
tinyint数据类型的列大约需要9个字节来存储。
1.2.6 深入研究smallint数据类型
下面我们来深入研究smallint数据类型的使用。其研究方法同上。
1．使用范围
smallint数据类型用于表示-215(-32,768)～215-1(32767)范围的整数，采用固定长度，数据本身需要的存储空间为2字节。
2．创建表
执行下面的语句创建测试表。
USE [DBTEST]
GO
CREATE TABLE [dbo].[Table_DataType_SMALLINT](
[F_D_SMALLINT] [smallint] NOT NULL
) ON [PRIMARY]
GO
源代码文件：\代码\0105.sql。
3．录入数据
执行下面的语句向测试表中录入256条数据。
DECLARE @i smallint
SELECT @i=0
SET NOCOUNT ON
WHILE(@i<=255)
BEGIN
INSERT INTO [DBTEST].[dbo].[Table_DataType_SMALLINT]
([F_D_SMALLINT])
VALUES (@i);
IF (@i<=254)
SELECT @i=@i+1
ELSE
BREAK;
END
SET NOCOUNT OFF
源代码文件：\代码\0106.sql。
4．查询表的页分布
执行下面的语句查询给表分配了哪些页。
DBCC TRACEON(3604)
GO
DBCC EXTENTINFO(DBTEST, Table_DataType_SMALLINT)
GO
源代码文件：\代码\0107.sql。
执行结果如图1-2所示。说明表的数据页分配了1个，页号为157。
图1-2 表的页分布
5．查询数据页内容
执行下面的语句查询页的内容。
DBCC TRACEON(3604)
GO
DBCC PAGE(DBTEST,1,157,1)
GO
源代码文件：\代码\0108.sql。
查询结果中，行偏移数组的部分内容如下。说明每行记录在页中都使用了9个字节进行存储。
10 (0xa) -186 (0xba)
9 (0x9) -177 (0xb1)
8 (0x8) -168 (0xa8)
7 (0x7) -159 (0x9f)
6 (0x6) -150 (0x96)
5 (0x5) -141 (0x8d)
4 (0x4) -132 (0x84)
3 (0x3) -123 (0x7b)
2 (0x2) -114 (0x72)
1 (0x1) -105 (0x69)
0 (0x0) -96 (0x60)
6．深入分析
smallint数据类型存储的数据本身尽管仅需要1个字节，但是当作为表列存储时需要9个字节来存储，这是为什么呢？
这9个字节中：
● 2个字节：数据本身。
● 3个字节：空位图使用，用于表示列的为空性，计算方法为2+(列的总数+7)/8，向上取整数值，这里1个表列，所以计算结果为3。
● 4个字节：用于存储数据行的行标题。
7．结论
smallint数据类型的列大约需要9个字节来存储。
1.2.7 深入研究int（Integer）数据类型
下面我们来深入研究int数据类型的使用。其研究方法同上。
1．使用范围
int数据类型用于表示-231(-2,147,483,648)～231-1(-2,147,483,647)范围的整数，采用固定长度，数据本身需要的存储空间为4字节。
2．创建表
执行下面的语句创建测试表。
USE [DBTEST]
GO
CREATE TABLE [dbo].[Table_DataType_INT](
[F_D_INT] [int] NOT NULL
) ON [PRIMARY]
GO
源代码文件：\代码\0109.sql。
3．录入数据
执行下面的语句向测试表中录入256条数据。
DECLARE @i int
SELECT @i=0
SET NOCOUNT ON
WHILE(@i<=255)
BEGIN
INSERT INTO [DBTEST].[dbo].[Table_DataType_INT]
([F_D_INT])
VALUES (@i);
IF (@i<=254)
SELECT @i=@i+1
ELSE
BREAK;
END
SET NOCOUNT OFF
源代码文件：\代码\0110.sql。
4．查询表的页分布
执行下面的语句查询给表分配了哪些页。
DBCC TRACEON(3604)
GO
DBCC EXTENTINFO(DBTEST, Table_DataType_INT)
GO
源代码文件：\代码\0111.sql。
执行结果如图1-3所示。说明表的数据页分配了1个，页号为159。
图1-3 表的页分布
5．查询数据页内容
执行下面的语句查询页的内容。
DBCC TRACEON(3604)
GO
DBCC PAGE(DBTEST,1,159,1)
GO
源代码文件：\代码\0112.sql。
查询结果中，行偏移数组的部分内容如下。说明每行记录在页中都使用了11个字节进行存储。
10 (0xa) -206 (0xce)
9 (0x9) -195 (0xc3)
8 (0x8) -184 (0xb8)
7 (0x7) -173 (0xad)
6 (0x6) -162 (0xa2)
5 (0x5) -151 (0x97)
4 (0x4) -140 (0x8c)
3 (0x3) -129 (0x81)
2 (0x2) -118 (0x76)
1 (0x1) -107 (0x6b)
0 (0x0) -96 (0x60)
6．深入分析
int数据类型存储的数据本身尽管仅需要4个字节，但是当作为表列存储时需要11个字节来存储，这是为什么呢？
这11个字节中：
● 4个字节：数据本身。
● 3个字节：空位图使用，用于表示列的为空性，计算方法为2+(列的总数+7)/8，向上取整数值，这里1个表列，所以计算结果为3。
● 4个字节：用于存储数据行的行标题。
7．结论
int数据类型的列大约需要11个字节来存储。
提示：在SQL Server中创建数据对象时，使用int数据类型。在Transact-SQL中可以使用int或integer类型，两者等同。
1.2.8 深入研究bigint数据类型
下面我们来深入研究bigint数据类型的使用。其研究方法同上。
1．使用范围
bigint数据类型用于表示-263(-9,223,372,036,775,808)～263-1(9,223,372,036,775,807)范围的整数，采用固定长度，数据本身需要的存储空间为8字节。
2．创建表
执行下面的语句创建测试表。
USE [DBTEST]
GO
CREATE TABLE [dbo].[Table_DataType_BIGINT](
[F_D_BIGINT] [bigint] NOT NULL
) ON [PRIMARY]
GO
源代码文件：\代码\0113.sql。
3．录入数据
执行下面的语句向测试表中录入256条数据。
DECLARE @i bigint
SELECT @i=0
SET NOCOUNT ON
WHILE(@i<=255)
BEGIN
INSERT INTO [DBTEST].[dbo].[Table_DataType_BIGINT]
([F_D_BIGINT])
VALUES (@i);
IF (@i<=254)
SELECT @i=@i+1
ELSE
BREAK;
END
SET NOCOUNT OFF
源代码文件：\代码\0114.sql。
4．查询表的页分布
执行下面的语句查询给表分配了哪些页。
DBCC TRACEON(3604)
GO
DBCC EXTENTINFO(DBTEST, Table_DataType_BIGINT)
GO
源代码文件：\代码\0115.sql。
执行结果如图1-4所示。说明表的数据页分配了1个，页号为161。
图1-4 表的页分布
5．查询数据页内容
执行下面的语句查询页的内容。
DBCC TRACEON(3604)
GO
DBCC PAGE(DBTEST,1,161,1)
GO
源代码文件：\代码\0116.sql。
查询结果中，行偏移数组的部分内容如下。说明每行记录在页中都使用了15个字节进行存储。
10 (0xa) -246 (0xf6)
9 (0x9) -231 (0xe7)
8 (0x8) -216 (0xd8)
7 (0x7) -201 (0xc9)
6 (0x6) -186 (0xba)
5 (0x5) -171 (0xab)
4 (0x4) -156 (0x9c)
3 (0x3) -141 (0x8d)
2 (0x2) -126 (0x7e)
1 (0x1) -111 (0x6f)
0 (0x0) -96 (0x60)
6．深入分析
bigint数据类型存储的数据本身尽管仅需要8个字节，但是当作为表列存储时需要15个字节来存储，这是为什么呢？
这15个字节中：
● 8个字节：数据本身。
● 3个字节：空位图使用，用于表示列的为空性，计算方法为2+ (列的总数+7) /8，向上取整数值，这里1个表列，所以计算结果为3。
● 4个字节：用于存储数据行的行标题。
7．结论
bigint数据类型的列大约需要15个字节来存储。
1.2.9 decimal（numeric）数据类型的使用
下面我们来研究decimal数据类型的使用，Transact-SQL中，numeric数据类型的使用与其相同。
对于可变精度和长度的数据类型，我们不再将研究重点放在其存储的长度上，因为这样的研究没有更多的实际意义。
1．使用范围
Decimal（numeric）数据类型用于表示长度可达38位的固定精度和范围的数值（-1038～1038-1）。
● P：表示数值的总长度
● S：小数点右边的小数位数或数字个数。
两者的关系如下。
0<=S<=P<=38
2．创建表
执行下面的语句创建测试表。
USE [DBTEST]
GO
CREATE TABLE [dbo].[Table_DataType_DECIMAL](
[F_D_DECIMAL] [decimal](18, 4) NOT NULL
) ON [PRIMARY]
GO
源代码文件：\代码\0117.sql。
3．录入数据
执行下面的语句向测试表中录入256条数据。
DECLARE @i decimal(18,4)
SELECT @i=0.1234
SET NOCOUNT ON
WHILE(@i<=255.1234)
BEGIN
INSERT INTO [DBTEST].[dbo].[Table_DataType_DECIMAL]
([F_D_DECIMAL])
VALUES (@i);
IF (@i<=254.1234)
SELECT @i=@i+1.0000
ELSE
BREAK;
END
SET NOCOUNT OFF
源代码文件：\代码\0118.sql。
4．查询表的页分布
执行下面的语句查询给表分配了哪些页。
DBCC TRACEON(3604)
GO
DBCC EXTENTINFO(DBTEST, Table_DataType_DECIMAL)
GO
源代码文件：\代码\0119.sql。
执行结果如图1-5所示。说明表的数据页分配了1个，页号为163。
图1-5 表的页分布
5．查询数据页内容
执行下面的语句查询页的内容。
DBCC TRACEON(3604)
GO
DBCC PAGE(DBTEST,1,163,1)
GO
源代码文件：\代码\0120.sql。
1.2.10 float数据类型的使用
float数据类型也称为浮点数据类型，用于表示-1.79E+308～-2.23E-308、0以及2.23E-308至1.79E+308之间的浮点数。
float(n)中的n用于指定尾数的位数，n可以取1～53。使用该数据类型时需要注意下面的规则。
1．仅存储近似值
float不是精确数值类型，仅仅存储近似数值。所以在需要精确表示数据的应用中，比如金融行业表述数额时是不能使用这样的数据类型的。应该使用integer、decimal、money这样的精确数值类型。
2．where比较子句中谨慎使用
float仅仅和＞、＜比较符号配合使用。在需要=或＜＞比较时不使用。
3．存储
float数据采用固定存储方法。
● 当n＜=24，采用4字节存储本身数据，加上管理存储空间，这样的列需要大约4+3+4=11字节。
● 当24＜n＜=53，采用8字节存储本身数据，加上管理存储空间，这样的列需要大约8+3+4=15字节。
1.2.11 real数据类型的使用
real数据类型也称为浮点数据类型，用于表示-3.40E+38至-1.18E-38、0以及1.18E-38至3.40E+38之间的浮点数。使用这种数据类型时除了和float类型一样是近似值，where子句中谨慎使用之外，该数据类型采用定长的4字节存储。
仅包含该数据类型的一个字段的表，每列在存储空间上大约需要4+3+4=11字节。
1.2.12 char数据类型的使用
char数据类型为定长的字符型数据类型。取值范围为1～8000，最多4000个汉字，因为一个汉字占据两个字符存储空间。需要注意的使用原则包括。
1．默认长度值
如果没有指定长度，默认长度为1。
在cast或convert函数中，如果没有指定长度值，默认值为30。
2．排序规则
除非特定指定，否则将自动继承数据库的排序规则。
3．使用时机
数据项属于字符型，且长度相同。
1.2.13 varchar数据类型的使用
varchar数据类型为变长的字符型数据类型。需要注意的使用原则如下。
1．长度值
可以指定varchar(n),1＜=n＜=8000。
varchar(max)可以存储231-1个字符。
2．排序规则
除非特定指定，否则将自动继承数据库的排序规则。
3．使用时机
数据项属于字符型，且长度差异大。这种情况下使用定长字符类型将浪费很多存储空间。
如果列的字符型数据相差很大，且可能突破8000的限制，需要采用varchar(max)。
1.2.14 用CAST函数转换数据类型
在Transact-SQL中，经常需要进行数据类型的转换。Transact-SQL内部有时可以自动执行隐式转换，比如一个定义为smallint的变量和一个定义为int的变量比较时，代码的执行也不会报错，这是因为系统自动进行了可以包容的数据类型的自动转换。
很多情况下，还需要使用CAST函数来进行显式转换。
1．CAST函数语法
CAST函数的语法如下。
CAST ( expression AS data_type [ (length ) ])
● Expression：要转换的表达式。
● Data_type：转换后的数据类型。
● Length:nchar、nvarchar、char、varchar、binary或varbinary数据类型的可选参数，设置转换后的长度。
2．CAST函数实例
下面通过一个实例介绍CAST函数的使用方法。
DECLARE @price1 smallint;
DECLARE @price2 int;
SELECT @price1=1;
SELECT @price2=450000;
SELECT CAST(@price1 AS int)+@price2
源代码文件：\代码\0121.sql。
1.2.15 用CONVERT函数转换数据类型
CONVERT函数也可以用于进行数据类型的转换。
1．CONVERT函数语法
CONVERT函数的语法如下。
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
● expression：要转换的表达式。
● data_type：转换后的数据类型。
● length:nchar、nvarchar、char、varchar、binary或varbinary数据类型的可选参数，设置转换后的长度。
● style：数据格式的样式。用于将datetime或smalldatetime数据转换成字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型），或将已知日期或时间格式的字符数据转换成datetime或smalldatetime数据；或者是字符串格式，用于将float、real、money或smallmoney数据转换成字符数据（nchar、nvarchar、char、varchar、nchar或nvarchar数据类型）。如果style为NULL，则返回的结果也为NULL。
2．CONVERT函数实例
下面通过一个实例介绍CONVERT函数的使用方法。
DECLARE @price1 smallint;
DECLARE @price2 int;
SELECT @price1=1;
SELECT @price2=450000;
SELECT CONVERT(int, @price1)+@price2
源代码文件：\代码\0122.sql。