51、SQL Server 数据类型全面解析

SQL Server 数据类型全面解析

1. 整数类型

1.1 int 类型

• 取值范围 ：从 -2,147,483,648 到 2,147,483,647（即 –2^31 到 2^31 – 1）。
• 存储大小 ：4 字节。
• 使用场景 ：常用于作为表的主键，因为其占用空间小且存储和检索效率高。
• 缺点 ：没有无符号版本，如果有无符号版本可以存储从 0 到 4,294,967,296（2^32）的非负值。对于有数十亿行的系统，无符号版本可以提供超过二十亿个额外的主键值。
• 应用示例 ：在存储 IP 地址时，IP 地址本质上是一个 32 位整数拆分为四个八位字节。例如，IP 地址 234.23.45.123 对应的整数值为 (234 * 2^3) + (23 * 2^2) + (45 * 2^1) + (123 * 2^0)，这个值适合无符号 32 位整数，但不适合有符号的 int 类型。

1.2 bigint 类型

• 取值范围 ：从 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（即 –2^63 到 2^63 – 1）。
• 存储大小 ：8 字节。
• 使用场景 ：当表中的行数超过二十亿时，或者在处理 IP 地址等情况时，适合使用 bigint 作为主键。

2. 小数类型

2.1 decimal（或 Numeric）类型

• 取值范围 ：所有数值数据（包括小数部分）在 –10^38 + 1 到 10^38 – 1 之间。
• 存储大小 ：根据精度（有效数字的数量）而定，1 - 9 位数字，5 字节；10 - 19 位数字，9 字节；20 - 28 位数字，13 字节；29 - 38 位数字需要 17 字节。
• 特点 ：是一种精确的数据类型，存储方式类似于字符数据，避免了 float 和 real 数据类型可能出现的不精确问题。但在进行数学运算时，由于没有硬件支持，会产生额外的性能和存储成本。
• 精度和刻度 ：
  • 精度 ：数字中有效数字的总数。例如，数字 10 需要精度为 2，43.00000004 需要精度为 10。精度范围从 1 到 38。
  • 刻度 ：小数点右侧可能的有效数字数量。例如，10 的刻度为 0，43.00000004 的刻度为 8。

精度范围	存储大小
1 - 9 位	5 字节
10 - 19 位	9 字节
20 - 28 位	13 字节
29 - 38 位	17 字节

1.3 示例代码

-- 定义一个 decimal 变量
DECLARE @testvar decimal(3,1)
-- 插入数据
SELECT @testvar = -10.155555555
-- 查询数据
SELECT @testvar

上述代码中， decimal(3,1) 允许输入大于 -99.94 且小于 99.94 的数值。输入 99.949999 可以，但输入 99.95 会因为四舍五入到 100.0 而无法显示。

1.4 注意事项

• 当使用 SET NUMERIC_ROUNDABORT ON 时，如果隐式数据转换会导致精度损失，会生成错误。
• 一般应尽量少使用 decimal 类型，除非需要存储不能接受任何精度损失的特定值。

3. 货币类型

3.1 类型介绍

SQL Server 中有两种货币类型： money 和 smallmoney 。

类型	取值范围	存储大小
money	-922,337,203,685,477.5808 到 922,337,203,685,477.5807	8 字节
smallmoney	-214,748.3648 到 214,748.3647	4 字节

3.2 存在的问题

• 单位无实际价值 ：可以指定货币单位，如 $ 或 £，但这些单位没有实际意义。

-- 创建表
CREATE TABLE dbo.testMoney
(
    moneyValue money
)
GO
-- 插入数据
INSERT INTO dbo.testMoney
VALUES ($100)
INSERT INTO dbo.testMoney
VALUES (100)
INSERT INTO dbo.testMoney
VALUES (£100)
GO
-- 查询数据
SELECT * FROM dbo.testMoney

上述代码插入不同单位的数据，但查询结果都是 100.00。

• 四舍五入问题 ：在进行数学运算时， money 类型存在四舍五入问题。

-- 定义 money 变量
DECLARE @money1 money, @money2 money
SET @money1 = 1.00
SET @money2 = 800.00
-- 计算并转换为 money 类型
SELECT cast(@money1/@money2 as money)

-- 定义 decimal 变量
DECLARE @decimal1 decimal(19,4), @decimal2 decimal(19,4)
SET @decimal1 = 1.00
SET @decimal2 = 800.00
-- 计算并转换为 decimal 类型
SELECT cast(@decimal1/@decimal2 as decimal(19,4))

上述代码中， money 类型的计算结果为 0.0012，而 decimal 类型的计算结果为 0.0013。

