当前位置： 首页 > news >正文

SQL Server 基础语句3： 数据操作（插入、删除、更新表）与数据类型

news 2025/6/23 16:03:27

文章目录

    • 一、DML
      • 1.1 `INSERT INTO`：向表中插入行
        • 1.1.1 基础示例
        • 1.1.2 使用 `OUTPUT` 子句捕获插入值
        • 1.1.3 向自增列插入显式值
        • 1.1.4 插入多行数据（INSERT Multiple Rows）
      • 1.2 `INSERT INTO SELECT` ：将数据插入已知表
        • 1.2.1 插入所有行
        • 1.2.2 根据筛选条件插入部分行
        • 1.2.3 插入前 N 行
        • 1.2.4 使用 `EXCEPT`插入不重复数据
      • 1.3 `SELECT INTO`：快速复制表数据
        • 1.3.1 基础示例
        • 1.3.2 `SELECT INTO` VS `INSERT INTO SELECT`
      • 1.4 `DELETE`：删除行数据
        • 1.4.1 随机删除行
        • 1.4.2 删除条件行
        • 1.4.3 删除表中指定列
      • 1.5 `UPDATE`：更新表数据
        • 1.5.1 更新整列数据
        • 1.5.2 使用条件更新部分行
      • 1.6 `UPDATE JOIN`：跨表更新
        • 1.6.1 设置样本表
        • 1.6.2 `UPDATE INNER JOIN`示例
        • 1.6.3 `UPDATE LEFT JOIN`示例
      • 1.7 `MERGE`：同步表数据
        • 1.7.1 SQL Server MERGE语句简介
        • 1.7.2 简单示例
    • 二、数据类型
      • 2.1 数据类型概览
        • 2.1.1 精确数值类型（Exact Numerics）
        • 2.1.2 近似数值类型（Approximate Numerics）
        • 2.1.3 日期与时间类型（Date & Time）
        • 2.1.4 字符与二进制类型
        • 2.1.5 最佳实践
      • 2.2 整数类型详解
        • 2.2.1 整数数据类型概述
        • 2.2.2 整数类型数据转换
      • 2.3 `DECIMAL` 数据类型
      • 2.4 `BIT` 数据类型
        • 2.4.1 `BIT` 类型概述
        • 2.4.2 应用示例
        • 2.4.3 注意事项
      • 2.5 CHAR 数据类型（固定长度的非 Unicode 字符串）
        • 2.5.1 CHAR 概述
        • 2.5.2 使用示例
        • 2.5.3 比较时忽略尾部空格
        • 2.5.4 总结
      • 2.6 NCHAR 数据类型（固定长度 Unicode 字符串）
      • 2.7 `VARCHAR` 数据类型(可变长度的非 Unicode 字符串)
        • 2.7.1 长度前缀
        • 2.7.2 简单示例
        • 2.7.3 最佳实践
      • 2.8 `NVARCHAR` 数据类型
      • 2.9 `DATETIME2` 数据类型
        • 2.9.1 `DATETIME2` 概述
        • 2.9.2 使用示例
      • 2.10 `DATE` 数据类型
        • 2.10.1 定义 `DATE` 列
        • 2.10.2 基于 `DATE` 值查询表中的数据
        • 2.10.3 与其他日期类型一起使用
      • 2.11 `TIME` 数据类型
        • 2.11.1 时区
        • 2.11.2 使用示例
      • 2.12 DATETIMEOFFSET 数据类型
        • 2.12.1 DATETIMEOFFSET概述
        • 2.12.2 重要函数
      • 2.13 GUID与UNIQUEIDENTIFIER数据类型
        • 2.13.1 GUID使用示例
        • 2.13.2 📊 GUID 优缺点与使用场景
        • 2.13.3 ⚙️ 生成方式比较
        • 2.13.4 高级技巧与最佳实践

全文参考《SQL Server Basics》

一、DML

数据操作 (DML)查询语句说明数据操作 (DML)查询语句说明
插入数据INSERT向表中插入一行。插入多行INSERT multiple rows使用单个INSERT语句向表中插入多行。
插入查询结果INSERT INTO SELECT将查询结果插入表中。更新数据UPDATE修改表中的现有值。
更新连接UPDATE JOIN使用JOIN子句根据另一表中的值更新表中的值。删除数据DELETE删除表中的一行或多行。
合并操作MERGE使用单条语句执行插入、更新和删除的组合操作。清空表TRUNCATE TABLE快速删除表中所有数据。
查找重复值Find duplicates查找表中一个或多个列的重复值。删除重复值Delete duplicates描述如何从表中删除重复行。

1.1 INSERT INTO：向表中插入行

INSERT 语句用于向表中添加一行或多行数据，其基本语法如下：

INSERT INTO table_name (column_list)
VALUES (value_list);
  • 表名：指定要插入数据的表名，通常包括架构名（如 schema_name.table_name）。
  • 列名列表：指定要插入数据的列名，列名之间用逗号分隔，并用括号括起来-。
  • 值列表：为指定的列提供对应的值，值之间用逗号分隔，并用括号括起来。

如果表中的某些列未出现在列名列表中，SQL Server 会根据以下规则自动为这些列提供值：

  • 如果列具有 IDENTITY 属性，则自动填充下一个增量值。
  • 如果列有默认值，则使用默认值。
  • 如果列的数据类型为 timestamp，则使用当前时间戳值。
  • 如果列允许 NULL，则使用 NULL。
  • 如果列是计算列，则使用计算值。
1.1.1 基础示例

为了演示 INSERT 语句的用法，我们首先创建一个名为 promotions 的表：

CREATE TABLE sales.promotions (promotion_id INT PRIMARY KEY IDENTITY (1, 1),	-- 主键列，自动递增promotion_name VARCHAR (255) NOT NULL,			-- 促销活动名，不可为空discount NUMERIC (3, 2) DEFAULT 0,				-- 折扣率，默认为 0start_date DATE NOT NULL,						-- 促销开始日期，不可为空expired_date DATE NOT NULL						-- 促销结束日期，不可为空。
);

以下是一个向 promotions 表中插入新行的示例：

INSERT INTO sales.promotions (promotion_name,discount,start_date,expired_date
)
VALUES('2018 Summer Promotion',0.15,'20180601','20180901');

在这个示例中，我们为 promotion_name、discount、start_date 和 expired_date 列提供了值，而没有为 promotion_id 列提供值，因为它是自增列，SQL Server 会自动为其生成值。

执行成功后，SQL Server 会返回以下消息：

(1 row affected)

如果没有插入任何行，返回的消息将是：

(0 rows affected)

可以通过以下查询语句验证插入操作：

SELECT * FROM sales.promotions;

在这里插入图片描述

1.1.2 使用 OUTPUT 子句捕获插入值

OUTPUT 子句可以捕获插入操作后的值，从而确认插入的数据是否符合预期：

INSERT INTO sales.promotions (promotion_name,discount,start_date,expired_date
) OUTPUT inserted.promotion_id,  -- 捕获多个列inserted.promotion_name,inserted.discount,inserted.start_date,inserted.expired_date
VALUES('2018 Winter Promotion',0.2,'20181201','20190101');

在这里插入图片描述
如果没有返回任何结果，则表示插入失败。

  此外，使用 @@ROWCOUNT也可以检查插入操作是否成功。@@ROWCOUNT 是一个系统函数，返回上一条 SQL 语句影响的行数。如果插入成功，@@ROWCOUNT 的值将大于 0；否则，值为 0。

-- 插入语句IF @@ROWCOUNT > 0PRINT 'Insert operation was successful';
ELSEPRINT 'Insert operation failed';
1.1.3 向自增列插入显式值

  通常情况下，我们不需要为自增列（如 IDENTITY 列）提供显式值，因为 SQL Server 会自动为其生成值。但在某些特殊情况下（如数据迁移），我们可能需要手动插入值。以下是一个尝试向 IDENTITY 列插入显式值的示例：

INSERT INTO sales.promotions (promotion_id,promotion_name,discount,start_date,expired_date
) OUTPUT inserted.promotion_id
VALUES(4,'2019 Spring Promotion',0.25,'20190201','20190301');

执行此语句时，SQL Server 会报错：

Cannot insert explicit value for identity column in table 'promotions' when IDENTITY_INSERT is set to OFF.

要解决此问题，需要先启用 IDENTITY_INSERT，如下所示：

SET IDENTITY_INSERT sales.promotions ON;-- 同上插入语句SET IDENTITY_INSERT sales.promotions OFF;

启用 IDENTITY_INSERT 后，可以成功插入显式值。插入完成后，应立即禁用 IDENTITY_INSERT，以防止其他操作意外插入显式值。

-- 检查插入后的结果
SELECT * FROM sales.promotions;

在这里插入图片描述

1.1.4 插入多行数据（INSERT Multiple Rows）

使用单条 INSERT 语句插入多行数据的语法如下：

INSERT INTO table_name (column_list)
OUTPUT inserted.column1, inserted.column2...
VALUES(value_list_1),(value_list_2),...(value_list_n);

  其中value_list是为每一行提供的一组对应值，每组值之间用逗号分隔，并用括号括起来。要注意的是，SQL Server 2008 及更高版本支持这种语法，且单次插入的最大行数为 1,000 行。如果需要插入更多行，可以考虑使用多个 INSERT 语句、BULK INSERT 或派生表。

以下是一个使用单条 INSERT 语句插入多行数据的示例：

INSERT INTO sales.promotions (promotion_name,discount,start_date,expired_date
)
VALUES('2019 Summer Promotion',0.15,'20190601','20190901'),('2019 Fall Promotion',  0.20,'20191001','20191101'),('2019 Winter Promotion',0.25,'20191201','20200101');

执行成功后，SQL Server 会返回以下消息：

(3 rows affected)

这表示成功插入了 3 行数据。可以通过以下查询语句验证插入操作：

SELECT * FROM sales.promotions;

在这里插入图片描述

