印刻数据库

外观

SQLServer 高级T-SQL特性 ​

窗口函数 ​

窗口函数概述 ​

窗口函数是 SQL Server 中用于在查询结果集的特定窗口内执行计算的高级函数，它允许在一行数据上访问同一结果集内的其他行数据，而不需要使用自连接或子查询。窗口函数在数据分析、报表生成和业务智能场景中具有广泛应用。

常见窗口函数 ​

排名函数 ​

  • ROW_NUMBER()：为结果集的每行分配唯一序号
  • RANK()：根据排序值分配排名，相同值获得相同排名，后续排名跳过
  • DENSE_RANK()：根据排序值分配排名，相同值获得相同排名，后续排名连续
  • NTILE(n)：将结果集均匀分为 n 个桶，返回每行所属桶号

聚合函数 ​

  • SUM() OVER()：计算窗口内的总和
  • AVG() OVER()：计算窗口内的平均值
  • COUNT() OVER()：计算窗口内的行数
  • MAX() OVER()：计算窗口内的最大值
  • MIN() OVER()：计算窗口内的最小值

分析函数 ​

  • LAG()：访问当前行之前的指定行数据
  • LEAD()：访问当前行之后的指定行数据
  • FIRST_VALUE()：返回窗口内的第一行数据
  • LAST_VALUE()：返回窗口内的最后一行数据

窗口函数语法 ​

sql
-- 基本语法
<window_function>([<argument>]) OVER (
    [PARTITION BY <partition_expression> [, ...]]
    [ORDER BY <order_expression> [ASC | DESC] [, ...]]
    [ROWS/RANGE <frame_spec>]
)

窗口函数应用场景 ​

生产环境实践：

  • 销售数据分析：计算每个产品的月度销售排名和累计销售额
  • 财务报表：生成累计利润、同比环比分析
  • 用户行为分析：计算用户登录次数排名、连续登录天数
  • 库存管理：计算库存周转率、安全库存预警

示例：

sql
-- 计算每个产品的月度销售额排名和累计销售额
SELECT 
    ProductID,
    Month(OrderDate) AS SalesMonth,
    SUM(SalesAmount) AS MonthlySales,
    RANK() OVER (PARTITION BY Month(OrderDate) ORDER BY SUM(SalesAmount) DESC) AS MonthlyRank,
    SUM(SUM(SalesAmount)) OVER (PARTITION BY ProductID ORDER BY Month(OrderDate)) AS CumulativeSales
FROM Sales.OrderDetails
GROUP BY ProductID, Month(OrderDate);

最佳实践 ​

  • 合理使用 PARTITION BY 减少窗口大小，提高性能
  • 避免在大表上使用复杂的窗口函数，考虑预计算
  • 注意窗口函数的执行顺序，它在 GROUP BY 之后执行
  • 对于需要频繁计算的窗口函数结果，考虑使用物化视图或临时表

公共表表达式 (CTE) ​

CTE 概述 ​

公共表表达式 (Common Table Expression, CTE) 是一个临时命名的结果集，仅在单个 SQL 语句（如 SELECT、INSERT、UPDATE 或 DELETE）的执行范围内有效。CTE 提供了一种更清晰、更模块化的方式来编写复杂查询，替代了传统的子查询和临时表。

递归 CTE ​

递归 CTE 是一种特殊类型的 CTE，它引用自身来处理层次结构数据，如组织结构、产品分类、文件系统等。递归 CTE 包含两个部分：

  • 锚点成员：返回初始结果集的查询
  • 递归成员：引用 CTE 自身的查询，通过连接锚点成员结果集进行递归

CTE 应用场景 ​

生产环境实践：

  • 层次结构数据处理：查询组织结构、产品分类树
  • 复杂查询分解：将复杂查询拆分为多个简单的 CTE 步骤
  • 递归数据生成：生成日期序列、数字序列
  • 数据转换：复杂的数据透视和转换操作

示例：

