SQL Server中的数据类型详解

目录

• 哪些对象需要数据类型
• 一、 整数数据类型
• 1、bit
• 2、tinyint
• 3、smallint
• 4、int （integer）
• 5、bigint
• 二、 浮点数据类型
• 1、real： 近似数值型
• 2、float[(n)]：近似数值型
• 3、decimal[p [s] ]
• 4、numeric[p [s] ]
• 5、smallMoney货币型
• 6、money货币型
• 三、字符数据类型
• 1、char[（n）]
• 2、nchar[（n）]
• 3、varchar[(n| max )]
• 4、nvarchar[(n| max )]
• 5、text
• 6 、ntext
• 四、 日期和时间数据类型
• 1、datetime ：日期和時間（1753-1-1 日到 9999-12-31）
• 2、dateTime2 (n)：高精度的datetime类型（0001-01-01 到 9999-12-31）
• 3、smalldatetime  ：精确到分钟，日期和時間。（1900-01-01 到 2079-06-06）不推荐。
• 4、date：只表示日期：年-月-日（0001-01-01 到 9999-12-31）
• 5、time：只表示时间：hh:mm:ss（00:00:00.0000000 到 23:59:59.9999999）
• 6、datetimeOffset：时间部分增加时区表示
• 五、二进制数据类型
• 1、binary
• 2、varbinary
• 3、image
• 六、特殊数据类型
• 1、 cursor：游标引用
• 2、timestamp：时间戳
• 3、uniqueIdentifier：全局唯一标识符，即GUID
• 4、sql_variant：SQL变量
• 5、table：表
• 6、xml
• 七、SQL Server 2008新增数据类型
• hierarchid
• 八、用户定义的数据类型
• （1）创建用户定义的数据类型
• （2）删除用户定义的数据类型
• 九、参考：

T-SQL语言和SQLServer数据库中的数据通常需要定义一个数据类型，数据类型定义了对象可以容纳的数据的种类。

哪些对象需要数据类型

(1)、表和视图的列:
在定义表或视图时，其列需要定义数据类型。

(2)、存储过程的参数:
在定义存储过程的T-SQL代码中定义参数的数据类型。

(3)、变量:
如果在T-SQL中使用变量，需要定义数据类型。

(4)、带返回值的函数:
返回一个或多个特定类型数据值的T-SQL函数中的返回值需要定义数据类型。

(5)、带返回代码的存储过程:
具有返回代码的存储过程。

一、 整数数据类型

1、bit

bit数据类型是整型，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如Yes 或No、True 或False 、On 或Off.   
注意：很省空间的一种数据类型，如果能够满足需求应该尽量多用。每个TINYINT类型的数据占用1个字节的存储空间。

• bit值保存为1、0或NULL 的整数数据类型。1代表true，0代表false。
• 在插入、修改 bit 数据时，使用 0 或 1。
  SQL Server将字符串值“TRUE”（字符串不分大小写，或者数字字符'1'）转换为1，将“FALSE”（字符串不分大小写，或者数字字符'0'）转换为0。将任何非零值转换为1。
• 在c# 中读取数据库数据时，可以直接用bool型读取该字段，会直接转换为true/false。

2、tinyint

tinyint数据类型存储从0 到255 之间的所有正整数。每个tinyint类型的数据占用1个字节的存储空间。

3、smallint

smallint 数据类型存储从-2的15次方（ -32768） 到2的15次方-1（ 32767 ）之间的所有正负整数。
每个smallint类型的数据占用2个字节的存储空间，其中1 位表示整数值的正负号，其它15 位表示整数值的长度和大小。

4、int （integer）

int （或 integer）数据类型存储从-2的31次方 （-2147483648） 到2的31次方-1 （2147483647） 之间的所有正负整数。
每个INT 类型的数据按4个字节存储，其中1 位表示整数值的正负号，其它31 位表示整数值的长度和大小。

5、bigint

bigint数据类型存储从-2^63 （-9 223372036854775807） 到2^63-1（ 9223372036854775807） 之间的所有正负整数。
每个BIGINT 类型的数据占用8个字节的存储空间。

