SQL Server 2022的数据类型

新书速览|SQL Server 2022从入门到精通：视频教学超值版_sql server 2022 出版社-CSDN博客

《SQL Server 2022从入门到精通（视频教学超值版）（数据库技术丛书）》(王英英)【摘要 书评 试读】- 京东图书 (jd.com)

数据类型是一种属性，用于指定对象可保存的数据的类型，SQL Server 2022支持多种数据类型，包括字符类型、数值类型以及日期时间类型等。数据类型相当于一个容器，容器的大小决定了装的东西的多少，将数据分为不同的类型可以节省磁盘空间和资源。

SQL Server还能自动限制每个数据类型的取值范围，例如定义了一个数据类型为int的字段，如果插入数据时插入的值的大小在smallint或者tinyint范围内，SQL Server会自动将类型转换为smallint或tinyint，这样一来，在存储数据时，占用的存储空间只有int数据类型的1/2或者1/4。

SQL Server数据库管理系统中的数据类型可以分为两类，分别是系统默认的数据类型和用户自定义的数据类型。下面分别介绍这两大类数据类型。

1. 系统默认的数据类型

SQL Server 2022提供的系统数据类型有以下九大类，共25种。SQL Server会自动限制每个系统数据类型的值的范围，当插入数据库中的值超过了数据类型允许的范围时，SQL Server就会报错。

1）整数数据类型

整数数据类型是常用的数据类型之一，主要用于存储数值，可以直接进行数据运算而不必使用函数转换。

（1）bigint

每个bigint存储在8字节中，其中一个二进制位表示符号，其他63个二进制位表示长度和大小，可以表示−263~263−1范围内的所有整数。

（2）int

int或者integer，每个int存储在4字节中，其中一个二进制位表示符号，其他31个二进制位表示长度和大小，可以表示−231~231−1范围内的所有整数。

（3）smallint

每个smallint类型的数据占用了2字节的存储空间，其中一个二进制位表示整数值的正负号，其他15个二进制位表示长度和大小，可以表示−215~215−1范围内的所有整数。

（4）tinyint

每个tinyint类型的数据占用了1字节的存储空间，可以表示0~255范围内的所有整数。

2）浮点数据类型

浮点数据类型用于存储十进制小数，表示浮点数值数据的大致数值数据类型。浮点数据为近似值；浮点数据类型的数据在SQL Server中采用只入不舍的方式进行存储，即当且仅当要舍入的数是一个非零数时，对其保留数字部分的最低有效位上的数值加1，并进行必要的进位。

（1）real

real可以存储正的或者负的十进制数值，它的存储范围为−3.40E+38~−1.18E−38、0以及1.18E−38~3.40E + 38。每个real类型的数据占用4字节的存储空间。

（2）float [( n )]

在float [( n )]中，n用于存储float数值尾数的位数（以科学记数法表示），因此可以确定精度和存储大小。如果指定了n，则它必须是介于1和53之间的某个值。n的默认值为53。

其取值范围为−1.79E+308~−2.23E−308、0以及2.23E−308~1.79E+308。如果不指定数据类型float的长度，则它占用8字节的存储空间。float数据类型可以写成float(n)的形式，n指定float数据的精度，n为1~53的整数值。当n取1~24时，实际上是定义了一个real类型的数据，系统用4字节存储它；当n取25~53时，系统认为其是float类型，用8字节存储它。

（3）decimal[ (p[ , s] )]和numeric[ (p[ , s] )]

decimal[ (p[ , s] )]和numeric[ (p[ , s] )]是带固定精度和小数位数的数值数据类型。使用最大精度时，有效值取值范围为−1038+1~1038−1。numeric在功能上等价于decimal。

1. p（精度）指定了最多可以存储的十进制数字的总位数，包括小数点左边和右边的位数。该精度必须是1和最大精度38之间的值。默认精度为18。
2. s（小数位数）指定小数点右边可以存储的十进制数字的最大位数。小数位数必须是0和p之间的值。仅在指定精度后才可以指定小数位数。由于默认的小数位数为0，因此0≤s≤p。最大存储大小基于精度而变化。例如，decimal(10,5)表示共有10位数，其中整数5位，小数5位。