1.2 INSERT INTO SELECT ：将数据插入已知表

  INSERT INTO SELECT 用于将数据从一个或多个表中插入到目标表（必须存在）中。这种方法不仅能够高效地批量插入数据，还能灵活地根据条件筛选数据，其基本语法如下：

INSERT [TOP (expression) [PERCENT]]
INTO target_table (column_list)SELECT (source_column_list)
FROM source_table
WHERE condition;
  • TOP (expression) [PERCENT]：可选部分，用于限制插入的行数。expression 可以是一个数字，表示插入的行数；也可以是一个百分比，表示插入的行数占总行数的百分比。
  • target_table：目标表，即数据将被插入的表。
  • column_list：目标表中需要插入数据的列名列表。
  • query：一个有效的 SELECT 查询语句，用于从其他表中检索数据。查询返回的列必须与目标表的列对应。

插入之前，确保数据一致性是至关重要：

  • 验证数据类型和格式：执行 INSERT INTO SELECT 语句之前，确保查询返回的列的数据类型和格式（例如日期格式、字符串长度）与目标表的列定义完全一致

  • 验证约束：在插入数据之前，确保查询返回的数据满足目标表的约束条件（主键约束、唯一约束、外键约束）

  • 使用事务管理：将 INSERT INTO SELECT 语句包含在一个事务中，确保操作的原子性。如果插入过程中发生错误，可以回滚事务

    BEGIN TRANSACTION;BEGIN TRYINSERT INTO target_table (col1, col2, col3)SELECT source_col1, source_col2, source_col3FROM source_tableWHERE source_col1 NOT IN (SELECT col1 FROM target_table);COMMIT TRANSACTION;
END TRY
BEGIN CATCHROLLBACK TRANSACTION;PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;
1.2.1 插入所有行

为了演示 INSERT INTO SELECT 的用法，我们首先创建一个名为 sales.addresses 的表：

CREATE TABLE sales.addresses (address_id INT IDENTITY PRIMARY KEY,  -- 主键列，自动递增street VARCHAR (255) NOT NULL,		  -- 地址列，不可为空city VARCHAR (50),					  -- 城市列state VARCHAR (25),					  -- 州zip_code VARCHAR (5)				  -- 邮编
);

以下是一个从 sales.customers 表中插入所有地址到 sales.addresses 表的示例：

在这里插入图片描述

INSERT INTO sales.addresses (street, city, state, zip_code)
SELECTstreet,city,state,zip_code
FROMsales.customers
ORDER BYfirst_name,last_name;

此语句将 sales.customers 表中的所有地址信息插入到 sales.addresses 表中。ORDER BY 子句用于按 first_name 和 last_name 排序。验证插入操作的查询如下：

SELECT * FROM sales.addresses;

在这里插入图片描述

1.2.2 根据筛选条件插入部分行

通过在 SELECT 查询中使用 WHERE 子句来限制插入的行数：

INSERT INTO sales.addresses (street, city, state, zip_code)
SELECTstreet,city,state,zip_code
FROMsales.stores
WHEREcity IN ('Santa Cruz', 'Baldwin');

(2 rows affected)

此语句仅将 sales.stores 表中城市为 Santa Cruz 或 Baldwin 的地址插入到 sales.addresses 表中。

1.2.3 插入前 N 行

使用 TOP 子句可以插入前 N 行数据：

TRUNCATE TABLE sales.addresses; -- 清空目标表INSERT TOP (10)
INTO sales.addresses (street, city, state, zip_code)
SELECTstreet,city,state,zip_code
FROMsales.customers
ORDER BYfirst_name,last_name;

此语句将 sales.customers 表中按 first_name 和 last_name 排序后的前 10 行数据插入到 sales.addresses 表中。如果需要插入前 10% 的行，代码可改为：

TRUNCATE TABLE sales.addresses; -- 清空目标表INSERT TOP (10) PERCENT
。。。

此语句将 sales.customers 表中按 first_name 和 last_name 排序后的前 10% 行数据插入到 sales.addresses 表中。

1.2.4 使用 EXCEPT插入不重复数据

如果需要插入不重复的数据，可以使用 EXCEPT 来确保插入的数据在目标表中不存在：

INSERT INTO target_table (col1, col2, col3)
SELECT source_col1, source_col2, source_col3
FROM source_table
EXCEPT
SELECT col1, col2, col3
FROM target_table;

1.3 SELECT INTO：快速复制表数据

1.3.1 基础示例

  在 SQL Server 中，SELECT INTO 和 INSERT INTO SELECT 都是用于将数据从一个表复制到另一个表，但SELECT INTO通常更快，它会自动创建一个新表，并将查询结果插入到该表中，适用于快速创建临时表或复制数据。SELECT INTO语法为：

SELECTselect_list		-- 要插入的列
INTOdestination		-- 新表的名称
FROMsource			-- 源表的名称
[WHERE condition];	-- 可选，用于指定要复制的行。

以下查询将 sales.customers 表的部分数据复制到另一个数据库 TestDb 中的 customers 表中：

CREATE DATABASE TestDb;
GOSELECTcustomer_id,first_name,last_name,email
INTOTestDb.dbo.customers
FROMsales.customers
WHEREstate = 'CA';

SELECT * 
FROM TestDb.dbo.customers;

在这里插入图片描述

1.3.2 SELECT INTO VS INSERT INTO SELECT
对比项SELECT INTOINSERT INTO SELECT
目标表要求目标表不存在，自动创建新表目标表必须已存在
语法示例SELECT * INTO new_table FROM old_tableINSERT INTO target_table SELECT * FROM source_table
表结构✅ 自动创建与查询结果相同的表结构❌ 需要提前创建匹配的表结构
约束/索引❌ 仅复制数据，不复制索引、约束等✅ 数据插入时会受目标表的约束和索引影响
WHERE 条件✅ 支持，可筛选数据插入✅ 可筛选数据插入
TOP 子句❌ 不支持（需改用 WHERE 或子查询）✅ 支持
日志记录方式最小日志（Minimal Logging），性能更高完整日志（Full Logging），性能较低
事务影响自动提交（除非显式使用事务）受事务控制（可回滚）
适用场景快速备份、创建临时表、数据迁移向已有表追加数据、ETL 数据加载
  • SELECT INTO 适合创建新表并一次性导入数据（如备份、临时表）。
  • INSERT INTO SELECT 适合向已有表追加数据（如数据合并、ETL 过程），支持TOP子句，更加灵活。

1.4 DELETE：删除行数据

在SQL Server中，DELETE语句用于从表中删除一行或多行数据，其语法如下：

DELETE [TOP (expression) [PERCENT]]
FROM table_name
[WHERE search_condition];
  • DELETE：指定要执行删除操作。
  • TOP (expression) [PERCENT]：可选子句，用于指定删除的行数或百分比。例如，TOP (10)表示删除10行，TOP (10) PERCENT表示删除10%的行。
  • FROM table_name：指定要删除数据的表名。
  • WHERE search_condition：可选子句，用于指定删除条件。如果不指定WHERE子句，则会删除表中的所有行。
DELETE FROM target_table;						-- 删除整个表
DELETE TOP 10 FROM target_table;  				-- 删除前10行
DELETE TOP 10 PERCENT FROM target_table;		-- 删除前10%的行

如果需要快速清空一个大表，建议使用TRUNCATE TABLE语句，它比DELETE语句更高效：

TRUNCATE TABLE target_table;
1.4.1 随机删除行

为了更好地理解DELETE语句的使用，我们首先创建一个名为production.product_history的表，并从production.products表中复制数据：

SELECT *
INTO production.product_history
FROM production.products;

查询production.product_history表中的所有行（一共321行）：

SELECT * FROM production.product_history;

以下DELETE语句删除production.product_history表中的21行随机数据：

DELETE TOP (21)
FROM production.product_history;

(21 rows affected)
1.4.2 删除条件行

以下DELETE语句删除production.product_history表中model_year为2017的所有行：

DELETE
FROM production.product_history
WHERE model_year = 2017;

(75 rows affected)

这表示成功删除了75行符合条件的数据。

1.4.3 删除表中指定列

DELETE操作会移除整行数据，如果你希望“删除”特定列的数据，可以通过以下两种方式实现：

  1. 将特定列的值设置为NULL或默认值
    如果你只是想清空某个列的值，而不是删除整行数据，可以使用UPDATE语句将该列的值设置为NULL（如果该列允许NULL值）或某个默认值。

    -- 清空phone列的所有值
UPDATE employees
SET phone = NULL;

    如果某些列不允许NULL值，可以将其设置为默认值：

    UPDATE employees
SET phone = '';
-- 或者是SET phone = DEFAULT;

  2. 逻辑删除
    如果你希望保留行的存在，但让某些列的数据“不可用”（比如employees表中某些行的phone列），可以通过添加一个标志列（如is_deleted列）来实现逻辑删除。例如，可以添加一个，用于标记某行是否被“删除”。

    -- 1. 添加标志列
ALTER TABLE employees
ADD is_phone_deleted BIT DEFAULT 0;

    -- 更新标志列
UPDATE employees
SET is_phone_deleted = 1
WHERE phone IS NOT NULL;

    -- 查询时，通过`WHERE`子句过滤掉被逻辑删除的行
SELECT id, name, email, phone
FROM employees
WHERE is_phone_deleted = 0;

注意事项：

  1. 数据完整性：在将列值设置为NULL或默认值之前，确保这不会违反表的完整性约束（例如，某些列可能不允许NULL值）。
  2. 逻辑删除的维护：逻辑删除需要额外的维护，例如在查询时始终需要考虑标志列的状态。
  3. 性能影响：频繁更新大量数据可能会对性能产生影响，尤其是在大表中。

1.5 UPDATE：更新表数据

UPDATE语句是用于更新（修改）表值的数据，其语法如下：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name ：要更新的表名。
  • SET 子句：用于指定要更新的列及其新值。
  • WHERE 子句用于指定更新的条件，不指定则更新表中所有行。