sql
-- 递归 CTE 查询组织结构
WITH OrgHierarchy AS (
    -- 锚点成员：查询顶级部门
    SELECT 
        EmployeeID,
        ManagerID,
        EmployeeName,
        1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- 递归成员：查询下级部门
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        oh.Level + 1 AS Level
    FROM Employees e
    JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
)
SELECT * FROM OrgHierarchy;

最佳实践 ​

  • 递归 CTE 必须包含终止条件，避免无限递归
  • 合理使用递归深度限制：OPTION (MAXRECURSION n)
  • 避免在递归 CTE 中使用复杂的逻辑和函数
  • 对于大型层次结构数据，考虑使用层次结构 ID 类型

MERGE 语句 ​

MERGE 概述 ​

MERGE 语句是 SQL Server 2008 引入的高级 DML 语句，它允许在单个语句中同时执行 INSERT、UPDATE 和 DELETE 操作，根据源数据集和目标数据集的匹配情况执行相应的操作。MERGE 语句在数据同步、ETL 过程和数据仓库加载中具有重要应用。

MERGE 语法 ​

sql
MERGE [TOP (n)] <target_table> AS T
USING <source_table> AS S
ON <join_condition>
WHEN MATCHED [AND <clause>] THEN <update_action>
WHEN MATCHED [AND <clause>] THEN <delete_action>
WHEN NOT MATCHED [BY TARGET] [AND <clause>] THEN <insert_action>
WHEN NOT MATCHED BY SOURCE [AND <clause>] THEN <update_action>
[OUTPUT <output_clause>]
[OPTION (<query_hint> [, ...])];

MERGE 应用场景 ​

生产环境实践：

  • 数据同步：将源系统数据同步到目标系统
  • ETL 过程：数据仓库的增量加载
  • 维度表更新：更新数据仓库中的维度表
  • 合并数据集：将多个数据集合并为一个

示例：

sql
-- 使用 MERGE 同步销售订单数据
MERGE Sales.OrderFact AS T
USING Staging.NewOrders AS S
ON T.OrderID = S.OrderID
WHEN MATCHED AND (T.OrderDate <> S.OrderDate OR T.TotalAmount <> S.TotalAmount) THEN
    UPDATE SET 
        T.OrderDate = S.OrderDate,
        T.CustomerID = S.CustomerID,
        T.TotalAmount = S.TotalAmount,
        T.LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (OrderID, OrderDate, CustomerID, TotalAmount, LastUpdated)
    VALUES (S.OrderID, S.OrderDate, S.CustomerID, S.TotalAmount, GETDATE())
WHEN NOT MATCHED BY SOURCE AND T.OrderDate < DATEADD(MONTH, -12, GETDATE()) THEN
    DELETE
OUTPUT $action, INSERTED.*, DELETED.*;

最佳实践 ​

  • 始终在 MERGE 语句中使用 TOP (n) 限制处理的数据量，避免性能问题
  • 在 ON 子句中使用唯一键或主键，确保匹配的唯一性
  • 避免在单个 MERGE 语句中执行过多的操作，考虑拆分
  • 使用 OUTPUT 子句记录 MERGE 操作的结果，便于审计和调试

动态 SQL ​

动态 SQL 概述 ​

动态 SQL 是指在运行时构建和执行 SQL 语句的技术，它允许根据不同条件生成不同的 SQL 代码。动态 SQL 在通用查询工具、报表生成器和灵活的数据访问层中具有广泛应用。

动态 SQL 实现方法 ​

EXEC 语句 ​

sql
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Employees WHERE DepartmentID = ' + CAST(@DepartmentID AS NVARCHAR(10));
EXEC (@sql);

sp_executesql 系统存储过程 ​

sql
DECLARE @sql NVARCHAR(MAX);
DECLARE @DepartmentID INT = 1;

SET @sql = 'SELECT * FROM Employees WHERE DepartmentID = @DeptID';
EXEC sp_executesql @sql, N'@DeptID INT', @DeptID = @DepartmentID;