3）字符数据类型

字符数据类型也是SQL Server中最常用的数据类型之一，用来存储各种字母、数字符号和特殊符号。在使用字符数据类型时，需要在其前后加上英文单引号或者双引号。

（1）char(n)

当用char数据类型存储数据时，每个字符和符号占用1字节的存储空间。n表示所有字符所占的存储空间，n的取值为1~8000。若不指定n值，则系统默认n的值为1。若输入数据的字符串长度小于n，则系统自动在其后添加空格来填满设定好的空间；若输入的数据过长，则会截掉其超出部分。

（2）varchar(n|max)

在varchar(n|max)中，n为存储字符的最大长度，其取值范围为1~8000，但可根据实际存储的字符数改变存储空间，max表示最大存储大小是231−1字节。存储大小是输入数据的实际长度加2字节。所输入数据的长度可以为0个字符。例如varchar(20)，则对应的变量最多只能存储20个字符，不够20个字符时按实际大小存储。

（3）nchar(n)

nchar(n)用于存储n个字符的固定长度的Unicode字符数据。n值必须在1和4000之间（含），如果没有在数据定义或变量声明语句中指定n，则默认长度为1。此数据类型采用Unicode标准字符集，因此每一个存储单位占2字节，可将全世界文字囊括在内。

（4）nvarchar(n|max)

与varchar相似，nvarchar用于存储可变长度Unicode字符数据。n值必须在1和4000之间（含），如果没有在数据定义或变量声明语句中指定n，则默认长度为1。max指示最大存储大小为231−1字节。存储大小是所输入字符个数的两倍加2字节。所输入数据的长度可以为0个字符。

4）日期和时间数据类型

（1）date

date用于存储用字符串表示的日期数据，可以表示0001-01-01到9999-12-31（公元元年1月1日到公元9999年12月31日）之间的任意日期值。其数据格式为YYYY-MM-DD。

1. YYYY：表示年份的四位数字，其取值范围为0001~9999。
2. MM：表示指定年份中的月份的两位数字，其取值范围为01~12。
3. DD：表示指定月份中的某一天的两位数字，其取值范围为01~31（最高值取决于具体月份）。

该类型数据占用3字节的空间。

（2）time

time用于以字符串形式记录一天中的某个时间，其取值范围为00:00:00.0000000~ 23:59:59.9999999，数据格式为hh:mm:ss[.nnnnnnn]。

1. hh：表示小时的两位数字，其取值范围为0~23。
2. mm：表示分钟的两位数字，其取值范围为0~59。
3. ss：表示秒的两位数字，其取值范围为0~59。
4. n*是0到7位数字，其取值范围为0~9999999，表示秒的小数部分。

time值在存储时占用5字节的空间。

（3）datetime

datetime用于存储时间和日期数据，从1753年1月1日到9999年12月31日，默认值为1900-01-01 00:00:00，当插入数据或在其他地方使用时，需用单引号或双引号引起来，可以使用“/”“-”和“.”作为分隔符。该类型数据占用8字节的空间。

（4）datetime2

datetime2是datetime类型的扩展，其取值范围更大，默认的小数精度更高，并具有可选的用户定义的精度。默认格式是YYYY-MM-DD hh:mm:ss[.fractional seconds]，日期存取范围是0001-01-01~9999-12-31（公元元年1月1日到公元9999年12月31日）。

（5）smalldatetime

smalldatetime类型与datetime类型相似，只是其存取的范围是从1900年1月1日到2079年6月6日，当日期时间值精度较小时，可以使用smalldatetime，该类型数据占用4字节的空间。

（6）datetimeoffset

datetimeoffset用于定义一个日期，该日期采用24小时制的一天时间相组合，并可识别时区，默认格式是YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]：

1. hh：两位数，其取值范围为−14~+14。
2. mm：两位数，其取值范围为00~59。

这里hh是时区偏移量，该类型数据中保存的是世界标准时间（Coordinated UniverSal Time，UTC）值。例如，要存储北京时间2011年11月11日12点整，存储时该值将是2011-11-11 12:00:00+08:00，因为北京处于东八区，比UTC早8个小时。存储该类型数据时默认占用10字节大小的固定存储空间。