注意事项:

  1. 备份数据：在执行UPDATE语句之前，建议备份相关数据，以防误操作导致数据丢失。
  2. 使用WHERE子句：尽量使用WHERE子句指定更新条件，避免无条件更新整个表。
  3. 验证更新结果：执行UPDATE语句后，可以通过SELECT语句验证更新结果是否符合预期。
1.5.1 更新整列数据

为了更好地理解UPDATE语句的使用，我们首先创建一个示例表sales.taxes，并插入一些数据：

CREATE TABLE sales.taxes (tax_id INT PRIMARY KEY IDENTITY (1, 1),state VARCHAR (50) NOT NULL UNIQUE,state_tax_rate DEC (3, 2),avg_local_tax_rate DEC (3, 2),combined_rate AS state_tax_rate + avg_local_tax_rate,max_local_tax_rate DEC (3, 2),updated_at datetime
);INSERT INTO sales.taxes(state, state_tax_rate, avg_local_tax_rate, max_local_tax_rate)
VALUES
('Alabama', 0.04, 0.05, 0.07),
('Alaska', 0, 0.01, 0.07),
('Arizona', 0.05, 0.02, 0.05),
('Arkansas', 0.06, 0.02, 0.05),
('California', 0.07, 0.01, 0.02),
('Colorado', 0.02, 0.04, 0.08),
('Connecticut', 0.06, 0, 0),
('Delaware', 0, 0, 0),
('Florida', 0.06, 0, 0.02),
('Georgia', 0.04, 0.03, 0.04),
('Hawaii', 0.04, 0, 0),
('Idaho', 0.06, 0, 0.03),
('Illinois', 0.06, 0.02, 0.04),
('Indiana', 0.07, 0, 0),
('Iowa', 0.06, 0, 0.01),
('Kansas', 0.06, 0.02, 0.04),
('Kentucky', 0.06, 0, 0),
('Louisiana', 0.05, 0.04, 0.07),
('Maine', 0.05, 0, 0),
('Maryland', 0.06, 0, 0),
('Massachusetts', 0.06, 0, 0),
('Michigan', 0.06, 0, 0),
('Minnesota', 0.06, 0, 0.01),
('Mississippi', 0.07, 0, 0.01),
('Missouri', 0.04, 0.03, 0.05),
('Montana', 0, 0, 0),
('Nebraska', 0.05, 0.01, 0.02),
('Nevada', 0.06, 0.01, 0.01),
('New Hampshire', 0, 0, 0),
('New Jersey', 0.06, 0, 0),
('New Mexico', 0.05, 0.02, 0.03),
('New York', 0.04, 0.04, 0.04),
('North Carolina', 0.04, 0.02, 0.02),
('North Dakota', 0.05, 0.01, 0.03),
('Ohio', 0.05, 0.01, 0.02),
('Oklahoma', 0.04, 0.04, 0.06),
('Oregon', 0, 0, 0),
('Pennsylvania', 0.06, 0, 0.02),
('Rhode Island', 0.07, 0, 0),
('South Carolina', 0.06, 0.01, 0.02),
('South Dakota', 0.04, 0.01, 0.04),
('Tennessee', 0.07, 0.02, 0.02),
('Texas', 0.06, 0.01, 0.02),
('Utah', 0.05, 0, 0.02),
('Vermont', 0.06, 0, 0.01),
('Virginia', 0.05, 0, 0),
('Washington', 0.06, 0.02, 0.03),
('West Virginia', 0.06, 0, 0.01),
('Wisconsin', 0.05, 0, 0.01),
('Wyoming', 0.04, 0.01, 0.02),
('D.C.', 0.05, 0, 0);

假设我们需要将taxes表中的updated_at列更新为系统当前日期和时间，可以使用以下UPDATE语句：

UPDATE sales.taxes
SET updated_at = GETDATE();

(51 rows affected)
1.5.2 使用条件更新部分行

  在实际应用中，我们通常需要根据特定条件更新数据。例如，我们希望将max_local_tax_rate为1%的州的max_local_tax_rate增加2%，同时将这些州的avg_local_tax_rate增加1%。可以使用以下语句：

UPDATE sales.taxes
SET max_local_tax_rate = max_local_tax_rate + 0.02,avg_local_tax_rate = avg_local_tax_rate + 0.01
WHERE max_local_tax_rate = 0.01;

执行该语句后，只有满足条件的行会被更新：

(7 rows affected)

1.6 UPDATE JOIN：跨表更新

  在SQL Server中，UPDATE JOIN用于跨表更新数据，通过结合JOIN子句，可以实现基于多个表的数据更新，其基本语法如下：

UPDATEt1
SETt1.c1 = t2.c2,t1.c2 = expression,...
FROMt1[INNER | LEFT] JOIN t2 ON join_predicate
WHEREwhere_predicate;
  • t1：需要更新的目标表。
  • t2：用于更新的源表。
  • join_predicate：指定连接条件，用于确定JOIN操作的匹配规则。
  • where_predicate：可选的WHERE子句，用于进一步筛选需要更新的行。

注意事项：

  1. 数据一致性：在执行UPDATE JOIN操作时，务必确保连接条件和更新逻辑的正确性，以避免数据不一致。
  2. 备份数据：在执行跨表更新之前，建议备份相关数据，以防误操作导致数据丢失。
  3. 性能优化：对于大规模数据更新，建议在执行前评估性能影响，并考虑使用索引优化查询。
1.6.1 设置样本表

为了更好地理解UPDATE JOIN的使用，我们首先创建两个示例表：sales.targets和sales.commissions。

  1. sales.targets表存储销售目标及其对应的佣金比例：

    DROP TABLE IF EXISTS sales.targets;CREATE TABLE sales.targets
(target_id  INT PRIMARY KEY,percentage DECIMAL(4, 2) NOT NULL DEFAULT 0
);INSERT INTOsales.targets(target_id, percentage)
VALUES(1, 0.2),(2, 0.3),(3, 0.5),(4, 0.6),(5, 0.8);

    target_id	  percentage1			0.202			0.303			0.504			0.605			0.80

  2. sales.commissions表存储销售人员的ID、销售目标ID、基础销售额和佣金：

    CREATE TABLE sales.commissions
(staff_id    INT PRIMARY KEY,target_id   INT,base_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,commission  DECIMAL(10, 2) NOT NULL DEFAULT 0,FOREIGN KEY(target_id) REFERENCES sales.targets(target_id)
);INSERT INTOsales.commissions(staff_id, base_amount, target_id)
VALUES(1, 100000, 2),(2, 120000, 1),(3, 80000, 3),(4, 900000, 4),(5, 950000, 5);

    staff_id	target_id	base_amount		commission1			2		 100000.00		  0.002			1		 120000.00		  0.003			3		 80000.00		  0.004			4		 900000.00		  0.005			5		 950000.00		  0.00
1.6.2 UPDATE INNER JOIN示例

假设我们需要根据sales.targets表中的目标百分比计算sales.commissions表中销售人员的佣金。可以使用UPDATE INNER JOIN语句：

UPDATEsales.commissions
SETsales.commissions.commission = c.base_amount * t.percentage
FROMsales.commissions cINNER JOIN sales.targets t ON c.target_id = t.target_id;

执行该语句后，sales.commissions表中的commission列将根据sales.targets表中的percentage值进行更新：

staff_id	target_id	base_amount		commission1			2		 100000.00		  30000.002			1		 120000.00		  24000.003			3		 80000.00		  40000.004			4		 900000.00		  540000.005			5		 950000.00		  760000.00
1.6.3 UPDATE LEFT JOIN示例

假设我们新增了两名销售人员，但尚未分配销售目标：

INSERT INTOsales.commissions(staff_id, base_amount, target_id)
VALUES(6, 100000, NULL),(7, 120000, NULL);

为了给这些新员工设置默认的佣金比例（例如10%），可以使用UPDATE LEFT JOIN语句：

UPDATEsales.commissions
SETsales.commissions.commission = c.base_amount * COALESCE(t.percentage, 0.1)
FROMsales.commissions cLEFT JOIN sales.targets t ON c.target_id = t.target_id;
  • COALESCE(t.percentage, 0.1)：如果percentage为NULL，则使用默认值0.1。
  • 该语句会更新所有销售人员的佣金，包括那些尚未分配目标的员工。

更新后的sales.commissions表如下：

staff_id	target_id	base_amount		commission1			2		 100000.00		  30000.002			1		 120000.00		  24000.003			3		 80000.00		  40000.004			4		 900000.00		  540000.005			5		 950000.00		  760000.006			NULL	 100000.00		  10000.007			NULL     120000.00		  12000.00

1.7 MERGE：同步表数据

1.7.1 SQL Server MERGE语句简介

  MERGE语句用于将一个源表（source table）的数据同步到目标表（target table）中，根据源表和目标表之间的匹配情况，MERGE语句可以执行以下三种操作：

  1. 插入（insert）：源表中有而目标表中没有的行（NOT MATCHED，下图橘色部分），将被插入到目标表中。
  2. 更新（update）：源表和目标表中都有匹配的行（MATCHED，下图蓝色部分），但某些列的值不同，将更新目标表中的行。
  3. 删除（ delete）：目标表中有而源表中没有的行（NOT MATCHED BY SOURCE，绿色部分），将从目标表中删除。

在这里插入图片描述

MERGE 语句能够同时完成插入、更新和删除操作，极大地简化了数据同步过程。以下是MERGE语句的基本语法：