动态 SQL 安全问题 ​

  • SQL 注入：恶意用户通过输入恶意 SQL 代码执行未授权操作
  • 权限提升：动态 SQL 可能执行比预期更高权限的操作
  • 性能问题：频繁生成和编译动态 SQL 会导致性能下降

最佳实践 ​

生产环境实践：

  • 始终使用 sp_executesql 替代 EXEC，利用参数化查询防止 SQL 注入
  • 限制动态 SQL 的执行权限，使用最小权限原则
  • 预编译常用的动态 SQL 语句，提高性能
  • 验证和过滤所有用户输入，避免恶意代码
  • 使用 QUOTENAME() 函数处理动态对象名

示例：

sql
-- 安全的动态 SQL 示例
DECLARE @TableName NVARCHAR(128) = 'Employees';
DECLARE @ColumnName NVARCHAR(128) = 'DepartmentID';
DECLARE @Value INT = 1;

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM ' + QUOTENAME(@TableName) + 
           ' WHERE ' + QUOTENAME(@ColumnName) + ' = @ParamValue';

EXEC sp_executesql @sql, 
                   N'@ParamValue INT', 
                   @ParamValue = @Value;

分区表 ​

分区表概述 ​

分区表是将大型表的数据按照指定的分区键分散存储在多个文件组中的技术，它允许将大型表作为一个逻辑表进行管理，同时享受分区带来的性能提升和管理便利。分区表在数据仓库、日志系统和历史数据存储中具有重要应用。

分区表类型 ​

  • 水平分区：将表中的行按照分区键值分散到不同的分区中
  • 垂直分区：将表中的列分散到不同的分区中（通过视图实现）

分区表设计 ​

生产环境实践：

  • 选择合适的分区键：通常选择日期、地域或业务维度列
  • 设计分区函数：定义分区的边界值
  • 设计分区方案：将分区映射到不同的文件组
  • 考虑分区维护：定期拆分和合并分区

示例：

sql
-- 创建分区函数
CREATE PARTITION FUNCTION pf_OrderDate (DATETIME) 
AS RANGE RIGHT FOR VALUES (
    '2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01',
    '2023-05-01', '2023-06-01', '2023-07-01', '2023-08-01',
    '2023-09-01', '2023-10-01', '2023-11-01', '2023-12-01'
);

-- 创建分区方案
CREATE PARTITION SCHEME ps_OrderDate 
AS PARTITION pf_OrderDate 
TO (
    [FG_202212], [FG_202301], [FG_202302], [FG_202303],
    [FG_202304], [FG_202305], [FG_202306], [FG_202307],
    [FG_202308], [FG_202309], [FG_202310], [FG_202311],
    [FG_202312]
);

-- 创建分区表
CREATE TABLE Sales.OrderFact (
    OrderID INT IDENTITY(1,1) NOT NULL,
    OrderDate DATETIME NOT NULL,
    CustomerID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(18,2) NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL
) ON ps_OrderDate(OrderDate);

最佳实践 ​

  • 为每个分区分配独立的文件组，提高 I/O 并行度
  • 选择高选择性的分区键，确保数据均匀分布
  • 定期维护分区，包括拆分、合并和重建索引
  • 考虑使用分区切换快速加载和归档数据
  • 避免在小表上使用分区表，增加管理复杂性

列存储索引 ​

列存储索引概述 ​

列存储索引是 SQL Server 2012 引入的一种新型索引类型，它以列而不是行的方式存储数据，专为数据仓库和分析查询优化。列存储索引通过高压缩率、批处理执行模式和内存优化，提供了比传统行存储索引更高的查询性能。

列存储索引类型 ​

  • 聚集列存储索引：表的物理存储方式为列存储，适用于数据仓库事实表
  • 非聚集列存储索引：在传统行存储表上创建的列存储索引，适用于混合工作负载

列存储索引设计 ​