3.3 建议

数据库架构师通常建议避免使用 money 类型，而使用 numeric 类型，因为 numeric 类型能以自然的方式给出数学问题的答案，且没有内置单位的干扰。可以使用 DECIMAL(19,4) 来复制 money 的范围，使用 DECIMAL (10,4) 来复制 smallmoney 的范围。

4. 近似数值数据

4.1 类型介绍

近似数值数据包含小数点，存储格式便于快速操作，被称为浮点类型，因为其有效数字位数固定，但小数点位置“浮动”，可以存储非常小或非常大的数字。主要涉及 real 和 float 数据类型，它们是 IEEE 754 标准的单精度和双精度浮点值。

4.2 float [ (N) ] 类型

• 取值范围 ：从 –1.79E + 308 到 1.79E + 308。可以指定尾数中使用的位数，从 1 到 53，默认值为 53。
• 存储大小 ：
  | N（尾数位数） | 精度 | 存储大小 |
  | ---- | ---- | ---- |
  | 1 - 24 | 7 | 4 字节 |
  | 25 - 53 | 15 | 8 字节 |

SQL Server 会将所有 N 值向上舍入到 24 或 53，因此每个范围内的存储和精度相同。

4.3 特点和使用场景

• 可以表示从 -1.79E + 308 到 1.79E + 308 的大多数值，最多 15 位有效数字。虽然有效数字位数不如 numeric 数据类型多，但范围非常大，适用于几乎所有科学应用。
• 由于小数点位置不固定，可以存储各种模式的数值，如 0.DDDDDDDDDDDDDDD、NNNNN.DDDDDDDDDD 等，适合存储和处理极端范围的值。

4.4 real 类型

real 是 float(24) 数据类型的同义词，可以存储从 -3.40E + 38 到 3.40E + 38 的值。

5. 日期和时间数据

5.1 类型介绍

SQL Server 中有两种处理日期和时间值的数据类型： datetime 和 smalldatetime 。

类型	取值范围	存储大小	精度
smalldatetime	1900 年 1 月 1 日到 2079 年 6 月 6 日	4 字节（两个 2 字节整数，一个用于表示从 1900 年 1 月 1 日起的天数偏移，另一个用于表示午夜过后的分钟数）	1 分钟
datetime	1753 年 1 月 1 日到 9999 年 12 月 31 日	8 字节（两个 4 字节整数，一个用于表示从 1753 年 1 月 1 日起的天数偏移，另一个用于表示午夜过后的 3.33 毫秒周期数）	3.33 毫秒

5.2 使用场景

• smalldatetime ：当只需要存储事件的日期和可能的时间，且精度为一分钟不是问题时，是最佳选择。
• datetime ：需要高精度的时间戳列（如记录操作的确切时间），或者在并发控制机制中使用时，适合使用 datetime 。但如果不需要这么大的范围和精度，会占用较多内存。

5.3 日期格式

在处理 datetime 值时，使用标准格式总是最好的。建议使用 ‘YYYY - MM - DD’ 表示日期，在表示 datetime 值时附加 ‘HH:MM:SS.LLL’（L 表示毫秒）。例如：

-- 转换为 smalldatetime 类型
select cast ('2006-01-01' as smalldatetime) as dateOnly
-- 转换为 datetime 类型
select cast('2006-01-01 14:23:00.003' as datetime) as withTime

5.4 使用用户定义数据类型处理日期和时间

使用 datetime 数据类型只需要日期部分或时间部分时，可能会很麻烦。可以使用以下方法解决：
- 使用多列 ：分别存储年、月、小时、分钟、秒等，但查询不太方便，需要至少三列来查询给定时间，并且需要对涉及查询的所有列进行索引以获得良好的性能。

-- 在 tempdb 中创建表
USE tempdb
GO
CREATE TABLE date
(
    dateValue datetime,
    year as (datepart(yy, dateValue)) persisted,
    month as (datepart(m, dateValue)) persisted
)
GO
-- 插入数据
INSERT INTO date(dateValue)
VALUES ('2005-04-12')
-- 查询数据
SELECT * FROM date

• 使用单列 ：存储从午夜到当前时间的秒数（或秒的部分），但难以对特定时间段内的值进行合理查询。适合在内部过程中使用，不需要人类用户交互。
• 使用用户定义函数 ：将整数值转换为内置的 datetime 变量，用于日期计算或显示给客户端。

-- 创建函数
CREATE FUNCTION intDateType$convertToDatetime
(
    @dateTime int
)
RETURNS datetime
AS
BEGIN
    RETURN (dateadd(second, @datetime, '1990-01-01'))
