SQL Server 开发系列（第一期）：数据类型——精准选择，从源头避免性能陷阱

SQL Server 开发系列（第一期）：数据类型——精准选择，从源头避免性能陷阱

作为开发人员，你每天都要和数据类型打交道。但你是否想过：为什么 NVARCHAR 比 VARCHAR 慢？为什么 DATETIME 和 DATETIME2 的性能差异巨大？为什么 WHERE 1=1 这种写法会影响执行计划？这一期，我们从最基础也最容易忽视的数据类型开始，帮你建立正确的开发认知——选对数据类型，后续的索引、查询、存储都会事半功倍。

一、数据类型选择的黄金法则

在深入具体类型之前，先记住三条黄金法则：

二、字符串类型：CHAR vs VARCHAR vs NVARCHAR

2.1 核心区别

2.2 实战示例

-- 创建测试表
CREATE TABLE StringTest (
    ID INT IDENTITY(1,1),
    Code CHAR(10),        -- 固定10个字符，如订单号 ORD-001234
    Name VARCHAR(50),     -- 可变长度，如英文名
    Address NVARCHAR(200) -- 支持中文地址
);

-- 插入数据
INSERT INTO StringTest (Code, Name, Address)
VALUES ('ORD-001', 'John Doe', '北京市朝阳区xxx路123号');

-- 查看实际占用空间
SELECT 
    DATALENGTH(Code) AS CodeBytes,  -- 10 字节（固定）
    DATALENGTH(Name) AS NameBytes,  -- 8 字节（'John Doe' 实际8字符）
    DATALENGTH(Address) AS AddressBytes  -- 30 字节（中文字符 × 2）
FROM StringTest;

2.3 CHAR vs VARCHAR 性能对比

-- 创建两张对比表
CREATE TABLE Orders_Char (OrderID CHAR(10), Data CHAR(100));
CREATE TABLE Orders_VarChar (OrderID VARCHAR(10), Data VARCHAR(100));

-- 插入100万条数据（略）
-- 测试查询性能
SET STATISTICS TIME ON;

-- CHAR 查询
SELECT * FROM Orders_Char WHERE OrderID = 'ORD0000001';  -- 固定长度，精确匹配

-- VARCHAR 查询  
SELECT * FROM Orders_VarChar WHERE OrderID = 'ORD0000001';

-- 观察逻辑读取次数：CHAR 表通常略优（存储结构更规整）

2.4 常见陷阱

陷阱1：用 NVARCHAR 存纯英文

-- ❌ 浪费空间（双倍存储）
CREATE TABLE Users (Email NVARCHAR(100));

-- ✅ 纯英文用 VARCHAR
CREATE TABLE Users (Email VARCHAR(100));

陷阱2：用 CHAR 存变长数据

-- ❌ 浪费空间（'John' 存为 'John     '，6个空格）
CREATE TABLE Names (FirstName CHAR(10));

-- ✅ 变长用 VARCHAR
CREATE TABLE Names (FirstName VARCHAR(10));

陷阱3：对索引列使用 VARCHAR(MAX)

-- ❌ 不能创建索引（或只能创建全文索引）
CREATE TABLE Articles (Content VARCHAR(MAX));

-- ✅ 需要用索引的列，控制长度在 8000 以内
CREATE TABLE Articles (ShortDesc VARCHAR(500), Content VARCHAR(MAX));

三、数值类型：精度决定空间和性能

3.1 整型选择

-- ✅ 好的设计
CREATE TABLE Products (
    ProductID INT IDENTITY(1,1),     -- 4 字节
    Status TINYINT,                   -- 1 字节，0=下架 1=上架
    Stock SMALLINT,                   -- 2 字节，库存最多 3万
    TotalSales BIGINT                 -- 8 字节，累计销量可能很大
);

-- ❌ 坏的设计
CREATE TABLE Products (
    ProductID BIGINT IDENTITY(1,1),   -- 浪费 4 字节/行 × 1000万 = 38MB
    Status INT,                       -- 1 字节就能搞定，浪费 3 字节
    Stock INT                         -- 2 字节就能搞定
);

3.2 小数类型：精确 vs 近似

-- ✅ 金额必须用 DECIMAL
CREATE TABLE Orders (
    Amount DECIMAL(18,2),  -- 18位整数，2位小数，足够大部分金额
    TaxRate DECIMAL(5,4)   -- 如 0.1250 表示 12.50%
);

-- ❌ 金额用 FLOAT 会导致精度丢失
CREATE TABLE Orders (Amount FLOAT);  -- 0.1 + 0.2 可能不等于 0.3

DECIMAL 的精度与存储对照：