5）文本和图形数据类型

（1）text

text用于存储文本数据，服务器代码页中长度可变的非Unicode数据，最大长度为231−1       （2 147 483 647）个字节。当服务器代码页使用双字节字符时，容量仍是2 147 483 647字节。

（2）ntext

ntext类型与text类型的作用相同，为长度可变的Unicode数据，其最大长度为230−1           （1 073 741 823）个字符，存储大小是所输入字符个数的两倍（以字节为单位）。

（3）image

image用于存储长度可变的二进制数据，长度范围为0~231−1字节，用于存储照片、目录图片或者图画，容量也是2 147 483 647字节，由系统根据数据的长度自动分配空间。存储该字段的数据一般不能使用INSERT语句直接输入。

    在Microsoft SQL Server的未来版本中，将删除text、ntext和image 数据类型。尽量避免在新的开发工作中使用这些数据类型，并考虑修改当前使用这些数据类型的应用程序。这些数据类型可改用nvarchar(max)、varchar(max)和varbinary(max)。

6）货币数据类型

（1）money

money用于存储货币值，其取值范围在正负922 337 213 685 477.580 8之间。在money数据类型中，整数部分包含19个数字，小数部分包含4位数字，因此money数据类型的精度是19，存储时占用8字节存储空间。

（2）smallmoney

smallmoney类型与money类型相似，其取值范围在正负214 748.346 8之间，smallmoney存储时占用4字节存储空间。输入数据时，在前面加上一个货币符号，如人民币为¥或其他定义的货币符号。

7）位数据类型

bit称为位数据类型，值只取0或1，长度为1字节。bit值经常当作逻辑值用于判断TRUE（1）和FALSE（0），输入非零值时系统将其换为1。

8）二进制数据类型

（1）binary(n)

binary(n)用于存储长度为n字节的固定长度的二进制数据，其中n的取值范围为1~8000。binary(n)的存储大小为n字节。在输入binary值时，必须在前面带0x，可以使用0~9和A~F表示二进制值，例如输入0xAA5代表AA5，如果输入数据长度大于定义的长度，则超出的部分会被截断。

（2）varbinary(n|max)

varbinary(n|max)用于存储可变长度的二进制数据。n的取值范围为1~8000。max指示最大存储大小为231-1字节。存储大小为所输入数据的实际长度+2字节。

在定义的范围内，不论输入的时间长度是多少，binary类型的数据都占用相同的存储空间，即定义时空间；而对于varbinary类型的数据，在存储时根据实际值的长度使用存储空间。

9）其他数据类型

（1）rowversion

每个数据库都有一个计数器，当对数据库中包含rowversion列的表执行插入或更新操作时，该计数器值就会增加。此计数器是数据库行版本。一个表只能有一个rowversion列。每次修改或插入包含rowversion列的行时，就会在rowversion列中插入经过增量的数据库行版本值。

rowversion是公开数据库中自动生成的唯一二进制数字的数据类型。rowversion通常用作给表行加版本戳的机制，存储大小为8字节。rowversion数据类型只是递增的数字，不保留日期或时间。

（2）timestamp

timestamp是时间戳数据类型，timestamp是rowversion的同义词，提供数据库范围内的唯一值，用于反映数据修改的相对顺序，是一个单调上升的计数器，此列的值被自动更新。

在CREATE TABLE或ALTER TABLE语句中，不必为timestamp数据类型指定列名，例如：

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);

此时，SQL Server数据库引擎将生成timestamp列名，但rowversion不具有这样的行为。在使用rowversion时必须指定列名，例如：

CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion) ;

    微软将在后续版本的SQL Server中删除timestamp语法的功能。因此，在新的开发工作中应该避免使用该功能，并修改当前还在使用该功能的应用程序。

（3）uniqueidentifier

16字节GUID（Globally Unique Identifier，全球唯一标识符）是SQL Server根据网络适配器地址和主机CPU时钟产生的唯一号码，其中，每个位都是0~9或a~f范围内的十六进制数字。例如6F9619FF-8B86-D011-B42D-00C04FC964FF，此号码可以通过调用newid()函数获得，全世界各地的计算机经由此函数产生的数字不会相同。

