SQL Server 核心特性与高级查询用法总结

夏知夏

发布时间：2026-10-15 | 4人浏览过

原创

本文深入解析 SQL Server 数据库的高级特性，涵盖表变量、临时表与 TVP 的选择策略，datetime 类型精度对比，MERGE 语句的多表同步操作，以及 CTE 递归查询和行列转换（Pivot/Unpivot）的实际应用。

基础数据类型与存储特性

在 SQL Server 中，合理选择数据结构对性能至关重要。

第1步：区分表变量、TVP、临时表与 CTE

目的：根据数据量和使用场景选择最高效的存储方式。

TVP (Table-Valued Parameters) 与临时表：两者均支持索引，且通常存储在 tempdb 数据库中，会增加系统数据库开销。适用于数据量大、需反复使用或频繁关联查询的场景。
表变量与 CTE：默认仅在内存中运行，当内存溢出时才会写入 tempdb。适用于数据量较小、单次使用的场景，性能更优。

第2步：掌握万能类型 sql_variant

sql_variant 可以存放所有兼容的数据类型，类似于 C# 中的 object 类型，但在实际开发中需谨慎使用，因为其会限制某些优化操作。

第3步：理解日期时间类型的精度差异

datetime：有效期范围较小（1753年1月1日之前不可用），精度为毫秒级。
datetime2：数据范围更广（类似 C# 的 DateTime），精度更高，小数点后保留7位（100纳秒级）。推荐使用 Sysdatetime() 函数获取该类型值。
datetimeoffset：包含时区信息的日期类型，适合跨国应用。

第4步：了解 rowversion 类型

替代旧的 timestamp 关键字。它是一个8字节的二进制值，自动递增，常用于解决并发控制问题，而非记录具体时间。

高级数据处理语法

第5步：使用 MERGE 进行多表同步操作

MERGE 语句主要用于处理两张表的对比操作，支持插入、更新或删除。

WHEN NOT MATCHED BY TARGET：针对源表存在但目标表中没有的记录，通常执行 INSERT。
WHEN NOT MATCHED BY SOURCE：针对目标表存在但源表中没有的记录，通常执行 DELETE 或 UPDATE。

第6步：避免不推荐的赋值语法

虽然 DECLARE @id INT = 0 很便捷，但 DBA 通常建议将声明与查表赋值分开，例如不要使用 DECLARE @id = SELECT TOP 1 id FROM table 这种混合写法，应保持清晰的结构。

聚合运算与递归查询

第7步：利用 WITH CUBE, ROLLUP 和 GROUPING SETS 汇总数据

这些运算符常与 GROUP BY 配合使用：

WITH CUBE：汇总所有可能的组合级别。
WITH ROLLUP：按层级从下至上汇总。
GROUPING SETS：仅返回指定的分组顶级汇总行。在汇总行中，NULL 可视为所有值的占位符，可使用 GROUPING(字段名) = 1 来判断。

复制代码代码如下:

-- 测试数据准备
DECLARE @t TABLE(goodsname VARCHAR(max), sku1name VARCHAR(max), sku2name VARCHAR(max), qty INT)
INSERT @t SELECT '凡客TX','红色','S',1
INSERT @t SELECT '凡客TX','黑色','S',2
INSERT @t SELECT '凡客TX','白色','L',3
INSERT @t SELECT '京东村山','白色','L',4
INSERT @t SELECT '京东村山','红色','S',5
INSERT @t SELECT '京东村山','黑色','L',6
INSERT @t SELECT '亚马逊拖鞋','白色','L',7
INSERT @t SELECT '亚马逊拖鞋','红色','S',8

-- WITH ROLLUP: 按 goodsname -> sku1name -> sku2name 层级汇总
SELECT goodsname, sku1name, sku2name, SUM(qty) AS sumqty
FROM @t
GROUP BY goodsname, sku1name, sku2name WITH ROLLUP
ORDER BY goodsname, sku1name, sku2name;

-- WITH CUBE: 汇总所有维度组合
SELECT goodsname, sku1name, sku2name, SUM(qty) AS sumqty
FROM @t
GROUP BY goodsname, sku1name, sku2name WITH CUBE
ORDER BY goodsname, sku1name, sku2name;

-- GROUPING SETS: 只指定特定维度的汇总
SELECT goodsname, sku1name, sku2name, SUM(qty)
FROM @t
GROUP BY GROUPING SETS(goodsname, sku1name, sku2name);

-- 结合 ROLLUP 与 GROUPING SETS
SELECT goodsname, sku1name, sku2name, SUM(qty)
FROM @t
GROUP BY GROUPING SETS(goodsname), ROLLUP(sku1name, sku2name);

-- 使用 GROUPING() 函数标记汇总行
SELECT 
    CASE WHEN GROUPING(goodsname) = 1 THEN '[ALL]' ELSE goodsname END AS goodsname,
    CASE WHEN GROUPING(sku1name) = 1 THEN '[ALL]' ELSE sku1name END AS sku1name,
    CASE WHEN GROUPING(sku2name) = 1 THEN '[ALL]' ELSE sku2name END AS sku2name,
    SUM(qty)