MERGE target_table AS target
USING source_table AS source
ON merge_condition
WHEN MATCHEDTHEN UPDATE SET target.column1 = source.column1, target.column2 = source.column2, ...
WHEN NOT MATCHED BY TARGETTHEN INSERT (column1, column2, ...)VALUES (source.column1, source.column2, ...)
WHEN NOT MATCHED BY SOURCETHEN DELETE;
  • target_table：需要更新的目标表。
  • source_table：提供数据的源表。
  • merge_condition：用于匹配源表和目标表的条件，通常是基于主键或唯一键。
  • WHEN MATCHED：当源表和目标表中的行匹配时，执行更新操作。
  • WHEN NOT MATCHED BY TARGET：当源表中有而目标表中没有的行时，执行插入操作。
  • WHEN NOT MATCHED BY SOURCE：当目标表中有而源表中没有的行时，执行删除操作。
1.7.2 简单示例

为了更好地理解MERGE语句的使用，我们首先创建两个表：sales.category和sales.category_staging。

  1. sales.category表存储产品类别的基本信息：

    CREATE TABLE sales.category (category_id INT PRIMARY KEY,category_name VARCHAR(255) NOT NULL,amount DECIMAL(10, 2)
);INSERT INTO sales.category(category_id, category_name, amount)
VALUES(1, 'Children Bicycles', 15000),(2, 'Comfort Bicycles', 25000),(3, 'Cruisers Bicycles', 13000),(4, 'Cyclocross Bicycles', 10000);

  2. sales.category_staging表存储待同步的产品类别信息：

    CREATE TABLE sales.category_staging (category_id INT PRIMARY KEY,category_name VARCHAR(255) NOT NULL,amount DECIMAL(10, 2)
);INSERT INTO sales.category_staging(category_id, category_name, amount)
VALUES(1, 'Children Bicycles', 15000),(3, 'Cruisers Bicycles', 13000),(4, 'Cyclocross Bicycles', 20000),(5, 'Electric Bikes', 10000),(6, 'Mountain Bikes', 10000);

  3. 使用MERGE语句，将sales.category_staging表中的数据同步到sales.category表中：

    MERGE sales.category AS target
USING sales.category_staging AS source
ON (source.category_id = target.category_id)
WHEN MATCHEDTHEN UPDATE SETtarget.category_name = source.category_name,target.amount = source.amount
WHEN NOT MATCHED BY TARGETTHEN INSERT (category_id, category_name, amount)VALUES (source.category_id, source.category_name, source.amount)
WHEN NOT MATCHED BY SOURCETHEN DELETE;
    • ON (source.category_id = target.category_id)：指定匹配条件，基于category_id列。
    • WHEN MATCHED：当源表和目标表中的行匹配时，更新目标表中的category_name和amount列。
    • WHEN NOT MATCHED BY TARGET：当源表中有而目标表中没有的行时，将这些行插入到目标表中。
    • WHEN NOT MATCHED BY SOURCE：当目标表中有而源表中没有的行时，从目标表中删除这些行。

执行MERGE语句后，sales.category表中的数据将与sales.category_staging表中的数据完全同步：

在这里插入图片描述

二、数据类型

数据类型查询语句说明数据类型查询语句说明
数据类型概述SQL Server data types介绍SQL Server内置数据类型。位数据类型BIT在数据库中存储位数据（0、1或NULL）。
整数类型INT了解SQL Server中的各种整数类型，包括BIGINT、INT、SMALLINT和TINYINT。精确数值类型DECIMAL使用DECIMAL或NUMERIC数据类型在数据库中存储精确数值。
固定长度字符CHAR在数据库中存储固定长度的非Unicode字符字符串。固定长度Unicode字符NCHAR存储固定长度的Unicode字符字符串，解释CHAR和NCHAR数据类型的区别。
可变长度字符VARCHAR在数据库中存储可变长度的非Unicode字符串数据。可变长度Unicode字符NVARCHAR在表中存储可变长度的Unicode字符串数据，了解VARCHAR和NVARCHAR的主要区别。
日期时间类型DATETIME2在数据库中存储日期和时间数据。日期类型DATE讨论日期数据类型以及如何在表中存储日期。
时间类型TIME使用TIME数据类型在数据库中存储时间数据。带时区的日期时间DATETIMEOFFSET操作带有时区的日期时间。
唯一标识符GUID了解GUID以及如何使用NEWID()函数生成GUID值。XML 数据类型XML存储XML数据。
JSON 数据类型JSON存储JSON数据。

2.1 数据类型概览

在 SQL Server 中，数据类型（Data Type） 是定义列（Column）、变量（Variable）和参数（Parameter）所持有数据类型的属性。它决定了：

  1. 数据存储格式（整数、字符、日期等）和 存储空间占用
  2. 查询性能和系统可靠性
  3. 数据精度与范围
  4. 参与计算的规则
-- 示例：列定义中的数据类型
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,FirstName NVARCHAR(50),HireDate DATE,Salary DECIMAL(10,2)
);
2.1.1 精确数值类型（Exact Numerics）

精确数值数据类型用于存储确切的数值，如整数、小数和货币金额。这些数据类型在需要精确计算的场景中非常有用，例如财务和科学计算。

类型范围存储字节应用场景
bigint−2^63 (−9,223,372, 036,854,775,808) 到2^63−1 (9,223,372, 036,854,775,807)8 bytes大型ID、天文数字计算
int−2^31 (−2,147, 483,648) 到2^31−1 (−2,147, 483,647)4 bytes常规整数（主键常用）
smallint−2^15 (−32,767) 到2^15 (−32,768)2 bytes小型计数
tinyint0 到2551 byte年龄、状态码
bit0 、 1 或NULL1 byte布尔值（真/假）
decimal（numeric）−10^38+1 到 10^381−15 到 17 bytes货币、高精度计算
money−922,337, 203, 685,477.5808 到 +922,337, 203, 685,477.58078 bytes货币金额（兼容旧系统）
smallmoney−214,478.3648 到+214,478.36474 bytes同上

最佳实践：优先使用 decimal 替代 money 以保证跨平台兼容性。

2.1.2 近似数值类型（Approximate Numerics）

存储浮点数，适用于科学计算（可能损失精度）。

类型存储范围精度
float(n)4/8字节±1.79E+3087-15位有效数字
real4字节等同于 float(24)，±3.40E+387位有效数字

⚠️ 注意：避免在财务计算中使用浮点类型，因精度问题可能导致舍入错误。

2.1.3 日期与时间类型（Date & Time）
类型存储大小精度最小值最大值
datetime（遗留系统）8 bytes3.33毫秒1753-01-019999-12-31
smalldatetime4 bytes, fixed1分钟1900-01-012079-06-06
date3 bytes, fixed1 天0001-01-019999-12-31
time5 bytes100纳秒00:00:00.000000023:59:59.9999999
datetimeoffset10 bytes100纳秒0001-01-019999-12-31
datetime26 bytes100纳秒0001-01-019999-12-31

  在开发新应用时，建议使用 time、date、datetime2（ 替代 datetime） 和 datetimeoffset 数据类型，因为这些类型符合 SQL 标准，更具可移植性。此外，time、datetime2 和 datetimeoffset 提供更高的秒精度，datetimeoffset 还支持时区。

2.1.4 字符与二进制类型

  字符串数据类型用于存储字符数据，包括固定长度和可变长度的字符串。这些数据类型在存储文本信息时非常有用。以下是字符串数据类型的具体介绍：

  1. 非 Unicode 字符串
类型长度范围存储状态
char(n)0到8,000字符固定长度 n 字节定长数据使用
varchar(n)0到 8,000字符实际长度 + 2 字节推荐
varchar(max)0到2³¹字符实际长度 + 2 字节推荐（替代text）
text0到2,147,483,647 字符 （2GB）实际长度 + 4 字节❌ 已弃用
  1. Unicode 字符串（支持多种语言和字符集）
类型长度范围存储状态
nchar(n)0到4,000字符固定长度 n x 2 字节定长Unicode数据
nvarchar(n)0到4,000字符实际长度 x2 + 2字节推荐
nvarchar(max)0到2³⁰字符实际长度 x2 + 2字节推荐（替代ntext）
ntext0到1,073,741,823 字符（1GB）实际长度 x2❌ 已弃用
  1. 二进制数据
类型最大长度应用场景
binary(n)8,000字节固定长度二进制
varbinary(n)8,000字节小型图片、文档
varbinary(max)2GB大型文件（替代image）
image2GB❌ 已弃用
  1. 始终使用 varchar(max)/nvarchar(max)/varbinary(max) 替代弃用的 text/ntext/image
  2. 非拉丁字符（如中文）必须使用 nvarchar 避免乱码
  1. 特殊数据类型：
类型用途示例
cursor用于存储游标变量或存储过程的 OUTPUT 参数，包含对游标的引用
uniqueidentifier存储 16 字节的全局唯一标识符（GUID）。NEWID() 生成唯一值
xml存储XML文档<employee><name>John</name></employee>
hierarchyid表示树形结构位置组织架构、文件路径
geometry平面坐标系空间数据（GIS）地图坐标点、多边形
geography椭球体空间数据（GPS坐标）经纬度位置
table临时存储结果集存储过程返回数据集
sql_variant存储除text/ntext/image外的任意类型动态数据类型列（慎用）
2.1.5 最佳实践

  1. 精确性原则

    • 财务计算：用 decimal 而非 float
    • 时间戳：用 datetime2 而非 datetime

  2. 存储优化

    • 定长数据（如身份证号）：用 char(18)
    • 变长数据（如地址）：用 varchar 节省空间

  3. 国际化支持

    • 多语言系统：必须使用 nvarchar 存储文本

2.2 整数类型详解

2.2.1 整数数据类型概述

SQL Server 提供了四种标准的整数数据类型，每种类型都有其特定的存储大小和范围：

数据类型范围存储大小
BIGINT-2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807)8 字节
INT-2^31 (-2,147,483,648) 到 2^31-1 (2,147,483,647)4 字节
SMALLINT-2^15 (-32,768) 到 2^15-1 (32,767)2 字节
TINYINT0 到 2551 字节

  选择合适的数据类型不仅可以节省存储空间，还可以提高查询性能。以下是一个示例，展示如何在 SQL Server 中创建一个包含四种整数列的表，并插入最大值。

  1. 创建表

    CREATE TABLE test.sql_server_integers (bigint_col BIGINT,int_col INT,smallint_col SMALLINT,tinyint_col TINYINT
);

  2. 插入数据

    INSERT INTO test.sql_server_integers (bigint_col,int_col,smallint_col,tinyint_col)