生产环境实践：

  • 数据仓库事实表：使用聚集列存储索引
  • 大型维度表：考虑使用非聚集列存储索引
  • 频繁更新的表：使用增量更新的列存储索引
  • 分析查询为主的表：优先考虑列存储索引

示例：

sql
-- 创建聚集列存储索引
CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrderFact
ON Sales.OrderFact;

-- 创建非聚集列存储索引
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_ProductDimension
ON Dimensions.ProductDimension(ProductID, ProductName, Category, SubCategory, Price);

最佳实践 ​

  • 对大型事实表使用聚集列存储索引，提高查询性能
  • 定期执行索引重建或重组，优化列存储索引的压缩率
  • 考虑使用列存储索引存档压缩，进一步降低存储成本
  • 对于频繁更新的表，使用带有行组管理的列存储索引
  • 结合分区表使用列存储索引，提高管理灵活性

内存优化表 ​

内存优化表概述 ​

内存优化表是 SQL Server 2014 引入的一种新型表类型，它完全存储在内存中，使用乐观并发控制，提供了比传统磁盘表更高的性能。内存优化表适用于高并发、低延迟的业务场景，如在线交易处理、实时数据分析等。

内存优化表设计 ​

生产环境实践：

  • 选择合适的表类型：持久化内存优化表或非持久化内存优化表
  • 设计合适的索引：哈希索引或范围索引
  • 考虑内存使用：监控和限制内存优化表的大小
  • 优化事务处理：使用原生编译存储过程提高性能

示例：

sql
-- 创建内存优化文件组
ALTER DATABASE Sales ADD FILEGROUP Sales_InMemory CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE Sales ADD FILE (NAME = 'Sales_InMemory', FILENAME = 'D:\Data\Sales_InMemory') TO FILEGROUP Sales_InMemory;