END
-- 测试函数
SELECT dbo.intDateType$convertToDatetime(485531247) as convertedValue

6. 字符串类型

在 SQL Server 中，大多数数据使用字符数据类型存储，但很多时候字符列被用于存储非字符数据，如数字和日期。这种做法虽然技术上可行，但并不理想。因为存储 8 位数字的字符字符串至少需要 8 字节，而作为整数只需要 4 字节。此外，整数的搜索更容易，并且可以使用处理器的固有函数进行操作，而不是使用 SQL Server 特定的函数。

综上所述，在使用 SQL Server 时，需要根据具体的业务需求和数据特点，选择合适的数据类型，以确保数据的存储效率和查询性能。

7. 数据类型选择的综合考量

7.1 性能与存储的平衡

在选择数据类型时，性能和存储是两个关键因素。例如， int 类型占用 4 字节，存储和检索效率高，适合作为表的主键；而 bigint 类型虽然范围更大，但占用 8 字节，在不需要这么大范围时会浪费存储空间。同样， decimal 类型虽然精确，但在进行数学运算时会有额外的性能开销，而 float 和 real 类型虽然范围大，但存在一定的不精确性。

7.2 业务需求的匹配

不同的业务场景对数据类型的要求不同。对于需要高精度的金融数据，应选择 decimal 类型；对于科学计算， float 或 real 类型更合适；对于日期和时间的记录，根据精度要求选择 datetime 或 smalldatetime 。在设计数据库时，需要充分考虑业务需求，确保数据类型能够准确地反映业务逻辑。

7.3 避免常见错误

在使用数据类型时，需要避免一些常见的错误。例如，避免使用 money 类型，因为它存在单位无实际价值和四舍五入问题；在使用 datetime 类型时，要注意日期格式的统一，避免因格式问题导致数据解析错误。

8. 数据类型转换与注意事项

8.1 隐式转换

SQL Server 会在某些情况下自动进行数据类型的隐式转换。例如，在进行数学运算时，如果操作数的数据类型不同，会自动将其转换为兼容的数据类型。但隐式转换可能会导致精度损失或性能下降，因此应尽量避免。

8.2 显式转换

可以使用 CAST 或 CONVERT 函数进行显式的数据类型转换。例如：

-- 使用 CAST 函数进行转换
SELECT CAST('123' AS INT)
-- 使用 CONVERT 函数进行转换
SELECT CONVERT(INT, '123')

8.3 注意事项

在进行数据类型转换时，需要注意以下几点：
- 确保转换是合法的，否则会产生错误。例如，将非数字字符串转换为整数会导致错误。
- 注意精度损失的问题。例如，将 decimal 类型转换为 float 类型可能会导致精度丢失。
- 使用 SET NUMERIC_ROUNDABORT ON 可以在隐式数据转换导致精度损失时生成错误，但使用时要谨慎，因为它可能会影响应用程序的正常运行。

9. 总结与建议

9.1 总结

本文详细介绍了 SQL Server 中的各种数据类型，包括整数类型、小数类型、货币类型、近似数值数据、日期和时间数据以及字符串类型。每种数据类型都有其特点、取值范围、存储大小和使用场景。在选择数据类型时，需要综合考虑性能、存储、业务需求等因素，避免常见的错误。

9.2 建议

• 根据数据的实际范围和精度要求选择合适的数据类型，避免使用过大或过小的数据类型。
• 尽量使用标准的日期和时间格式，避免因格式问题导致数据解析错误。
• 避免使用 money 类型，推荐使用 numeric 类型来存储货币数据。
• 在进行数据类型转换时，尽量使用显式转换，避免隐式转换带来的问题。

9.3 流程图：数据类型选择流程

graph TD
    A[开始] --> B{数据类型需求}
    B -->|整数| C{范围大小}
    C -->|小范围| D(int)
    C -->|大范围| E(bigint)
    B -->|小数| F{精度要求}
    F -->|高精度| G(decimal)
    F -->|一般精度| H(float/real)
    B -->|货币| I(numeric)
    B -->|日期时间| J{精度要求}
    J -->|低精度| K(smalldatetime)
    J -->|高精度| L(datetime)
    B -->|字符串| M{数据性质}
    M -->|字符数据| N(字符类型)
    M -->|数字或日期| O(合适的数值或日期类型)
    D --> P[结束]
    E --> P
    G --> P
    H --> P
    I --> P
    K --> P
    L --> P
    N --> P
    O --> P

通过合理选择数据类型，可以提高数据库的存储效率和查询性能，确保数据的准确性和完整性。在实际应用中，需要不断总结经验，根据具体情况进行调整和优化。