-- 根据实际需求选择精度
DECIMAL(10,2)  -- 最大 99,999,999.99，占用 9 字节
DECIMAL(18,2)  -- 最大 9,999,999,999,999,999.99，占用 9 字节（相同！）
-- 注意：DECIMAL(18,2) 和 DECIMAL(10,2) 都占用 9 字节，所以可以适当放宽

3.3 布尔值：用 BIT 而不是 INT

-- ✅ 推荐
CREATE TABLE Users (
    IsActive BIT,      -- 1 字节，可存 0/1/NULL
    IsDeleted BIT
);

-- ❌ 不推荐
CREATE TABLE Users (
    IsActive INT,      -- 4 字节，浪费
    IsDeleted CHAR(1)  -- 1 字节但存储 'Y'/'N'，查询需要转换
);

-- BIT 的使用技巧
SELECT * FROM Users WHERE IsActive = 1;  -- 直接用数字
-- BIT 列不能用于索引（选择性太差），但适合标记

四、日期时间类型：谁才是性能之王？

4.1 类型对比

4.2 为什么 DATETIME2 优于 DATETIME？

-- 创建对比表
CREATE TABLE DateTest (
    ID INT,
    Date1 DATETIME,
    Date2 DATETIME2(3)  -- 同样保留 3 位毫秒
);

-- 插入 100 万条数据
INSERT INTO DateTest (ID, Date1, Date2)
SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
    DATEADD(day, n % 3650, '2020-01-01'),
    DATEADD(day, n % 3650, '2020-01-01')
FROM sys.all_columns a, sys.all_columns b;

-- 查看占用空间（DATETIME2 可以更小）
EXEC sp_spaceused 'DateTest';

DATETIME 的问题：

1. 精度只有 3.33ms（不是整数毫秒）
2. 存储格式与 SQL 标准不兼容
3. 不支持 YYYY-MM-DD HH:MM:SS.nnnnnnn 格式

DATETIME2 的优势：

1. 精度可控（0-7 位小数秒）
2. 存储空间可变（6-8 字节）
3. 支持更大范围（0001-9999）
4. 与 .NET DateTime 完美兼容

-- ✅ 推荐
CREATE TABLE Orders (
    OrderDate DATETIME2(0),     -- 精确到秒，7 字节
    CreatedAt DATETIME2(2),     -- 精确到 10ms，7 字节
    UpdatedAt DATETIME2(7)      -- 最高精度，8 字节
);

-- ❌ 不推荐（除非维护旧系统）
CREATE TABLE Orders (OrderDate DATETIME);

4.3 日期查询的性能陷阱

-- ❌ 坏：函数包裹列，导致索引失效
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;

-- ✅ 好：使用范围查询
SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-01' 
  AND OrderDate < '2025-01-01';

-- ✅ 好：使用 DATETIME2 的范围查询
DECLARE @StartDate DATETIME2 = '2024-01-01';
DECLARE @EndDate DATETIME2 = '2025-01-01';
SELECT * FROM Orders WHERE OrderDate >= @StartDate AND OrderDate < @EndDate;

五、其他常用类型

5.1 唯一标识符：UNIQUEIDENTIFIER (GUID)

-- 创建 GUID 列
CREATE TABLE Users (
    UserID UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,     -- 随机 GUID
    UserIDSeq UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID()     -- 顺序 GUID（性能更好）
);

-- GUID 作为主键的问题
-- ❌ NEWID() 随机，导致索引页拆分频繁，插入性能差
-- ✅ NEWSEQUENTIALID() 顺序生成，减少页拆分

GUID vs INT 主键性能对比：

5.2 二进制：BINARY vs VARBINARY

-- 存储文件哈希值、加密数据等
CREATE TABLE Files (
    FileID INT PRIMARY KEY,
    FileHash BINARY(32),          -- SHA256 固定 32 字节
    FileData VARBINARY(MAX)       -- 文件内容，可变长度
);

-- 插入哈希值
INSERT INTO Files (FileHash) VALUES (HASHBYTES('SHA2_256', 'Hello World'));

5.3 XML 和 JSON

-- XML 类型
CREATE TABLE Config (Settings XML);

-- JSON 存储（SQL Server 2016+，实际存 NVARCHAR）
CREATE TABLE Logs (EventData NVARCHAR(MAX));

-- 查询 JSON（需要添加索引）
ALTER TABLE Logs ADD vCustomerID AS JSON_VALUE(EventData, '$.CustomerID');
CREATE INDEX IX_Logs_CustomerID ON Logs(vCustomerID);

六、隐式类型转换——最隐蔽的性能杀手

6.1 什么是隐式转换？

当 WHERE 子句中列的类型与值的类型不匹配时，SQL Server 会先转换类型再比较，导致索引失效。