VALUES(9223372036854775807,2147483647,32767,255);

  3. 查询数据

    SELECT bigint_col,int_col,smallint_col,tinyint_col
FROM test.sql_server_integers;
2.2.2 整数类型数据转换

  SQL Server 在处理整数常量时，会根据其值的大小自动选择合适的数据类型。例如，当整数常量大于 INT 的最大值（2,147,483,647）时，SQL Server 会将其转换为 DECIMAL 数据类型，而不是 BIGINT 数据类型。以下是一个示例：

SELECT 2147483647 / 3 AS r1,2147483649 / 3 AS r2;

在上述查询中，当值超过 INT 的最大值时，结果的数据类型从 INT 转换为 DECIMAL。

2.3 DECIMAL 数据类型

DECIMAL 数据类型用于存储具有固定精度和小数位数的数值，非常适合需要精确计算的场景（如金融和科学计算），其语法如下：

DECIMAL(p, s)
  • p（精度）：表示存储的数字的最大总位数，包括小数点左侧和右侧的数字。精度的范围是 1 到 38，默认值为 38。
  • s（小数位数）：表示小数点右侧的数字位数。小数位数的范围是 0 到 p（精度）。只有指定了精度时，才能指定小数位数。默认值为 0。

根据精度的不同，DECIMAL 数据类型的存储大小如下表所示：

精度范围存储大小（字节）
1 – 95
10 – 199
20 – 2813
29 – 3817

NUMERIC 和 DECIMAL 是同义词，可以互换使用。以下声明是等价的：

-- 精度为 10 位，小数位 2 位，即 ±99999999.99
DECIMAL(10, 2)
NUMERIC(10, 2)

此外，DECIMAL 的 ISO 同义词是 DEC 和 DEC(p, s)，因此可以使用 DECIMAL 或 DEC：

-- 精度为 5 位，小数位 3 位，即 ±99.999
DECIMAL(5, 3)
DEC(5, 3)

以下是一个示例，展示如何在 SQL Server 中使用 DECIMAL 和 NUMERIC 数据类型。

  1. 创建表：创建一个包含两个列的表，分别使用 DECIMAL 数据类型和NUMERIC 数据类型。

    CREATE TABLE test.sql_server_decimal (dec_col DECIMAL(4, 2),num_col NUMERIC(4, 2)
);

  2. 插入数据

    INSERT INTO test.sql_server_decimal (dec_col, num_col)
VALUES(10.05, 20.05);

  3. 查询数据

    SELECTdec_col,num_col
FROMtest.sql_server_decimal;

  4. 插入超出精度和小数位数的数据：尝试插入一行超出列定义的精度和小数位数的数据：

    INSERT INTO test.sql_server_decimal (dec_col, num_col)
VALUES(99.999, 12.345);

    SQL Server 会抛出错误并终止该语句：

    Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

2.4 BIT 数据类型

2.4.1 BIT 类型概述

SQL Server 中的 BIT 是一种特殊的整数数据类型，专门用于存储布尔值（真/假）。它可以接受以下三种值：

  • 0（表示假/否）
  • 1（表示真/是）
  • NULL（表示未知或未定义）

BIT 类型具有自动转换特性：

  1. 字符串转换：'TRUE' → 1，'FALSE' → 0

  2. 数值转换： 任何非零数值 → 1，零值 → 0

  3. 隐式转换：在布尔表达式中，SQL Server 会自动将 BIT 值视为逻辑值

SQL Server 对 BIT 列进行了存储优化，单个 BIT 列实际上不占用完整的 1 个字节，而是多个 BIT 列共享字节空间（比使用 TINYINT 或 CHAR(1) 更节省空间）。

  • 1-8 个 BIT 列：共占用 1 个字节
  • 9-16 个 BIT 列：共占用 2 个字节
  • 17-24 个 BIT 列：共占用 3 个字节
  • 以此类推…

BIT 类型特别适合以下场景：

  1. 开关标志：如 IsActive、IsDeleted 等状态标志
  2. 选项开关：如用户设置中的各种选项
  3. 权限标记：如 HasReadAccess、HasWriteAccess 等
  4. 布尔条件：任何需要是/否、真/假语义的字段
2.4.2 应用示例

  1. 创建表

    CREATE TABLE EmployeeStatus (EmployeeID INT PRIMARY KEY,IsActive BIT,           -- 是否在职IsFullTime BIT,         -- 是否全职HasHealthInsurance BIT  -- 是否有医疗保险
);

  2. 插入数据

    -- 插入布尔值
INSERT INTO EmployeeStatus VALUES (1, 1, 0, 1); -- 第1位员工：在职、全职、有保险-- 使用字符串插入
INSERT INTO EmployeeStatus VALUES (2, 'TRUE', 'FALSE', 'TRUE');-- 使用数值插入
INSERT INTO EmployeeStatus VALUES (3, 0, 1, 0.5); -- 0.5会被转换为1

  3. 查询示例

    -- 查询所有全职员工
SELECT EmployeeID 
FROM EmployeeStatus 
WHERE IsFullTime = 1;-- 简写形式（BIT可直接作为布尔条件）
SELECT EmployeeID 
FROM EmployeeStatus 
WHERE IsFullTime;
2.4.3 注意事项

  1. 索引优化：BIT 列可以创建索引，但因其基数低（只有3种可能值），通常不是好的索引候选

  2. 默认值：建议为 BIT 列设置默认值以避免 NULL 处理

    CREATE TABLE Example (Flag BIT DEFAULT 0
);

  3. 聚合函数：对 BIT 列使用聚合函数时，SQL Server 会将其视为整数

    SELECT AVG(CAST(IsActive AS INT)) AS ActiveRate FROM EmployeeStatus

  4. 与其它系统的兼容性：某些应用程序可能将 BIT 映射为特定布尔类型

  5. 性能考虑

    • 多个 BIT 列共享存储空间，比使用 TINYINT 或 CHAR(1) 更节省空间
    • 在 WHERE 子句中使用 BIT 列时，查询优化器能高效处理
    • 避免在 BIT 列上使用复杂计算，这可能导致类型转换影响性能
数据类型存储需求可读性适用场景
BIT1位/列中等纯布尔值
TINYINT1字节高需要多状态（如0/1/2）
CHAR(1)1字节高需要存储’Y’/'N’等字符形式

  SQL Server 的 BIT 数据类型是存储布尔值的高效选择，具有存储优化和自动类型转换特性。在设计数据库时，对于明确的二值状态字段，应优先考虑使用 BIT 类型而非其他替代方案，既能保证语义清晰又能优化存储空间。

2.5 CHAR 数据类型（固定长度的非 Unicode 字符串）

2.5.1 CHAR 概述

  在 SQL Server 中，CHAR 数据类型用于存储固定长度的非 Unicode 字符串（英文/数字等ASCII字符），其语法如下：

CHAR(n)

n：指定字符串的长度，范围从 1 到 8,000，未指定时默认值为 1。

  1. 如果插入时，字符串的长度小于n，SQL Server 会在字符串的末尾添加空格补齐长度n。当查询该字符串值时，SQL Server 会去掉末尾的空格再返回结果。
  2. 如果插入的值的长度大于n，则会报错。

  CHAR的优势是固定长度便于SQL Server预测和优化存储，读取性能通常优于VARCHAR（无需计算实际长度），更新时不会引起页拆分（行迁移）。限制是最长存储8000字符。其常见使用场景为：

  1. 长度固定的编码字段

    • 身份证号（CHAR(18)）
    • 邮政编码（CHAR(6)）
    • 银行卡号（CHAR(19)）

  2. 标准化代码

    • 国家代码（CHAR(2) 如 ‘CN’, ‘US’）
    • 货币代码（CHAR(3) 如 ‘USD’, ‘CNY’）
    • 状态代码（CHAR(1) 如 ‘A’-活跃, ‘I’-不活跃）

  3. 与其他系统对接

    • 固定宽度文本文件导入/导出
    • 传统系统接口数据交换
2.5.2 使用示例

  1. 测试插入短于列长度的字符串：

    -- 创建测试表
CREATE TABLE CharTest (FixedCode CHAR(10),Description VARCHAR(20)
);-- 插入短于定义长度的数据
INSERT INTO CharTest VALUES ('ABC', '3字符长度');
INSERT INTO CharTest VALUES ('ABCDEFGHIJ', '10字符长度');-- 查看结果
SELECT FixedCode,Description,LEN(FixedCode) AS 逻辑长度,  			-- 返回非空格字符数DATALENGTH(FixedCode) AS 物理存储字节数,	-- 使用DATALENGTH返回实际存储的字节数'|' + FixedCode + '|' AS 可视化边界 		-- 显示尾部空格
FROM CharTest;

    在这里插入图片描述

  2. 如果 插入超过列长度的字符串，SQL Server 抛出错误：

    String or binary data would be truncated.The statement has been terminated.

  3. LIKE 操作不受空格影响

    SELECT * FROM CharTest WHERE FixedCode LIKE 'A%'; -- 能匹配

  4. 在 CHAR 列上创建索引时，实际是按补空格后的值索引：

    CREATE INDEX IX_ProductCode ON Products(ProductCode);
2.5.3 比较时忽略尾部空格
-- 准备测试数据
DECLARE @char1 CHAR(5) = 'SQL';
DECLARE @char2 CHAR(5) = 'SQL  ';
DECLARE @char3 CHAR(5) = 'SQL  ';
DECLARE @varchar1 VARCHAR(5) = 'SQL';-- 比较测试
SELECT CASE WHEN @char1 = @char2 THEN '相等' ELSE '不等' END AS CHAR比较1,CASE WHEN @char2 = @char3 THEN '相等' ELSE '不等' END AS CHAR比较2,CASE WHEN @char1 = @varchar1 THEN '相等' ELSE '不等' END AS CHAR与VARCHAR比较,CASE WHEN @char1 = 'SQL' THEN '相等' ELSE '不等' END AS 与短字符串比较;