二、 浮点数据类型

浮点数据类型用于存储十进制小数。浮点数值的数据在SQL Server 中采用上舍入（Round up 或称为只入不舍）方式进行存储。
所谓上舍入是指，当（且仅当）要舍入的数是一个非零数时，对其保留数字部分的最低有效位上的数值加1 ，并进行必要的进位。若一个数是上舍入数，其绝对值不会减少。
如：对3.14159265358979 分别进行2 位和12位舍入，结果为3.15 和3.141592653590。

1、real： 近似数值型

real数据类型可精确到第7 位小数，其范围为从-3.40E -38 到3.40E +38。 每个real类型的数据占用4 个字节的存储空间。

2、float[(n)]：近似数值型

float数据类型可精确到第15 位小数，其范围为从-1.79E -308 到1.79E +308。 每个FLOAT 类型的数据占用8 个字节的存储空间。 
float数据类型可写为FLOAT[ n ]的形式。n 指定float数据的精度。n 为1到15 之间的整数值。当n 取1 到7 时，实际上是定义了一个real类型的数据，系统用4 个字节存储它；
当n 取8 到15 时，系统认为其是float类型，用8 个字节存储它。

3、decimal[p [s] ]

decimal数据类型可以提供小数所需要的实际存储空间，但也有一定的限制，您可以用2 到17 个字节来存储从-10的38次方-1 到10的38次方-1 之间的数值。
可将其写为decimal[ p [s] ]的形式，p 和s 确定了精确的比例和数位。其中p 表示可供存储的值的总位数（不包括小数点），缺省值为18； s 表示小数点后的位数，缺省值为0。
例如：decimal （15 5），表示共有15 位数，其中整数10 位，小数5。

4、numeric[p [s] ]

numeric数据类型与decimal 数据类型完全相同。
注意：SQL Server 为了和前端的开发工具配合，其所支持的数据精度默认最大为28位。但可以通过使用命令来执行sqlserver.exe程序以启动SQL Server，可改变默认精度。

5、smallMoney货币型

smallMoney 数据类型用来表示钱和货币值。这种数据类型能存储从-214748.3648 到214748.3647 之间的数据，精确到货币单位的万分之一。smallmoney 数据类型要求 4 个存储字节。

6、money货币型

money数据类型用来表示钱和货币值。这种数据类型能存储从-9220亿到9220 亿之间的数据，精确到货币单位的万分之一。money数据类型要求 8 个存储字节。

三、字符数据类型

字符数据类型是使用最多的数据类型。它可以用来存储各种字母、数字符号、特殊符号。一般情况下，使用字符类型数据时须在其前后加上单引号’或双引号” 。

1、char[（n）]

char数据类型的定义形式为char[（n）] 。 以char类型存储的每个字符和符号占一个字节的存储空间。n 表示所有字符所占的存储空间，n 的取值为1 到8000， 
即可容纳8000 个ANSI 字符。若不指定n 值，则系统默认值为1。若输入数据的字符数小于n，则系统自动在其后添加空格来填满设定好的空间。若输入的数据过长，将会截掉其超出部分。

如果没有在数据定义或变量声明语句中指定 n，则默认长度为 1如果在使用 CAST 和 CONVERT 函数时未指定 n，则默认长度为 30。

2、nchar[（n）]

nchar数据类型的定义形式为nchar[（n）]。 它与char类型相似。不同的是nchar数据类型n的取值为1 到4000。 因为nchar类型采用UNICODE 标准字符集（CharacterSet）。 
UNICODE 标准规定每个字符占用两个字节的存储空间，所以它比非UNICODE 标准的数据类型多占用一倍的存储空间。使用UNICODE 标准的好处是因其使用两个字节做存储单位，
其一个存储单位的容纳量就大大增加了，可以将全世界的语言文字都囊括在内，在一个数据列中就可以同时出现中文、英文、法文、德文等，而不会出现编码冲突。

3、varchar[(n| max )]