（4）cursor

cursor是游标数据类型，该类型类似于数据表，其保存的数据中包含行和列值，但是没有索引，游标用来建立一个数据的数据集，每次处理一行数据。

（5）sql_variant

sql_variant用于存储除文本、图形数据和timestamp数据外的其他任何合法的SQL Server数据，可以方便SQL Server的开发工作。

（6）table

table用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。

（7）xml

xml用于存储XML数据，可以在列中或者XML类型的变量中存储XML实例，存储的XML数据类型实例大小不能超过2 GB。

2. 用户自定义的数据类型

SQL Server允许用户自定义数据类型，用户自定义数据类型是建立在SQL Server系统数据类型基础上的，自定义的数据类型使得数据库开发人员能够根据需要定义符合自己开发需求的数据类型。自定义数据类型虽然使用比较方便，但是需要大量的性能开销，所以使用时要谨慎。当用户定义一种数据类型时，需要指定该类型的名称、所基于的系统数据类型以及是否允许为空等。SQL Server为用户提供了两种方法来创建自定义数据类型。下面将分别介绍这两种定义数据类型的方法。

1）使用对象资源管理器创建用户定义数据类型

首先连接到SQL Server服务器，自定义数据类型与具体的数据库相关，因此在对象资源管理器中创建新数据类型之前，需要选择要创建的数据类型所在的数据库，这里按照第2章介绍的创建数据库的方法，创建一个名称为test的数据库，使用系统默认的参数即可。

创建用户自定义数据类型的具体操作步骤如下：

   创建成功之后，依次打开【test】→【可编程性】→【类型】节点，右击【用户定义数据类型】节点，在弹出的快捷菜单中选择【新建用户定义数据类型】菜单命令，如图3-1所示。

  

打开【新建用户定义数据类型】窗口，在【名称】文本框中输入需要定义的数据类型的名称，这里输入新数据类型的名称为address，表示存储一个地址数据值，在【数据类型】下拉列表框中选择char系统数据类型，【长度】指定为8000，如果用户希望该类型的字段值为空的话，可以选择【允许NULL值】复选框，其他参数不做更改，如图3-2所示。

图3-2  【新建用户定义数据类型】窗口

 

   单击【确认】按钮，完成用户定义数据类型的创建，即可看到新创建的自定义数据类型，如图3-3所示。

2）使用存储过程创建用户定义数据类型

除使用图形界面创建自定义数据类型外，SQL Server 2022中的系统存储过程sp_addtype也可以为用户提供使用Transact-SQL语句创建自定义数据类型的方法，其语法形式如下：

sp_addtype [@typename=] type,

[@phystype=] system_data_type

[, [@nulltype=] 'null_type']

其中，各参数的含义如下。

1. type：用于指定用户定义的数据类型的名称。
2. system_data_type：用于指定相应的系统提供的数据类型的名称及定义。注意，未能使用timestamp数据类型，当所使用的系统数据类型有额外说明时，需要用引号将其引起来。
3. null_type：用于指定用户自定义的数据类型的null属性，其值可以为null、not null或nonull。用户自定义的数据类型的名称在数据库中应该是唯一的。

【例3.1】自定义一个地址HomeAddress数据类型，输入语句如下：

sp_addtype HomeAddress,'varchar(128)','not null'

新建一个使用当前连接进行的查询，在打开的查询编辑器中输入上面的语句，输入完成之后单击【执行】按钮，即可完成用户定义数据类型的创建。执行完成之后，刷新【用户定义数据类型】节点，将会看到新增的数据类型，如图3-4所示。

删除用户自定义数据类型的方法也有两种。第一种是在对象资源管理器中右击想要删除的数据类型，在弹出的快捷菜单中选择【删除】菜单命令，如图3-5所示。打开【删除对象】窗口，单击【确定】按钮即可，如图3-6所示。

         

另一种方法就是使用系统存储过程sp_droptype来删除，语法格式如下：

sp_droptype type

type为用户定义的数据类型，例如这里删除address，Transact-SQL语句如下：

sp_droptype address