在这里插入图片描述
所有比较都返回 “相等”，证明 SQL Server 在比较 CHAR 类型时会忽略尾部空格，所以等值比较时可能导致意外匹配。与 VARCHAR 混用时，也可能可能意外匹配更多记录：

-- 混合比较示例
DECLARE @v_code VARCHAR(10) = 'ABC';
SELECT * FROM Products 
WHERE ProductCode = @v_code; -- 可能意外匹配更多记录

如需排除空格影响，可以使用 RTRIM移除字符串右侧的空格：

-- 安全做法：使用 RTRIM 
SELECT * FROM Products 
WHERE RTRIM(ProductCode) = 'ABC123'; -- 避免空格干扰

或者在插入数据时显式补齐空格，统一长度：

INSERT INTO Products (ProductCode, ProductName)
VALUES (RTRIM('ABC123') + SPACE(10 - LEN('ABC123')), 'Product A');
2.5.4 总结
操作类型CHAR 行为特征推荐处理方式
数据插入短于定义长度的值自动补空格确保业务数据符合定义长度
等值比较忽略尾部空格，可能导致意外匹配重要查询使用 RTRIM 或 CAST 二进制比较
LIKE 查询正常运作，不受填充空格影响无需特殊处理
索引使用按补空格后的值建立索引考虑使用 VARCHAR 替代或函数索引
存储过程参数传入的字符串参数会自动转换为 CHAR 长度参数明确定义长度或使用 VARCHAR 参数类型

2.6 NCHAR 数据类型（固定长度 Unicode 字符串）

NCHAR 数据类型用于存储固定长度的 Unicode 字符串数据。其语法为：

NCHAR(n)

NCHAR 的 ISO 同义词是 NATIONAL CHAR 和 NATIONAL CHARACTER，它们可以互换使用。

NCHAR 和 CHAR 数据类型都用于存储固定长度的字符字符串，但它们之间存在一些关键区别：

特性CHARNCHAR
存储字符类型非 Unicode 字符Unicode 字符（UCS-2 格式）
每个字符占用字节数1 字节2 字节
存储大小等于列定义或变量声明的大小等于列定义或变量声明大小的两倍
最大字符数80004000

以下是一个使用 NCHAR 数据类型的示例。

  1. 创建一个包含 NCHAR 列的新表：

    CREATE TABLE test.sql_server_nchar (val NCHAR(1) NOT NULL
);

  2. 向 NCHAR 列中插入一个日语字符（あ）：

    INSERT INTO test.sql_server_nchar (val)
VALUES(N'あ');

    注意，在插入 Unicode 字符串时，必须在字符串前面加上字母 N。否则，SQL Server 会将字符串转换为数据库的默认代码页，这可能会导致某些 Unicode 字符无法被识别。

  3. 插入一个长度大于列定义的字符串，SQL Server 会报错并终止语句。

    INSERT INTO test.sql_server_nchar (val)
VALUES(N'いえ');

    String or binary data would be truncated.The statement has been terminated.

  4. 使用 LEN 和 DATALENGTH 函数查看 val 列中值的字符数和字节数

    SELECTval,LEN(val) AS length,DATALENGTH(val) AS data_length
FROMtest.sql_server_nchar;

    在这里插入图片描述

2.7 VARCHAR 数据类型(可变长度的非 Unicode 字符串)

VARCHAR 数据类型用于存储可变长度的非 Unicode 字符串。其语法如下：

VARCHAR(n)

另一种声明 VARCHAR 列的方法是使用 VARCHAR(max) 语法：

VARCHAR(max)

  • n：指定字符串的最大长度，范围从 1 到 8,000。未指定时默认值为 1。一般来说，VARCHAR 值的存储大小是实际存储的数据长度加上 2 字节。

  • max：定义最大存储大小，为 2^31-1 字节（2 GB）。

VARCHAR 的 ISO 同义词是 CHARVARYING 或 CHARACTERVARYING，因此可以互换使用。

2.7.1 长度前缀

  长度前缀是 SQL Server 高效管理变长数据类型的核心机制之一，指的是存储 VARCHAR 数据时， SQL Server在数据内容前额外存储的 2 字节信息，用于记录该字符串的实际长度。长度前缀的主要作用有：

  1. 快速确定数据长度：无需扫描整个字符串就能知道其长度

  2. 高效存储管理：帮助存储引擎快速定位和操作变长数据

  3. 防止数据截断：确保读取时只获取属于该字段的数据

例如对于 VARCHAR(10) 列存储 “SQL Server”，实际情况是：

[长度前缀][实际数据]
0x000A   'S''Q''L'' ''S''e''r''v''e''r'

其中长度前缀0x000A共2字节，表示十进制 10。

2.7.2 简单示例
  1. 创建表：创建包含一个 VARCHAR 列的表，因为没有指定 val 列的字符串长度，其默认值为 1。
CREATE TABLE test.sql_server_varchar (val VARCHAR NOT NULL
);

  1. 修改列定义：如果需要修改 val 列的字符串长度，可以使用 ALTER TABLE ALTER COLUMN 语句：

    ALTER TABLE test.sql_server_varchar
ALTER COLUMN val VARCHAR(10) NOT NULL;

  2. 插入符合长度的数据，语句按预期工作

    INSERT INTO test.sql_server_varchar (val)
VALUES ('SQL Server');

  3. 插入超过列长度的数据，SQL Server 将抛出错误

    INSERT INTO test.sql_server_varchar (val)
VALUES ('SQL Server VARCHAR');

    String or binary data would be truncated.The statement has been terminated.

  4. 使用 LEN 和 DATALENGTH 函数查询字符数和存储的字节数

    SELECTval,LEN(val) AS len,DATALENGTH(val) AS data_length
FROMtest.sql_server_varchar;

    在这里插入图片描述

      由于长度前缀是 SQL Server 的内部实现细节，开发者通常更关心实际数据占用的空间，所以DATALENGTH()设计为只返回"数据内容"的字节数，故其返回的结果也是10。

2.7.3 最佳实践

  1. 明确指定长度：在定义 VARCHAR 列时，应明确指定长度，以避免默认值为 1 的问题。

    CREATE TABLE Products (ProductCode VARCHAR(10),ProductName VARCHAR(50)
);

  2. 使用 VARCHAR(max)：如果需要存储大量文本数据，可以使用 VARCHAR(max)，但需要注意性能和存储空间的平衡。

    CREATE TABLE Articles (ArticleID INT,Content VARCHAR(max)
);

  3. 查询时注意长度限制：在查询时，确保比较的字符串长度不会超过列的定义长度。

    SELECT * FROM Products 
WHERE ProductCode = LEFT('ABC123', 10);

  4. 数据插入和更新时的长度检查：在插入或更新数据时，确保数据的长度不会超过列的定义长度，以避免截断错误。

    INSERT INTO Products (ProductCode, ProductName)
VALUES (LEFT('ABC123', 10), 'Product A');

2.8 NVARCHAR 数据类型

NVARCHAR 数据类型用于存储可变长度的 Unicode 字符串，其语法为：

NVARCHAR(n)

也可以使用使用 NVARCHAR(max) 语法：

NVARCHAR(max)
  • n：指定字符串的最大长度，范围从 1 到 4,000。未指定则默认值为 1。
  • max：定义最大存储大小，为 2^31-1 字节（2 GB）。

以下表格展示了 VARCHAR 和 NVARCHAR 数据类型的主要区别：

特性VARCHARNVARCHAR
字符数据类型可变长度，非 Unicode 字符可变长度，支持 Unicode 和非 Unicode 字符（如日语、韩语和中文）
最大长度最多 8,000 个字符最多 4,000 个字符
字符大小每个字符占用 1 个字节每个 Unicode/非 Unicode 字符占用 2 个字节
存储大小实际长度+2字节实际长度的两倍+2字节
使用场景适用于数据长度可变或实际数据远小于容量的情况仅在需要 Unicode 支持（如中日语韩等语言）时使用
-- 创建表格并插入数据
CREATE TABLE test.sql_server_nvarchar (val NVARCHAR(10) NOT NULL
);INSERT INTO test.sql_server_nvarchar (val)
VALUES (N'こんにちは');

如果插入超过列长度的数据，也会报错：

String or binary data would be truncated.The statement has been terminated.

-- 查询字符数和存储大小
SELECTval,LEN(val) AS len,DATALENGTH(val) AS data_length
FROMtest.sql_server_nvarchar;

在这里插入图片描述

2.9 DATETIME2 数据类型

2.9.1 DATETIME2 概述

DATETIME2 数据类型用于存储日期和时间数据。其语法如下：

DATETIME2(fractional seconds precision)

  • fractional seconds precision：可选参数，表示小数秒的精度，范围从 0 到 7。如果不指定，默认值为 7。

  • 存储大小：DATETIME2 值的存储大小取决于小数秒的精度。精度小于 3 时，需要 6 字节；精度在 3 到 4 之间时，需要 7 字节；其他精度需要 8 字节。

  • 范围：DATETIME2 包含两个部分：日期和时间。

    • 日期：范围从 0001-01-01 到 9999-12-31。
    • 时间：范围从 00:00:00 到 23:59:59.9999999。

DATETIME2 的默认字符串字面量格式如下：

YYYY-MM-DD hh:mm:ss[.fractional seconds]

例如2023-04-05 14:30:25.1234567表示 2023 年 4 月 5 日下午 2 点 30 分 25 秒，小数秒为 1234567。