varchar数据类型的定义形式为varchar[（n| max ）]。 它与char类型相似，n 的取值也为1 到8000，若输入的数据过长，将会截掉其超出部分。
不同的是，varchar数据类型具有变动长度的特性，因为varchar数据类型的存储长度为实际数值长度，若输入数据的字符数小于n ，则系统不会在其后添加空格来填满设定好的空间。
一般情况下，由于char数据类型长度固定，因此它比varchar类型的处理速度快。

4、nvarchar[(n| max )]

nvarchar数据类型的定义形式为nvarchar [ ( n | max ) ]。它与varchar类型相似。不同的是，nvarchar数据类型采用UNICODE 标准字符集（Character Set）， n 的取值为1 到4000。

5、text

text数据类型用于存储大量文本数据，其容量理论上为1 到2的31次方-1 （2147483647）个字节，在实际应用时需要视硬盘的存储空间而定。
SQL Server 中，将text和image 类型的数据直接存放到表的数据行中。

6 、ntext

ntext数据类型与text类型相似不同的,是ntext类型采用UNICODE 标准字符集(Character Set), 因此其理论容量为230-1(1073741823)个字节。

四、 日期和时间数据类型

日期和时间数据类型及函数 - SQL Server (Transact-SQL) | Microsoft 官方文档

1、datetime ：日期和時間（1753-1-1 日到 9999-12-31）

datetime 数据类型用于存储日期和时间的结合体。它可以存储从公元1753 年1 月1 日零时起到公元9999 年12 月31 日23 时59 分59 秒之间。精确到三百分之一秒或3.33毫秒

2、dateTime2 (n)：高精度的datetime类型（0001-01-01 到 9999-12-31）

类似于之前的datetime类型，不过其精度比较高，可以精确到小数点后面7位（100ns），其使用语法为：datetime2(n)。
0001-01-01 00:00:00.0000000到9999-12-31 23:59:59:9999999，表示日期和时间

使用示例：

declare @dt as datetime2(5)
set @dt = getdate()
select @dt

3、smalldatetime  ：精确到分钟，日期和時間。（1900-01-01 到 2079-06-06）不推荐。

smalldatetime 数据类型用来表示小范围间的日期和时间，精确到一分钟。

4、date：只表示日期：年-月-日（0001-01-01 到 9999-12-31）

按照年-月-日的格式表示日期

5、time：只表示时间：hh:mm:ss（00:00:00.0000000 到 23:59:59.9999999）

按照hh:mm:ss的格式精确表示时间

6、datetimeOffset：时间部分增加时区表示

加入了时区偏移量部分，时区偏移量表示为 [+|-] HH:MM。  必需的 + 或 - 符号指示在 UTC（通用协调时间或格林尼治标准时间）中是加上还是减去时区偏移量以获取本地时间。

00000-01-01 00:00:00.0000000到99999-12-31 23:59:59:9999999

使用示例：

declare @dt as datetimeoffset(8)
set @dt = '2008-08-08 08:08:08.0 +8:00'
select @dt

五、二进制数据类型

1、binary

binary 数据类型用于存储二进制数据。其定义形式为binary （n）， n 表示数据的长度，取值为1 到8000 。
在使用时必须指定binary 类型数据的大小，至少应为1 个字节。BINARY 类型数据占用n+4 个字节的存储空间。
在输入数据时必须在数据前加上字符“0X” 作为二进制标识，如：要输入“abc ”则应输入“0xabc ”。
若输入的数据过长将会截掉其超出部分。若输入的数据位数为奇数，则会在起始符号“0X ”后添加一个0，如上述的“0xabc ”会被系统自动变为“0x0abc”。

2、varbinary

varbinary 数据类型的定义形式为varbinary （n）。 它与binary 类型相似，n 的取值也为1 到8000，若输入的数据过长，将会截掉其超出部分。
不同的是varbinary 数据类型具有变动长度的特性，因为varbinary 数据类型的存储长度为实际数值长度+4个字节。当binary 数据类型允许NULL 值时，将被视为varbinary 数据类型。
一般情况下，由于BINARY 数据类型长度固定，因此它比varbinary  类型的处理速度快。

