Transact-SQL 语法元素之数据类型

君子举世无双

已于 2023-02-21 16:35:24 修改

阅读量273

点赞数

分类专栏： Transact-SQL 文章标签： sql 数据库 mysql

于 2023-02-21 15:14:45 首次发布

本文链接：https://blog.csdn.net/m0_67621290/article/details/129141317

版权

Transact-SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

文章详细介绍了Transact-SQL中不同数据类型的使用，包括表列、存储过程参数、变量等需要定义数据类型的情况。强调了选择数据类型时应考虑存储需求、效率和权衡。特别探讨了tinyint、smallint、int、bigint、decimal、float、real、char、varchar等数据类型的使用范围、存储占用，并提供了CAST和CONVERT函数在数据转换中的应用。文章还讨论了数据类型选择对数据库性能的影响。

摘要由CSDN通过智能技术生成

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

1.2.1 哪些对象需要数据类型

下面的对象在使用中需要使用数据类型。

1．表和视图的列

在定义表或视图时，其列需要定义数据类型。

2．存储过程的参数

在定义存储过程的Transact-SQL代码中定义参数的数据类型。

3．变量

如果在Transact-SQL中使用变量，需要定义数据类型。

4．带返回值的函数

返回一个或多个特定数据类型数据值的Transact-SQL函数中的返回值需要定义数据类型。

5．带返回代码的存储过程

具有返回代码（始终为integer数据类型）的存储过程。

1.2.2 如何选择恰当的数据类型

通常情况下在选择数据类型时可以遵循下列原则。

1．必须

首先根据需要判断数据的种类、需要存储的数据的最大长度和大小，对于数字数据类型还有考虑精度和小数位数，然后在Transact-SQL中挑选备用的数据类型。

2．够用

接下来可以在备用的数据类型中挑选够用即可的类型作为最后的方案。比如要表述“身份证号码”这样的字段，nvarchar和nchar这样的数据类型都可以，最后我们可以使用nchar(18)，因为身份证号码规定为统一的18位，所以选择定长的数据类型。

3．权衡

Transact-SQL定义如此多的数据类型，在使用时需要综合进行权衡。权衡存储空间和效率，权衡数据的存储方式等。

通常情况下，数据类型的长度越小，存储大量数据所需要的存储空间就越小。因此一次I/O读取到内存中的数据记录就会越多，同样大小的内存空间存储的记录数量就会更多，这样Transact-SQL语句使用数据时从内存读取的几率就增大了，所以自然效率就高，尤其是联机事务频繁的系统，这样的设计优点就十分明显。

数据类型的选择恰当与否往往会对数据库系统的性能产生非常大的影响。

1.2.3 基本数据类型

Transact-SQL中的基本数据类型

1.2.4 SQL Server 2008新增数据类型

在SQL Server 2008中新增加了很多数据类型。

1．日期和时间型

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

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

4．查询表的页分布

执行下面的语句查询给表分配了哪些页。

5．查询数据页内容

执行下面的语句查询页的内容。

        DBCC TRACEON(3604)
        GO
        DBCC PAGE(DBTEST,1,155,1)
        GO

查询结果中，行偏移数组的部分内容如下。说明每行记录在页中都使用了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．深入分析

smallint数据类型存储的数据本身尽管仅需要1个字节，但是当作为表列存储时需要9个字节来存储，这是为什么呢？

这9个字节中：

● 2个字节：数据本身。

● 3个字节：空位图使用，用于表示列的为空性，计算方法为2+(列的总数+7)/8，向上取整数值，这里1个表列，所以计算结果为3。

● 4个字节：用于存储数据行的行标题。

3．结论

smallint数据类型的列大约需要9个字节来存储。

1.2.7 深入研究int（Integer）数据类型

下面我们来深入研究int数据类型的使用。其研究方法同上。

1．使用范围

int数据类型用于表示-231(-2,147,483,648)～231-1(-2,147,483,647)范围的整数，采用固定长度，数据本身需要的存储空间为4字节。

2．深入分析

int数据类型存储的数据本身尽管仅需要4个字节，但是当作为表列存储时需要11个字节来存储，这是为什么呢？

这11个字节中：

● 4个字节：数据本身。

● 3个字节：空位图使用，用于表示列的为空性，计算方法为2+(列的总数+7)/8，向上取整数值，这里1个表列，所以计算结果为3。

● 4个字节：用于存储数据行的行标题。

3．结论

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．深入分析

bigint数据类型存储的数据本身尽管仅需要8个字节，但是当作为表列存储时需要15个字节来存储，这是为什么呢？

这15个字节中：

● 8个字节：数据本身。

● 3个字节：空位图使用，用于表示列的为空性，计算方法为2+ (列的总数+7) /8，向上取整数值，这里1个表列，所以计算结果为3。

● 4个字节：用于存储数据行的行标题。

3．结论

bigint数据类型的列大约需要15个字节来存储。

1.2.9 decimal（numeric）数据类型的使用

下面我们来研究decimal数据类型的使用，Transact-SQL中，numeric数据类型的使用与其相同。对于可变精度和长度的数据类型，我们不再将研究重点放在其存储的长度上，因为这样的研究没有更多的实际意义。

1．使用范围

Decimal（numeric）数据类型用于表示长度可达38位的固定精度和范围的数值（-1038～1038-1）。

● P：表示数值的总长度

● S：小数点右边的小数位数或数字个数。

两者的关系如下。

        0<=S<=P<=38

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

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