组成部分数字位数说明范围组成部分数字位数说明范围
年 (YYYY)4位表示年份0001 到 9999月 (MM)2位表示月份01 到 12
日 (DD)2位表示日期01 到 31小时 (hh)2位表示小时，24 小时制00 到 23
分钟 (mm)2位表示分钟00 到 59秒 (ss)2位表示秒00 到 59
fractional seconds0-7位表示小数秒0 到 9999999
2.9.2 使用示例

首先，创建一个包含一个 DATETIME2 列的表：

CREATE TABLE production.product_colors (color_id INT PRIMARY KEY IDENTITY,color_name VARCHAR(50) NOT NULL,created_at DATETIME2
);

  1. 插入当前日期和时间：

    INSERT INTO production.product_colors (color_name, created_at)
VALUES('Red', GETDATE());

    GETDATE() 函数类似于其他数据库系统（如 MySQL）中的 NOW() 函数。

  2. 插入字面量值

    INSERT INTO production.product_colors (color_name, created_at)
VALUES('Green', '2018-06-23 07:30:20');

  3. 设置默认值：如果希望 created_at 列的默认值为当前日期和时间，可以使用以下 ALTER TABLE 语句：

    ALTER TABLE production.product_colors
ADD CONSTRAINT df_current_time
DEFAULT CURRENT_TIMESTAMP FOR created_at;

    在此语句中，我们使用 CURRENT_TIMESTAMP 作为 created_at 列的默认值。CURRENT_TIMESTAMP 返回的值与 GETDATE() 函数相同。现在，当你插入新行时，如果不指定 created_at 列的值，SQL Server 将使用当前日期和时间值：

    INSERT INTO production.product_colors (color_name)
VALUES('Blue');

2.10 DATE 数据类型

DATE 数据类型用于存储日期数据。其语法如下：

DATE

DATE 值的范围从 0001-01-01 到 9999-12-31，存储大小为 3 字节，其默认字符串字面量格式如下：

YYYY-MM-DD
  • YYYY：四位数字，表示年份，范围从 0001 到 9999。
  • MM：两位数字，表示月份，范围从 01 到 12。
  • DD：两位数字，表示日期，范围从 01 到 31，具体取决于月份。
2.10.1 定义 DATE 列

  1. 创建一个名为 sales.list_prices 的表，以及两个 DATE 列：

    CREATE TABLE sales.list_prices (product_id INT NOT NULL,valid_from DATE NOT NULL,valid_to DATE NOT NULL,amount DEC(10, 2) NOT NULL,PRIMARY KEY (product_id, valid_from, valid_to),FOREIGN KEY (product_id) REFERENCES production.products(product_id)
);

  2. 插入带有字面量日期值的行：

    INSERT INTO sales.list_prices (product_id,valid_from,valid_to,amount)
VALUES (1,'2019-01-01','2019-12-31',400);

    在插入日期数据时，应确保日期格式符合 YYYY-MM-DD 的标准格式，以避免格式错误。

2.10.2 基于 DATE 值查询表中的数据

假设有一个 sales.orders 表，表结构如下：

在这里插入图片描述

以下查询返回所有订单日期早于 2016 年 1 月 5 日的订单：

SELECTorder_id,customer_id,order_status,order_date
FROMsales.orders
WHERE order_date < '2016-01-05'
ORDER BYorder_date DESC;

在这里插入图片描述

2.10.3 与其他日期类型一起使用

  DATE 数据类型可以与其他日期和时间数据类型一起使用，如 DATETIME、DATETIME2 和 SMALLDATETIME，SQL Server 自动处理不同日期和时间数据类型之间的转换。

  1. 赋值： 将 DATE 类型的值赋给 DATETIME 或 DATETIME2 类型的列时，SQL Server 会自动将日期部分转换为相应的日期和时间格式，时间部分默认为 00:00:00，例如：

    DECLARE @date DATE = '2023-04-05';
DECLARE @datetime DATETIME = @date;SELECT @date AS DateValue,@datetime AS DateTimeValue;

    DateValue   DateTimeValue       2023-04-05  2023-04-05 00:00:00

  2. 比较：对于不同日期和时间数据类型，SQL Server 也会自动将它们转换为一个共同的日期和时间格式，以便进行比较，例如：

    DECLARE @date DATE = '2023-04-05';
DECLARE @datetime DATETIME = '2023-04-05 14:30:00';IF @date = @datetimeSELECT '相等' AS Result;
ELSESELECT '不相等' AS Result;

    Result 不相等

    在比较时，DATE 类型的值会被隐式转换为 DATETIME 类型，时间部分默认为 00:00:00。因此，2023-04-05 和 2023-04-05 14:30:00 不相等。

  3. 显式转换：在需要进行类型转换时，可以使用 CAST 或 CONVERT 函数显式转换，以避免隐式转换带来的问题。

    SELECT CAST(OrderDate AS DATETIME) AS OrderDateTime
FROM Orders;

  不同日期类型之间的转换可能会引入额外的计算开销，尤其是在处理大量数据时。尽量避免不必要的类型转换，以提高查询性能。

2.11 TIME 数据类型

TIME 数据类型定义了一天中的时间，基于 24 小时制。其语法如下：

TIME[(fractional second scale)]
  • fractional second scale：指定小数秒的精度，范围从 0 到 7。如果不显式指定，默认值为 7。

TIME 值的默认字符串字面量格式如下：

hh:mm:ss[.nnnnnnn]
  • hh：两位数字，表示小时，范围从 00 到 23。
  • mm：两位数字，表示分钟，范围从 00 到 59。
  • ss：两位数字，表示秒，范围从 00 到 59。
  • 小数秒部分：零到七位数字，范围从 0 到 9999999。

TIME 类型的存储大小取决于小数秒的精度，范围从 3 到 5 字节。合理选择精度可以平衡存储大小和性能。

2.11.1 时区

  TIME 数据类型不支持时区，也就是说TIME 数据类型仅表示一天中的时间，而不涉及日期或时区信息。因此在实际应用中，需要根据具体需求来处理时区问题。以下是一些常见的处理方式：

  1. 使用 UTC 时间：在应用中使用协调世界时（UTC）作为标准时间，以确保时间的一致性，比如14:30:00 UTC。

  2. 存储时区信息：在表中额外存储时区信息，以便在需要时进行时区转换

    CREATE TABLE Appointments (AppointmentID INT PRIMARY KEY,StartTime TIME NOT NULL,EndTime TIME NOT NULL,TimeZone VARCHAR(50) NOT NULL
);INSERT INTO Appointments (StartTime, EndTime, TimeZone)
VALUES ('14:30:00', '15:30:00', 'UTC+8');

  3. 在应用层处理时区转换
    在应用层（如 Web 应用或桌面应用）中进行时区转换，而不是在数据库中处理。这样可以利用应用层的时区处理库（如 Python 的 pytz 或 JavaScript 的 moment-timezone）来处理时区转换。示例（Python）：

    from datetime import datetime
import pytzutc_time = datetime.strptime('14:30:00', '%H:%M:%S').time()
target_timezone = pytz.timezone('Asia/Shanghai')
local_time = utc_time.replace(tzinfo=pytz.utc).astimezone(target_timezone)
print(local_time)

  4. 使用DATETIMEOFFSET：如果需要存储带时区的时间数据，可以使用 DATETIMEOFFSET 数据类型，它包含日期、时间和时区偏移量。

    CREATE TABLE Appointments (AppointmentID INT PRIMARY KEY,StartTime DATETIMEOFFSET NOT NULL,EndTime DATETIMEOFFSET NOT NULL
);INSERT INTO Appointments (StartTime, EndTime)
VALUES ('2023-04-05 14:30:00 +08:00', '2023-04-05 15:30:00 +08:00');

注意事项：

  • 明确时区约定：在设计系统时，明确约定使用的时间基准（如 UTC 或特定时区），并在文档中详细说明。
  • 避免混淆：避免在同一个系统中混合使用不同时间基准（如 UTC 和本地时间）。如果必须使用本地时间，确保在存储和处理时明确标注时区信息。
2.11.2 使用示例

  1. 创建表：创建一个 sales.visits 表，用于记录客户访问特定商店的时间：

    CREATE TABLE sales.visits (visit_id INT PRIMARY KEY IDENTITY,customer_name VARCHAR(50) NOT NULL,phone VARCHAR(25),store_id INT NOT NULL,visit_on DATE NOT NULL,start_at TIME(0) NOT NULL,end_at TIME(0) NOT NULL,FOREIGN KEY (store_id) REFERENCES sales.stores(store_id)
);

  2. 向 sales.visits 表中插入一行数据：

    INSERT INTO sales.visits (customer_name,phone,store_id,visit_on,start_at,end_at)
VALUES('John Doe', '(408)-993-3853',1,'2018-06-23','09:10:00','09:30:00');

  3. 查询 sales.visits 表中的数据：

SELECTvisit_id,customer_name,phone,store_id,visit_on,start_at,end_at
FROMsales.visits;

在这里插入图片描述

2.12 DATETIMEOFFSET 数据类型

2.12.1 DATETIMEOFFSET概述

DATETIMEOFFSET 是 SQL Server 2008 引入的一种日期时间数据类型，它包含了日期、时间以及时区偏移量信息，是处理全球化应用程序中最完整的日期时间类型。

  1. 基本语法

    DATETIMEOFFSET [ (fractional seconds precision) ]

    例如，声明一个 DATETIMEOFFSET 变量的语法为：

    DECLARE @dt DATETIMEOFFSET(7)

    创建一个数据类型为 DATETIMEOFFSET 的表列的语法为：

    CREATE TABLE table_name (...,column_name DATETIMEOFFSET(7)...
);

  2. 字面量格式

    YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
    • YYYY-MM-DD：日期部分
    • hh:mm:ss[.nnnnnnn]：时间部分，可精确到100纳秒(7位小数)
    • [+|-]hh:mm：时区偏移量(相对于UTC的偏移)，偏移范围：-14:00 到 +14:00。例如+08:00表示比UTC早8小时(北京时间)。

  3. 存储大小，取决于精度(小数秒位数) 。0-2位需要8字节，3-4位需要9字节，5-7位需要10字节。