FROM @t
GROUP BY GROUPING SETS(goodsname), ROLLUP(sku1name, sku2name)
ORDER BY goodsname, sku1name, sku2name;

第8步：使用 CTE 实现递归查询

公用表达式 (CTE) 可以嵌套使用，结构比子查询更清晰，特别适合递归场景（如组织架构、目录树）。

复制代码代码如下:

-- 创建员工层级表
CREATE TABLE EMPLOYEETREE(
    EMPLOYEE INT PRIMARY KEY,
    employeename NVARCHAR(50),
    reportsto INT
);
INSERT INTO EMPLOYEETREE VALUES(1,'Richard',NULL)
INSERT INTO EMPLOYEETREE VALUES(2,'Stephen',1)
INSERT INTO EMPLOYEETREE VALUES(3,'Clemens',2)
INSERT INTO EMPLOYEETREE VALUES(4,'Malek',2)
INSERT INTO EMPLOYEETREE VALUES(5,'Goksin',4)
INSERT INTO EMPLOYEETREE VALUES(6,'Kimberly',1)
INSERT INTO EMPLOYEETREE VALUES(7,'Ramesh',5);

-- 基础递归：查询 Stephen (ID=2) 的所有下属
WITH employeeTemp AS (
    -- 锚点成员：查找直接上级为 Stephen 的员工
    SELECT EMPLOYEE, employeename, reportsto 
    FROM EMPLOYEETREE 
    WHERE EMPLOYEE = 2
    UNION ALL
    -- 递归成员：查找下属的下属
    SELECT a.EMPLOYEE, a.employeename, a.reportsto 
    FROM EMPLOYEETREE AS a
    INNER JOIN employeeTemp AS b ON a.reportsto = b.EMPLOYEE
)
SELECT * FROM employeeTemp WHERE EMPLOYEE <> 2;

-- 进阶递归：增加层级深度控制
WITH employeeTemp AS (
    SELECT EMPLOYEE, employeename, reportsto, 0 AS sublevel 
    FROM EMPLOYEETREE 
    WHERE EMPLOYEE = 2
    UNION ALL
    SELECT a.EMPLOYEE, a.employeename, a.reportsto, sublevel + 1
    FROM EMPLOYEETREE AS a
    INNER JOIN employeeTemp AS b ON a.reportsto = b.EMPLOYEE
)
SELECT * FROM employeeTemp WHERE EMPLOYEE <> 2 AND sublevel <= 2;

-- 注意：若需防止无限递归，可在外层添加 OPTION(MAXRECURSION n);

第9步：Pivot 行转列与 Unpivot 列转行

Pivot (行转列)：必须配合聚合函数使用（如 SUM, AVG）。计算聚时将忽略空值。需注意 IN 列表中的列类型必须一致，且最终结果需要别名。
Unpivot (列转行)：过滤掉值为 NULL 的列。语法要求 FOR 前是新列名，FOR 后对应原表列名中的值；IN 中指定原表的列标题。

复制代码代码如下:

-- 1. Pivot 示例：行转列
CREATE TABLE #test(
    NAME VARCHAR(max),
    SCORE INT
);
INSERT INTO #test VALUES ('张三','97'), ('李四','28'), ('王五','33'), ('神人','78');

SELECT [张三], [李四], [王五]
FROM #test
PIVOT (AVG(SCORE) FOR NAME IN ([张三], [李四], [王五])) AS pvt;

-- 2. Unpivot 示例：列转行
CREATE TABLE VendorEmployee(
    VendorId INT,
    Emp1Order INT, Emp2Order INT, Emp3Order INT, Emp4Order INT, Emp5Order INT
);
INSERT INTO VendorEmployee VALUES(1,4,3,5,4,4);
INSERT INTO VendorEmployee VALUES(2,4,1,5,5,5);
INSERT INTO VendorEmployee VALUES(3,4,3,5,4,4);
INSERT INTO VendorEmployee VALUES(4,4,2,5,4,4);
INSERT INTO VendorEmployee VALUES(5,5,1,5,5,5);

-- 标准 Unpivot 语法
SELECT *
FROM (
    SELECT VendorId, [Emp1Order], [Emp2Order], [Emp3Order], [Emp4Order], [Emp5Order] 
    FROM VendorEmployee
) AS unpiv
UNPIVOT (
    orders FOR elyid IN ([Emp1Order], [Emp2Order], [Emp3Order], [Emp4Order], [Emp5Order])
) AS child
ORDER BY elyid;

排错与注意事项

在使用上述高级特性时，请注意以下常见问题：

CTE 作用域：CTE 仅在紧接着的 SELECT, INSERT, UPDATE 或 DELETE 语句中有效，后续语句无法访问，如需多次引用请使用临时表或表变量。
Pivot 灵活性：PIVOT 的列在编译时需确定。如果列是动态变化的，通常需要拼接动态 SQL 或使用 CASE WHEN 代替。
递归限制：默认的递归最大深度为 100，如果遇到深层级数据报错，请在查询末尾添加 OPTION (MAXRECURSION 0) 以解除限制（0 代表无限制），但务必确保递归终止条件正确，防止死循环。