3、image

image 数据类型用于存储大量的二进制数据Binary Data。其理论容量为2的31次方-1(2147483647)个字节。其存储数据的模式与text数据类型相同。
通常用来存储图形等OLE Object Linking and Embedding对象连接和嵌入对象。在输入数据时同BINARY数据类型一样，必须在数据前加上字符“0X”作为二进制标识。应用程序可以使用BMP、TIEF、GIF 和 JPEG 格式把数据存储在 image 数据类型中。

六、特殊数据类型

1、 cursor：游标引用

cursor 数据类型是一种特殊的数据类型，它包含一个对游标的引用。这种数据类型用在存储过程中，而且创建表时不能用。

T-SQL游标

2、timestamp：时间戳

timestamp 数据类型是一种特殊的数据类型，用来创建一个数据库范围内的唯一数码。 一个表中只能有一个timestamp列。每次插入或修改一行时，timestamp列的值都会改变。Timestamp 用于表示SQL Server 活动的先后顺序。尽管它的名字中有“time”， 但timestamp列不是人们可识别的日期。在一个数据库里，timestamp值是唯一的  。timestamp的数据类型为rowVersion数据类型的同义词。

3、uniqueIdentifier：全局唯一标识符，即GUID

uniqueIdentifier数据类型用来存储一个全局唯一标识符，即GUID。由 16 字节的十六进制数字组成。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列。

例如：6F9619FF-8B86-D011-B42D-00C04FC964FF,此号码可以通过newid()函数获得，在全世界各地的计算机由此函数产生的数字不会相同。

4、sql_variant：SQL变量

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

5、table：表

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

6、xml

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

SQL Server XML类型

七、SQL Server 2008新增数据类型

hierarchid

新增的数据类型，用于创建层次结构的表，或引用位于另一位置的数据层次结构。

八、用户定义的数据类型

用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型。当几个表中必须存储同一种数据类型时，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。例如，可定义一种称为postal_code 的数据类型，它基于 Char 数据类型。
当创建用户定义的数据类型时，必须提供三个数：数据类型的名称、所基于的系统数据类型和数据类型的可空性。

图形化操作：【可编程性】-->【类型】节点，右击【用户定义数据类型】节点，在弹出的框中，按照说明相应操作即可。

（1）创建用户定义的数据类型

创建用户定义的数据类型可以使用 Transact-SQL 语句。系统存储过程 sp_addtype 可以来创建用户定义的数据类型。其语法形式如下：

sp_addtype {type},[,system_data_bype][,'null_type']

其中，type 是用户定义的数据类型的名称。system_data_type 是系统提供的数据类型，例如 Decimal、Int、Char 　　等等。 null_type 表示该数据类型是如何处理空值的，必须使用单引号引起来，例如'NULL'、'NOT NULL'或者'NONULL'。
例子：创建一个用户定义的数据类型 ssn，其基于的系统数据类型是变长为11 的字符，不允许空。

Use cust
Exec sp_addtype ssn,'Varchar(11)',"Not Null'

例子：创建一个用户定义的数据类型 birthday，其基于的系统数据类型是 DateTime，允许空。

Use cust
Exec sp_addtype birthday,datetime,'Null'

例子：创建两个数据类型，即 telephone 和 fax

Use master
Exec sp_addtype telephone,'varchar(24),'Not Null'
Eexc sp_addtype fax,'varchar(24)','Null'

（2）删除用户定义的数据类型

当用户定义的数据类型不需要时，可删除。删除用户定义的数据类型的命令是

sp_droptype {'type'}

例子：

Use master
Exec sp_droptype 'ssn'

注意：当表中的列还正在使用用户定义的数据类型时，或者在其上面还绑定有默认或者规则时，这种用户定义的数据类型不能删除。

九、参考：

SQL Server与CLR数据类型的对应关系

到此这篇关于SQL Server数据类型的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持我们。