-- 创建内存优化表
CREATE TABLE Sales.OrderQueue (
    OrderID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    IsProcessed BIT NOT NULL DEFAULT 0,
    INDEX IX_OrderQueue_IsProcessed HASH (IsProcessed) WITH (BUCKET_COUNT = 1000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

最佳实践 ​

  • 仅在高并发、低延迟场景中使用内存优化表
  • 合理设置哈希索引的 BUCKET_COUNT，避免哈希冲突
  • 监控内存使用情况，避免内存不足
  • 结合原生编译存储过程使用，提高性能
  • 考虑数据持久化需求，选择合适的持久化级别

原生编译存储过程 ​

原生编译存储过程概述 ​

原生编译存储过程是 SQL Server 2014 引入的一种新型存储过程，它在创建时被编译为机器代码，直接在处理器上执行，提供了比传统存储过程更高的性能。原生编译存储过程只能访问内存优化表，适用于高并发的 OLTP 场景。

原生编译存储过程设计 ​

生产环境实践：

  • 简化逻辑：原生编译存储过程不支持所有 T-SQL 功能
  • 使用原子块：使用 BEGIN ATOMIC WITH 定义事务边界
  • 优化参数：使用合适的数据类型，避免隐式转换
  • 监控性能：使用动态管理视图监控执行情况

示例：

sql
-- 创建原生编译存储过程
CREATE PROCEDURE dbo.ProcessOrderQueue
    @BatchSize INT = 1000
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(   TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
    DECLARE @ProcessedOrders TABLE (
        OrderID INT PRIMARY KEY NONCLUSTERED
    );

    INSERT INTO @ProcessedOrders (OrderID)
    SELECT TOP (@BatchSize) OrderID
    FROM Sales.OrderQueue
    WHERE IsProcessed = 0;

    -- 处理订单逻辑
    UPDATE Sales.OrderQueue
    SET IsProcessed = 1
    WHERE OrderID IN (SELECT OrderID FROM @ProcessedOrders);
END;

最佳实践 ​

  • 仅在访问内存优化表时使用原生编译存储过程
  • 简化存储过程逻辑，避免复杂的条件判断和循环
  • 使用合适的事务隔离级别，提高并发性能
  • 避免在原生编译存储过程中使用临时表，使用表变量替代

JSON 支持 ​

JSON 函数 ​

SQL Server 2016 引入了全面的 JSON 支持，包括 JSON 解析、生成、修改和查询功能。JSON 支持允许 SQL Server 与现代 Web 应用和 NoSQL 数据库进行无缝集成。

JSON 数据类型 ​

SQL Server 不提供专门的 JSON 数据类型，而是使用 NVARCHAR(MAX) 存储 JSON 数据。SQL Server 提供了一系列内置函数来处理 JSON 数据：

  • JSON_VALUE()：从 JSON 字符串中提取标量值
  • JSON_QUERY()：从 JSON 字符串中提取对象或数组
  • JSON_MODIFY()：修改 JSON 字符串中的值
  • ISJSON()：验证 JSON 字符串的有效性
  • OPENJSON()：将 JSON 数组转换为表格格式

JSON 索引 ​

虽然 SQL Server 不支持原生 JSON 索引，但可以通过创建计算列和索引来提高 JSON 查询性能。

最佳实践 ​

生产环境实践：

  • 对频繁查询的 JSON 属性创建计算列和索引
  • 使用 OPENJSON() 配合索引提高 JSON 数组查询性能
  • 限制 JSON 数据的大小，避免过度使用 NVARCHAR(MAX)
  • 验证 JSON 数据的有效性，避免存储无效 JSON

示例：

sql
-- 创建 JSON 计算列和索引
ALTER TABLE Sales.OrderDetails
ADD CustomerInfo_Email AS JSON_VALUE(CustomerInfo, '$.Email');

CREATE INDEX IX_OrderDetails_CustomerEmail
ON Sales.OrderDetails(CustomerInfo_Email);

-- 查询 JSON 数据
SELECT OrderID, 
       JSON_VALUE(CustomerInfo, '$.Name') AS CustomerName,
       JSON_VALUE(CustomerInfo, '$.Email') AS CustomerEmail
FROM Sales.OrderDetails
WHERE JSON_VALUE(CustomerInfo, '$.Email') LIKE '%@example.com';

最佳实践 ​

代码可读性 ​

  • 使用一致的命名规范，提高代码可维护性
  • 添加适当的注释，解释复杂逻辑和业务规则
  • 格式化 SQL 代码，使用缩进和换行提高可读性
  • 将复杂查询拆分为多个简单步骤，使用 CTE 或临时表

性能优化 ​

  • 优先使用集合操作，避免游标和循环
  • 合理使用索引，避免过度索引和缺少索引
  • 优化查询计划，使用查询提示和计划指南
  • 考虑数据访问模式，优化表设计和索引策略

安全性 ​

  • 使用参数化查询，防止 SQL 注入
  • 遵循最小权限原则，限制用户和应用程序权限
  • 加密敏感数据，保护数据安全
  • 定期审计和监控数据库活动

可维护性 ​

  • 使用模块化设计，将复杂逻辑封装到存储过程和函数中
  • 建立版本控制机制，管理数据库对象的变更
  • 编写自动化测试，确保代码质量和功能正确性
  • 建立文档体系，记录数据库设计和实现细节

版本差异 ​

SQL Server 2008/2008 R2 ​

  • 支持基本的窗口函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）
  • 支持 CTE 和递归 CTE
  • 支持 MERGE 语句
  • 不支持列存储索引
  • 不支持内存优化表和原生编译存储过程
  • 不支持 JSON 支持

SQL Server 2012 ​

  • 引入非聚集列存储索引
  • 增强了窗口函数支持
  • 引入序列对象
  • 不支持内存优化表
  • 不支持 JSON 支持

SQL Server 2014 ​

  • 引入内存优化表和原生编译存储过程
  • 增强了列存储索引支持
  • 引入延迟持久化
  • 不支持 JSON 支持

SQL Server 2016 ​

  • 引入全面的 JSON 支持
  • 引入更新able 列存储索引
  • 增强了内存优化表支持
  • 引入 Query Store
  • 引入 Row-Level Security

SQL Server 2017 ​

  • 支持 Linux 和 Docker 容器
  • 增强了 JSON 支持
  • 引入自适应查询处理
  • 引入图形数据类型
  • 增强了列存储索引支持

SQL Server 2019 ​

  • 引入 Big Data Clusters
  • 增强了智能查询处理
  • 支持 UTF-8 字符集
  • 引入数据虚拟化功能
  • 增强了内存优化表支持

SQL Server 2022 ​

  • 引入 Ledger 功能
  • 增强了 Query Store Hints
  • 支持 Azure Synapse Link
  • 增强了智能查询处理
  • 支持 TLS 1.3

FAQ ​

窗口函数和聚合函数有什么区别？ ​

窗口函数和聚合函数的主要区别在于：

  • 聚合函数将多行数据聚合为一行，而窗口函数保留原始行数
  • 窗口函数使用 OVER() 子句定义计算窗口，而聚合函数使用 GROUP BY 子句
  • 窗口函数可以访问同一结果集中的其他行数据，而聚合函数只能访问分组内的数据

什么时候应该使用 CTE？ ​

推荐在以下场景使用 CTE：

  • 查询层次结构数据（使用递归 CTE）
  • 将复杂查询拆分为多个简单步骤
  • 替代临时表和表变量，简化代码
  • 需要在同一查询中多次引用相同的子查询结果

MERGE 语句有什么优势？ ​

MERGE 语句的主要优势包括：

  • 在单个语句中同时执行 INSERT、UPDATE 和 DELETE 操作，简化代码
  • 减少与数据库的交互次数，提高性能
  • 提供原子性操作，确保数据一致性
  • 支持 OUTPUT 子句，便于审计和调试

如何安全地使用动态 SQL？ ​

安全使用动态 SQL 的最佳实践：

  • 始终使用 sp_executesql 替代 EXEC，利用参数化查询
  • 使用 QUOTENAME() 函数处理动态对象名
  • 验证和过滤所有用户输入
  • 限制动态 SQL 的执行权限
  • 预编译常用的动态 SQL 语句

什么时候应该使用列存储索引？ ​

推荐在以下场景使用列存储索引：

  • 数据仓库和分析查询场景
  • 大型事实表和维度表
  • 以读取为主，更新频率低的数据
  • 需要高压缩率减少存储成本的场景

内存优化表适用于哪些场景？ ​

内存优化表适用于以下场景：

  • 高并发、低延迟的 OLTP 系统
  • 实时数据分析和处理
  • 高频交易系统
  • 会话状态管理

如何选择合适的分区键？ ​

选择分区键的最佳实践：

  • 选择高选择性的列，确保数据均匀分布
  • 选择查询中经常使用的过滤条件列
  • 考虑数据加载和归档策略
  • 避免使用频繁更新的列

JSON 支持和 XML 支持有什么区别？ ​

JSON 支持和 XML 支持的主要区别：

  • JSON 更轻量级，解析速度更快
  • XML 支持更丰富的数据类型和 schema 验证
  • JSON 更适合 Web 应用和 NoSQL 集成
  • XML 更适合复杂的文档结构和企业级应用

原生编译存储过程有什么限制？ ​

原生编译存储过程的主要限制：

  • 只能访问内存优化表
  • 不支持所有 T-SQL 功能
  • 需要使用原子块定义事务边界
  • 不支持临时表，只支持表变量

如何监控和优化列存储索引性能？ ​

监控和优化列存储索引性能的方法：

  • 使用动态管理视图监控列存储索引的使用情况
  • 定期重建或重组列存储索引，优化压缩率
  • 监控行组大小，确保最佳查询性能
  • 考虑使用增量更新的列存储索引减少维护开销