-- 创建表
CREATE TABLE EventLog (EventId INT PRIMARY KEY,EventTime DATETIMEOFFSET(7) NOT NULL,Description NVARCHAR(100)
);-- 插入数据(显式指定时区)
INSERT INTO EventLog VALUES 
(1, '2023-05-15 14:30:00 +08:00', '北京会议开始'),
(2, '2023-05-15 03:30:00 -05:00', '纽约会议开始');-- 插入当前时间(带服务器时区)
INSERT INTO EventLog VALUES 
(3, SYSDATETIMEOFFSET(), '当前服务器时间');
2.12.2 重要函数

  1. SYSDATETIMEOFFSET()：返回当前日期时间及时区偏移

    SELECT SYSDATETIMEOFFSET() AS CurrentDateTimeOffset;

    在这里插入图片描述

  2. TODATETIMEOFFSET()：将日期时间值转换为带指定偏移的DATETIMEOFFSET

    SELECT TODATETIMEOFFSET(GETDATE(), '+08:00') AS BeijingTime;

    在这里插入图片描述

  3. SWITCHOFFSET()：转换时区偏移

    SELECT SWITCHOFFSET(EventTime, '-05:00') 
FROM EventLog 
WHERE EventId = 1;

    在这里插入图片描述

  4. AT TIME ZONE (SQL Server 2016+)： 使用时区名称转换

    SELECT EventTime AT TIME ZONE 'China Standard Time' AS BeijingTime
FROM EventLog;

    在这里插入图片描述

最佳实践：

  1. 存储用户本地时间时总是使用DATETIMEOFFSET
  2. 在应用程序中明确处理时区转换
  3. 考虑使用AT TIME ZONE进行直观的时区转换
  4. 对于需要高精度的系统(如金融交易)使用DATETIMEOFFSET(7)

2.13 GUID与UNIQUEIDENTIFIER数据类型

2.13.1 GUID使用示例

  GUID（Globally Unique Identifier，全局唯一标识符）是微软对 UUID（Universally Unique Identifier）标准的实现，是一种 128 位（16 字节）的唯一标识符。在 SQL Server 中，GUID 通过 UNIQUEIDENTIFIER 数据类型表示，其格式通常为 32 个十六进制数字（固定16字节），分为 5 组，形式为：8-4-4-4-12，例如：A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11。以下是一个具体的示例：

  1. 在 marketing 架构中创建一个名为 customers 的新表：

    CREATE SCHEMA marketing;
GOCREATE TABLE marketing.customers(customer_id UNIQUEIDENTIFIER DEFAULT NEWID(),first_name NVARCHAR(100) NOT NULL,last_name NVARCHAR(100) NOT NULL,email VARCHAR(200) NOT NULL
);
GO

  2. 向 marketing.customers 表中插入新行：

    INSERT INTOmarketing.customers(first_name, last_name, email)
VALUES('John','Doe','john.doe@example.com'),('Jane','Doe','jane.doe@example.com');

  3. 查询数据：

    SELECTcustomer_id,first_name,last_name,email
FROMmarketing.customers;

SQL Server GUID 示例

2.13.2 📊 GUID 优缺点与使用场景
优点缺点
✅ 全局唯一性（跨数据库/服务器）⚠️ 存储开销大（16字节）
✅ 分布式系统合并安全⚠️ 索引碎片率高（随机性导致）
✅ 无需中央分配机构⚠️ 查询性能较低（比整型慢3-4倍）
✅ 隐藏业务信息（防数据推测）⚠️ 可读性差（难以人工识别）
✅ 离线生成能力⚠️ 排序效率低（非连续值）

在某些情况下，使用 GUID 值作为表的主键列比使用整数更受青睐。使用 GUID 作为主键具有以下优势：

  • GUID 值在全球范围内是唯一的，这意味着你可以轻松地从不同服务器合并数据。
  • GUID 值不会暴露信息，因此在公共接口（如 URL）中使用时更加安全。例如，如果你有一个 URL https://www.example.com/customer/100/，很容易推断出可能存在 ID 为 101、102 等的客户。然而，使用 GUID 时，这是不可能的：https://www.example.com/customer/F4AB02B7-9D55-483D-9081-CC4E3851E851/

然而，将 GUID 存储在表的主键列中也有一些缺点：

  • GUID 值（16 字节）比 INT（4 字节）或甚至 BIGINT（8 字节）占用更多的存储空间。
  • GUID 值使得故障排除和调试变得困难，比较 WHERE id = 100 与 WHERE id = 'F4AB02B7-9D55-483D-9081-CC4E3851E851'。

基于以上特点， GUID 典型应用场景为：

推荐使用场景不推荐使用场景
🔹 分布式系统数据同步🔸 高频写入的OLTP表
🔹 多数据库合并场景🔸 需要频繁JOIN的大表
🔹 客户端离线数据生成🔸 性能敏感的实时系统
🔹 需要隐藏ID连续性的安全系统🔸 主键作为外键大量引用的场景
🔹 跨平台数据交换🔸 需要人工维护ID的场景
2.13.3 ⚙️ 生成方式比较

  1. NEWID() 函数

    SELECT NEWID() AS GUID; -- 生成随机 GUID

    每次执行上述语句时，都会生成一个不同的值。例如：

    GUID
------------------------------------
3297F0F2-35D3-4231-919D-1CFCF4035975

  2. NEWSEQUENTIALID() 函数：使用有序 GUID 减少索引碎片。

    -- 只能在表 DEFAULT 约束中使用
CREATE TABLE Products (ProductID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,ProductName NVARCHAR(100)
);

  3. 应用程序生成

    // C# 示例
Guid newGuid = Guid.NewGuid();
方法特性适用场景
NEWID()完全随机生成通用场景
NEWSEQUENTIALID()按顺序生成（减少碎片）高写入量的GUID主键
应用层生成可预先生成批量处理需要批量插入的场景

💡 最佳实践提示：在分布式系统中必须使用GUID时，优先选择NEWSEQUENTIALID()，并配合定期索引重建维护。

2.13.4 高级技巧与最佳实践

  1. 索引优化

    -- 对GUID列使用INCLUDE而非键列
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID) INCLUDE(OrderGUID);

  2. 混合键策略

    -- 组合自增ID和GUID
CREATE TABLE Users (UserID INT IDENTITY(1,1) PRIMARY KEY,UserGUID UNIQUEIDENTIFIER DEFAULT NEWID() UNIQUE,UserName NVARCHAR(100)
);

  3. 批量插入优化

    -- 预生成GUID减少开销
DECLARE @guids TABLE (guid UNIQUEIDENTIFIER);
INSERT INTO @guids VALUES (NEWID()), (NEWID()), (NEWID());INSERT INTO Products(ProductID, ProductName)
SELECT guid, 'Product ' + CAST(ROW_NUMBER() OVER(ORDER BY guid) AS VARCHAR)
FROM @guids;

  4. 性能对比测试

    -- 创建测试表
CREATE TABLE TestIDs (ID INT IDENTITY PRIMARY KEY,GuidID UNIQUEIDENTIFIER DEFAULT NEWID(),Data VARCHAR(100)
);-- 插入100万条数据
-- INT: 平均12秒
-- GUID: 平均28秒 (NEWID())
-- GUID: 平均18秒 (NEWSEQUENTIALID())

GUID 是分布式系统中不可或缺的工具，但在单一数据库环境中应谨慎使用。遵循以下原则：

  1. 优先考虑 NEWSEQUENTIALID() 而非 NEWID()
  2. 在分布式架构中才使用GUID作为主键
  3. 考虑混合键策略平衡性能与需求
  4. 定期维护GUID索引以减少碎片
http://www.lryc.cn/news/573901.html

相关文章：

  • 考研408《计算机组成原理》复习笔记，第三章(1)——存储系统概念
  • （C++）素数的判断（C++教学）（C语言）
  • UNet改进（4）：交叉注意力（Cross Attention）-多模态/多特征交互
  • 测试工程师实战：用 LangChain+deepseek构建多轮对话测试辅助聊天机器人
  • 2025-06-22 思考-人的意识与不断走向死亡的过程
  • P99延迟：系统性能优化的关键指标
  • AWS认证系列：考点解析 - cloud trail，cloud watch，aws config
  • MySQL之索引结构和分类深度详解
  • 【构建大型语言模型】
  • 鸿蒙 Column 组件指南：垂直布局核心技术与场景化实践
  • 【PyTorch项目实战】CycleGAN：无需成对训练样本，支持跨领域图像风格迁移
  • 《计算机网络：自顶向下方法（第8版）》Chapter 8 课后题
  • 华为云Flexus+DeepSeek征文｜基于Dify构建解析网页写入Notion笔记工作流
  • 嵌入式C语言编程规范
  • Vue3解析Spring Boot ResponseEntity
  • select和poll用法解析
  • 如何仅用AI开发完整的小程序＜4＞—小程序页面创建与删除
  • 软件工程核心知识全景图：从需求到部署的系统化构建指南
  • 《算法笔记》之二（笔记）
  • DeepSeek：中国AI开源先锋的技术突破与行业革新
  • DeepSeek技术解析：开源大模型的创新突围之路
  • Unity中的Mathf.Clamp
  • 【unitrix】 4.0 类型级数值表示系统(types.rs)
  • 【已解决】 数据库INSERT操作时，Column count doesn’t match value count at row 1
  • 微处理器原理与应用篇---常见基础知识（6）
  • Redis-CPP 5大类型操作
  • 72、单元测试-常用测试注解
  • vue3 el-table 行字体颜色 根据字段改变
  • 在 Windows 和 Linux 下使用 C/C++ 连接 MySQL 的详细指南
  • SQL 中 HAVING COUNT (1)＞1 与 HAVING COUNT (*)＞1 的深度解析