-- 创建测试表
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Phone VARCHAR(20)
);

CREATE INDEX IX_Users_Phone ON Users(Phone);

-- ❌ 隐式转换：Phone 是 VARCHAR，传入 INT
SELECT * FROM Users WHERE Phone = 1234567890;
-- 实际执行：WHERE CONVERT(INT, Phone) = 1234567890
-- 索引失效！因为函数包裹了列

-- ✅ 正确写法
SELECT * FROM Users WHERE Phone = '1234567890';

6.2 常见隐式转换场景

-- 特别注意：NVARCHAR 和 VARCHAR 的转换
-- 列是 VARCHAR，传入 NVARCHAR
SELECT * FROM Users WHERE Name = N'John';  -- ❌ 索引失效

-- 列是 NVARCHAR，传入 VARCHAR  
SELECT * FROM Users WHERE Name = 'John';   -- ✅ VARCHAR 自动转 NVARCHAR，索引有效

-- 最佳实践：统一使用 NVARCHAR 参数（存储过程）
CREATE PROC GetUser @Name NVARCHAR(50)
AS
    SELECT * FROM Users WHERE Name = @Name;  -- 列也是 NVARCHAR，完美匹配

6.3 如何发现隐式转换

-- 查看执行计划中的警告
-- 在 SSMS 执行计划中，扫描运算符上会有黄色感叹号
-- 提示 "CONVERT_IMPLICIT" 或类型转换

-- 也可以通过扩展事件捕获
SELECT * FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)
WHERE qs.last_execution_time > DATEADD(hour, -1, GETDATE())
  AND qs.sql_handle IN (
      SELECT sql_handle FROM sys.dm_exec_requests
  );

七、数据类型选择决策树

开始
 │
 ├─ 是否需要存储 Unicode（中文/多语言）？
 │   ├─ 是 → NVARCHAR/NCHAR
 │   └─ 否 → VARCHAR/CHAR
 │
 ├─ 长度是否固定？
 │   ├─ 是 → CHAR/NCHAR
 │   └─ 否 → VARCHAR/NVARCHAR
 │
 ├─ 是否为数值？
 │   ├─ 整数 → TINYINT/SMALLINT/INT/BIGINT
 │   ├─ 金额 → DECIMAL(18,2)
 │   └─ 科学计算 → FLOAT/REAL
 │
 ├─ 是否为日期时间？
 │   ├─ 只需日期 → DATE
 │   ├─ 需要时间 → DATETIME2
 │   └─ 需要时区 → DATETIMEOFFSET
 │
 └─ 是否需要全局唯一？
     └─ 是 → UNIQUEIDENTIFIER (NEWSEQUENTIALID)

八、核心总结

一句话记住本期内容：

数据类型是数据库设计的基石——选对了事半功倍，选错了性能崩溃；VARCHAR vs NVARCHAR 差 2 倍空间，DATETIME vs DATETIME2 差 100 倍精度，隐式转换让索引形同虚设。

动手练习

-- 问题1：以下表设计有什么问题？
CREATE TABLE Orders (
    OrderID BIGINT IDENTITY(1,1),
    OrderNo VARCHAR(20),
    CustomerID INT,
    Amount FLOAT,
    OrderDate DATETIME,
    Status INT,
    IsPaid INT
);

-- 问题2：以下查询为什么慢？
-- Phone 列是 VARCHAR(20)，有索引
SELECT * FROM Users WHERE Phone = 13800138000;

-- 问题3：如何优化这个日期查询？
SELECT * FROM Orders WHERE CONVERT(DATE, OrderDate) = '2024-01-01';

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1),           -- 21亿够用，BIGINT 浪费
    OrderNo CHAR(20),                     -- 固定长度用 CHAR
    CustomerID INT,                       -- 合理
    Amount DECIMAL(18,2),                 -- 金额用 DECIMAL
    OrderDate DATETIME2(0),               -- 用 DATETIME2
    Status TINYINT,                       -- 状态用 TINYINT
    IsPaid BIT                            -- 布尔用 BIT
);

SELECT * FROM Orders 
WHERE OrderDate >= '2024-01-01' 
  AND OrderDate < '2024-01-02';

下一期预告

T-SQL 编程基础——变量、流程控制与动态 SQL

• 变量声明与使用技巧
• IF/ELSE、WHILE 循环的正确用法
• 动态 SQL 的构建与防注入
• 游标的替代方案（尽量不用）
• 错误处理：TRY...CATCH 与事务

📌 本文代码已在 SQL Server 2019+ 验证。数据类型的选择影响深远，建议在新项目开始时仔细设计，避免后期迁移痛苦。

本系列持续更新中，点击关注不错过第二期。