sql: Query Design Patterns using sql server 2019

 

-- ============================================================
-- SQL Server 2019 珠宝行业查询设计模式 - 完整可运行脚本
-- 包含：建表、插数据、高效JOIN、分页、搜索、窗口函数、递归CTE、透视转换
-- ============================================================

-- ==================== 第一部分：基础数据准备 ====================

-- 1. 创建珠宝产品分类表（含层级关系）
IF OBJECT_ID('JewelryCategories', 'U') IS NOT NULL DROP TABLE JewelryCategories;
CREATE TABLE JewelryCategories (
    CategoryID INT PRIMARY KEY IDENTITY(1,1),
    CategoryName NVARCHAR(100) NOT NULL,
    ParentCategoryID INT NULL,
    FOREIGN KEY (ParentCategoryID) REFERENCES JewelryCategories(CategoryID)
);

-- 2. 创建珠宝产品表
IF OBJECT_ID('JewelryProducts', 'U') IS NOT NULL DROP TABLE JewelryProducts;
CREATE TABLE JewelryProducts (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(200) NOT NULL,
    CategoryID INT NOT NULL,
    MetalType NVARCHAR(50),          -- 金属类型：黄金/铂金/银
    Gemstone NVARCHAR(100),          -- 宝石类型：钻石/红宝石/蓝宝石
    CaratWeight DECIMAL(10,2),       -- 克拉重量
    UnitPrice DECIMAL(12,2),         -- 单价
    StockQuantity INT DEFAULT 0,     -- 库存数量
    FOREIGN KEY (CategoryID) REFERENCES JewelryCategories(CategoryID)
);

-- 3. 创建客户表
IF OBJECT_ID('JewelryCustomers', 'U') IS NOT NULL DROP TABLE JewelryCustomers;
CREATE TABLE JewelryCustomers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    CustomerName NVARCHAR(100) NOT NULL,
    MembershipLevel NVARCHAR(20),    -- 会员等级：普通/银卡/金卡/钻石
    RegistrationDate DATE
);

-- 4. 创建订单表
IF OBJECT_ID('JewelryOrders', 'U') IS NOT NULL DROP TABLE JewelryOrders;
CREATE TABLE JewelryOrders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    CustomerID INT NOT NULL,
    OrderDate DATETIME NOT NULL,
    OrderStatus NVARCHAR(20),        -- 订单状态：待支付/已支付/已发货/已完成/已取消
    TotalAmount DECIMAL(12,2),
    FOREIGN KEY (CustomerID) REFERENCES JewelryCustomers(CustomerID)
);

-- 5. 创建订单明细表
IF OBJECT_ID('JewelryOrderDetails', 'U') IS NOT NULL DROP TABLE JewelryOrderDetails;
CREATE TABLE JewelryOrderDetails (
    DetailID INT PRIMARY KEY IDENTITY(1,1),
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(12,2) NOT NULL,
    LineTotal DECIMAL(12,2),
    FOREIGN KEY (OrderID) REFERENCES JewelryOrders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES JewelryProducts(ProductID)
);

-- 6. 创建销售流水表（用于窗口函数演示）
IF OBJECT_ID('JewelrySalesLog', 'U') IS NOT NULL DROP TABLE JewelrySalesLog;
CREATE TABLE JewelrySalesLog (
    LogID INT PRIMARY KEY IDENTITY(1,1),
    ProductID INT NOT NULL,
    SaleDate DATE NOT NULL,
    SaleAmount DECIMAL(12,2),
    SalespersonID INT
);

-- ==================== 插入示例数据 ====================

INSERT INTO JewelryCategories VALUES 
('珠宝首饰', NULL),
('戒指', 1), ('项链', 1), ('手镯', 1), ('耳环', 1),
('黄金戒指', 2), ('钻石戒指', 2), ('铂金项链', 3);

INSERT INTO JewelryProducts VALUES 
('经典黄金戒指', 6, '黄金', NULL, 0, 3500.00, 50),
('一克拉钻戒', 7, '铂金', '钻石', 1.00, 85000.00, 10),
('蓝宝石吊坠项链', 8, '铂金', '蓝宝石', 2.50, 42000.00, 8),
('纯银手镯', 4, '银', NULL, 0, 1200.00, 200),
('红宝石耳环', 5, '黄金', '红宝石', 0.80, 28000.00, 15);

INSERT INTO JewelryCustomers VALUES 
('张三', '钻石', '2020-01-15'),
('李四', '金卡', '2021-03-20'),
('王五', '银卡', '2022-06-10'),
('赵六', '普通', '2023-09-01');

INSERT INTO JewelryOrders VALUES 
(1, '2024-01-10', '已完成', 85000.00),
(1, '2024-02-14', '已完成', 42000.00),
(2, '2024-03-08', '已支付', 3500.00),
(3, '2024-01-20', '已完成', 28000.00),
(4, '2024-04-01', '待支付', 1200.00);

INSERT INTO JewelryOrderDetails VALUES 
(1, 2, 1, 85000.00, 85000.00),
(2, 3, 1, 42000.00, 42000.00),
(3, 1, 1, 3500.00, 3500.00),
(4, 5, 1, 28000.00, 28000.00),
(5, 4, 1, 1200.00, 1200.00);

INSERT INTO JewelrySalesLog VALUES 
(2, '2024-01-10', 85000.00, 101),
(3, '2024-01-15', 42000.00, 102),
(2, '2024-02-14', 85000.00, 101),
(1, '2024-03-08', 3500.00, 103),
(5, '2024-01-20', 28000.00, 101),
(3, '2024-04-01', 42000.00, 102),
(1, '2024-05-10', 3500.00, 103);

-- ==================== 创建推荐索引 ====================

CREATE NONCLUSTERED INDEX IX_JewelryProducts_Price_ID
ON JewelryProducts (UnitPrice DESC, ProductID DESC);

CREATE NONCLUSTERED INDEX IX_JewelryProducts_Search
ON JewelryProducts (MetalType, UnitPrice)
INCLUDE (ProductName, Gemstone, CaratWeight);

CREATE NONCLUSTERED INDEX IX_JewelryOrders_Status
ON JewelryOrders (OrderStatus)
INCLUDE (CustomerID, OrderDate, TotalAmount);

CREATE NONCLUSTERED INDEX IX_JewelrySalesLog_ProductDate
ON JewelrySalesLog (ProductID, SaleDate)
INCLUDE (SaleAmount, SalespersonID);


-- ==================== 第二部分：高效 JOIN 连接查询 ====================

-- 2.1 反模式：SELECT * 与未提前过滤的多表 JOIN
PRINT '===== 反模式：SELECT * 多表JOIN =====';
SELECT *
FROM JewelryOrders o
JOIN JewelryCustomers c ON o.CustomerID = c.CustomerID
JOIN JewelryOrderDetails od ON o.OrderID = od.OrderID
JOIN JewelryProducts p ON od.ProductID = p.ProductID
WHERE o.OrderStatus = '已完成'
  AND c.MembershipLevel = '钻石'
  AND p.UnitPrice > 10000;

-- 2.2 优化方案：CTE 提前过滤 + 明确字段选择
PRINT '===== 优化方案：CTE提前过滤 =====';
WITH CompletedOrders AS (
    SELECT OrderID, CustomerID, OrderDate, TotalAmount
    FROM JewelryOrders
    WHERE OrderStatus = '已完成'
),
DiamondMembers AS (
    SELECT CustomerID, CustomerName, MembershipLevel
    FROM JewelryCustomers
    WHERE MembershipLevel = '钻石'
),
HighValueProducts AS (
    SELECT ProductID, ProductName, MetalType, Gemstone, UnitPrice
    FROM JewelryProducts
    WHERE UnitPrice > 10000
)
SELECT 
    co.OrderID,
    co.OrderDate,
    dm.CustomerName,
    hp.ProductName,
    hp.MetalType,
    hp.Gemstone,
    hp.UnitPrice,
    co.TotalAmount
FROM CompletedOrders co
INNER JOIN DiamondMembers dm ON co.CustomerID = dm.CustomerID
INNER JOIN JewelryOrderDetails od ON co.OrderID = od.OrderID
INNER JOIN HighValueProducts hp ON od.ProductID = hp.ProductID
ORDER BY co.OrderDate DESC;

-- 2.3 高级技巧：CROSS APPLY / OUTER APPLY
PRINT '===== CROSS APPLY / OUTER APPLY 示例 =====';

-- 查询每件珠宝产品及其最近一笔销售记录
SELECT 
    p.ProductID,
    p.ProductName,
    p.UnitPrice,
    recent.LastSaleDate,
    recent.LastSaleAmount
FROM JewelryProducts p
OUTER APPLY (
    SELECT TOP 1 SaleDate AS LastSaleDate, SaleAmount AS LastSaleAmount
    FROM JewelrySalesLog sl
    WHERE sl.ProductID = p.ProductID
    ORDER BY SaleDate DESC
) recent;

-- 查询每位客户的订单总金额（使用 CROSS APPLY 动态聚合）
SELECT 
    c.CustomerName,
    c.MembershipLevel,
    agg.TotalSpent,
    agg.OrderCount
FROM JewelryCustomers c
CROSS APPLY (
    SELECT 
        SUM(TotalAmount) AS TotalSpent,
        COUNT(*) AS OrderCount
    FROM JewelryOrders o
    WHERE o.CustomerID = c.CustomerID
      AND o.OrderStatus = '已完成'
) agg;


-- ==================== 第三部分：高效分页查询 ====================

-- 3.1 反模式：OFFSET-FETCH 大偏移量性能问题
PRINT '===== 反模式：OFFSET-FETCH 大偏移量 =====';
SELECT ProductID, ProductName, UnitPrice
FROM JewelryProducts
ORDER BY UnitPrice DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;  -- 小偏移量演示

-- 3.2 优化方案：Keyset（游标）分页
PRINT '===== 优化方案：Keyset 分页 =====';

-- 第一页
SELECT TOP 3 ProductID, ProductName, UnitPrice
FROM JewelryProducts
ORDER BY UnitPrice DESC, ProductID DESC;

-- 后续页：假设上一页最后一条是 UnitPrice=3500.00, ProductID=1
-- 实际使用时由应用程序传入上一页的最后记录值
DECLARE @LastPrice DECIMAL(12,2) = 3500.00;
DECLARE @LastID INT = 1;

SELECT TOP 3 ProductID, ProductName, UnitPrice
FROM JewelryProducts
WHERE (UnitPrice < @LastPrice) 
   OR (UnitPrice = @LastPrice AND ProductID < @LastID)
ORDER BY UnitPrice DESC, ProductID DESC;


-- ==================== 第四部分：搜索优化查询 ====================

-- 4.1 反模式：LIKE '%keyword%' 全表扫描
PRINT '===== 反模式：LIKE %keyword% =====';
SELECT ProductID, ProductName, Gemstone, UnitPrice
FROM JewelryProducts
WHERE ProductName LIKE '%钻石%' OR Gemstone LIKE '%钻石%';

-- 4.2 优化方案：前缀匹配 LIKE 'keyword%' 可利用索引
PRINT '===== 优化方案：前缀匹配 =====';
SELECT ProductID, ProductName, Gemstone, UnitPrice
FROM JewelryProducts
WHERE ProductName LIKE '钻石%';

-- 4.3 优化方案：多条件动态搜索（参数化动态SQL防注入）
PRINT '===== 优化方案：动态搜索 =====';
DECLARE @sql NVARCHAR(MAX) = N'
    SELECT ProductID, ProductName, MetalType, Gemstone, UnitPrice
    FROM JewelryProducts
    WHERE 1=1';

DECLARE @MetalType NVARCHAR(50) = '铂金';
DECLARE @MinPrice DECIMAL(12,2) = 10000;
DECLARE @Gemstone NVARCHAR(100) = NULL;

IF @MetalType IS NOT NULL
    SET @sql += N' AND MetalType = @mt';
IF @MinPrice IS NOT NULL
    SET @sql += N' AND UnitPrice >= @mp';
IF @Gemstone IS NOT NULL
    SET @sql += N' AND Gemstone = @gs';

SET @sql += N' ORDER BY UnitPrice DESC';

EXEC sp_executesql @sql,
    N'@mt NVARCHAR(50), @mp DECIMAL(12,2), @gs NVARCHAR(100)',
    @mt = @MetalType, @mp = @MinPrice, @gs = @Gemstone;


-- ==================== 第五部分：窗口函数 ====================

-- 5.1 排名与 Top-N 分析
PRINT '===== 窗口函数：每种宝石价格最高的前2件产品 =====';
SELECT ProductID, ProductName, Gemstone, UnitPrice, PriceRank
FROM (
    SELECT 
        ProductID,
        ProductName,
        Gemstone,
        UnitPrice,
        ROW_NUMBER() OVER (PARTITION BY Gemstone ORDER BY UnitPrice DESC) AS PriceRank
    FROM JewelryProducts
    WHERE Gemstone IS NOT NULL
) ranked
WHERE PriceRank <= 2;

-- 5.2 累计销售额与排名
PRINT '===== 窗口函数：销售人员累计销售额 =====';
SELECT 
    SalespersonID,
    SaleDate,
    SaleAmount,
    SUM(SaleAmount) OVER (
        PARTITION BY SalespersonID 
        ORDER BY SaleDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS CumulativeSales
FROM JewelrySalesLog
ORDER BY SalespersonID, SaleDate;

-- 5.3 移动平均与同比分析
PRINT '===== 窗口函数：移动平均与LAG/LEAD =====';
SELECT 
    ProductID,
    SaleDate,
    SaleAmount,
    AVG(SaleAmount) OVER (
        PARTITION BY ProductID 
        ORDER BY SaleDate 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3,
    LAG(SaleAmount, 1) OVER (
        PARTITION BY ProductID ORDER BY SaleDate
    ) AS PrevSaleAmount,
    LEAD(SaleAmount, 1) OVER (
        PARTITION BY ProductID ORDER BY SaleDate
    ) AS NextSaleAmount
FROM JewelrySalesLog
ORDER BY ProductID, SaleDate;

-- 5.4 NTILE 价格分档
PRINT '===== 窗口函数：NTILE 价格分档 =====';
SELECT 
    ProductID,
    ProductName,
    UnitPrice,
    NTILE(4) OVER (ORDER BY UnitPrice) AS PriceTier
FROM JewelryProducts;


-- ==================== 第六部分：递归 CTE ====================

-- 6.1 构建珠宝产品完整分类路径
PRINT '===== 递归CTE：分类路径构建 =====';
WITH CategoryHierarchy AS (
    -- 锚点成员：顶级分类（无父级）
    SELECT 
        CategoryID,
        CategoryName,
        ParentCategoryID,
        CAST(CategoryName AS NVARCHAR(500)) AS FullPath,
        1 AS HierarchyLevel
    FROM JewelryCategories
    WHERE ParentCategoryID IS NULL
    
    UNION ALL
    
    -- 递归成员：逐级向下查找子分类
    SELECT 
        c.CategoryID,
        c.CategoryName,
        c.ParentCategoryID,
        CAST(ch.FullPath + ' > ' + c.CategoryName AS NVARCHAR(500)) AS FullPath,
        ch.HierarchyLevel + 1
    FROM JewelryCategories c
    INNER JOIN CategoryHierarchy ch ON c.ParentCategoryID = ch.CategoryID
)
SELECT 
    ch.FullPath AS CategoryPath,
    p.ProductName,
    p.MetalType,
    p.Gemstone,
    p.UnitPrice
FROM CategoryHierarchy ch
LEFT JOIN JewelryProducts p ON ch.CategoryID = p.CategoryID
ORDER BY ch.FullPath
OPTION (MAXRECURSION 10);

-- 6.2 查找所有子分类（含间接子级）
PRINT '===== 递归CTE：查找所有子分类产品 =====';
DECLARE @RootCategoryID INT = 1;

WITH SubCategories AS (
    SELECT CategoryID, CategoryName, ParentCategoryID
    FROM JewelryCategories
    WHERE CategoryID = @RootCategoryID
    
    UNION ALL
    
    SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID
    FROM JewelryCategories c
    INNER JOIN SubCategories sc ON c.ParentCategoryID = sc.CategoryID
)
SELECT 
    sc.CategoryName,
    p.ProductName,
    p.UnitPrice
FROM SubCategories sc
LEFT JOIN JewelryProducts p ON sc.CategoryID = p.CategoryID
ORDER BY sc.CategoryName;


-- ==================== 第七部分：PIVOT / UNPIVOT 透视转换 ====================

-- 7.1 PIVOT：按金属类型透视销售汇总
PRINT '===== PIVOT：按金属类型透视 =====';
SELECT *
FROM (
    SELECT 
        c.CustomerName,
        p.MetalType,
        od.LineTotal AS SaleAmount
    FROM JewelryOrderDetails od
    JOIN JewelryOrders o ON od.OrderID = o.OrderID
    JOIN JewelryCustomers c ON o.CustomerID = c.CustomerID
    JOIN JewelryProducts p ON od.ProductID = p.ProductID
    WHERE o.OrderStatus = '已完成'
) AS SourceData
PIVOT (
    SUM(SaleAmount)
    FOR MetalType IN ([黄金], [铂金], [银])
) AS PivotTable;

-- 7.2 条件聚合替代 PIVOT（更灵活）
PRINT '===== 条件聚合替代PIVOT =====';
SELECT 
    c.CustomerName,
    SUM(CASE WHEN p.MetalType = '黄金' THEN od.LineTotal ELSE 0 END) AS GoldSales,
    SUM(CASE WHEN p.MetalType = '铂金' THEN od.LineTotal ELSE 0 END) AS PlatinumSales,
    SUM(CASE WHEN p.MetalType = '银' THEN od.LineTotal ELSE 0 END) AS SilverSales,
    SUM(od.LineTotal) AS TotalSales
FROM JewelryOrderDetails od
JOIN JewelryOrders o ON od.OrderID = o.OrderID
JOIN JewelryCustomers c ON o.CustomerID = c.CustomerID
JOIN JewelryProducts p ON od.ProductID = p.ProductID
WHERE o.OrderStatus = '已完成'
GROUP BY c.CustomerName;

-- 7.3 UNPIVOT 示例：先创建月度汇总表再转换
PRINT '===== UNPIVOT：宽表转长表 =====';

-- 创建月度销售汇总表
IF OBJECT_ID('MonthlySales', 'U') IS NOT NULL DROP TABLE MonthlySales;
CREATE TABLE MonthlySales (
    ProductID INT PRIMARY KEY,
    JanSales DECIMAL(12,2),
    FebSales DECIMAL(12,2),
    MarSales DECIMAL(12,2)
);

INSERT INTO MonthlySales VALUES 
(1, 3500.00, 0, 3500.00),
(2, 85000.00, 85000.00, 0),
(3, 42000.00, 0, 42000.00);

SELECT ProductID, SaleMonth, SaleAmount
FROM MonthlySales
UNPIVOT (
    SaleAmount FOR SaleMonth IN (JanSales, FebSales, MarSales)
) AS UnpivotedData;


-- ==================== 第八部分：GROUPING SETS 多级聚合 ====================

PRINT '===== GROUPING SETS：多维度销售汇总 =====';
SELECT 
    GROUPING_ID(p.MetalType, c.MembershipLevel) AS GroupLevel,
    p.MetalType,
    c.MembershipLevel,
    COUNT(*) AS OrderCount,
    SUM(od.LineTotal) AS TotalSales,
    AVG(od.LineTotal) AS AvgOrderValue
FROM JewelryOrderDetails od
JOIN JewelryOrders o ON od.OrderID = o.OrderID
JOIN JewelryCustomers c ON o.CustomerID = c.CustomerID
JOIN JewelryProducts p ON od.ProductID = p.ProductID
WHERE o.OrderStatus = '已完成'
GROUP BY GROUPING SETS (
    (p.MetalType, c.MembershipLevel),  -- 按金属+会员等级
    (p.MetalType),                      -- 按金属类型小计
    (c.MembershipLevel),                -- 按会员等级小计
    ()                                  -- 总计
)
ORDER BY GROUPING_ID(p.MetalType, c.MembershipLevel), p.MetalType, c.MembershipLevel;


-- ==================== 清理（可选）====================
-- 如需删除测试表，取消以下注释：
/*
DROP TABLE JewelryOrderDetails;
DROP TABLE JewelryOrders;
DROP TABLE JewelrySalesLog;
DROP TABLE JewelryProducts;
DROP TABLE JewelryCustomers;
DROP TABLE JewelryCategories;
DROP TABLE MonthlySales;

*/


-- ==================== 第八部分：索引优化与执行计划分析 ====================

-- 开启实际执行计划（在SSMS中也可按 Ctrl+M 开启）
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- ==================== 8.1 核心算子识别与优化实战 ====================

-- 场景1：消除 Key Lookup（回表查找）
-- 反模式：索引只覆盖过滤条件，查询时需要回表取其他列
PRINT '===== 场景1：Key Lookup 优化 =====';

-- 模拟未覆盖索引的情况（先删除之前建的宽索引以演示问题）
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_JewelryOrders_Status')
    DROP INDEX IX_JewelryOrders_Status ON JewelryOrders;

-- 只建过滤列的窄索引
CREATE NONCLUSTERED INDEX IX_JewelryOrders_Status_Narrow
ON JewelryOrders (OrderStatus);

-- 执行查询：需要 OrderDate, TotalAmount 等未包含在索引中的列
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM JewelryOrders
WHERE OrderStatus = '已完成';

-- 优化：创建覆盖索引，将 SELECT 需要的列放入 INCLUDE
CREATE NONCLUSTERED INDEX IX_JewelryOrders_Status_Covering
ON JewelryOrders (OrderStatus)
INCLUDE (CustomerID, OrderDate, TotalAmount);

-- 再次执行，观察执行计划中 Key Lookup 消失，逻辑读取大幅下降
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM JewelryOrders
WHERE OrderStatus = '已完成';


-- 场景2：消除 Sort 算子（利用索引预排序）
PRINT '===== 场景2：Sort 算子优化 =====';

-- 反模式：ORDER BY 列无索引，需要额外排序操作
SELECT ProductID, ProductName, UnitPrice
FROM JewelryProducts
ORDER BY UnitPrice DESC;

-- 优化：创建包含排序列的索引
-- 注意：索引键列顺序应与 ORDER BY 一致
CREATE NONCLUSTERED INDEX IX_JewelryProducts_Price_Sort
ON JewelryProducts (UnitPrice DESC)
INCLUDE (ProductID, ProductName);

-- 再次执行，执行计划中 Sort 算子消失，变为 Index Scan（有序扫描）
SELECT ProductID, ProductName, UnitPrice
FROM JewelryProducts
ORDER BY UnitPrice DESC;


-- 场景3：隐式类型转换导致索引失效
PRINT '===== 场景3：隐式转换优化 =====';

-- 反模式：参数类型与列类型不匹配，导致全表扫描
-- 假设 MetalType 是 NVARCHAR，但传入 VARCHAR 参数
DECLARE @Metal VARCHAR(50) = '黄金';  -- 故意用 VARCHAR
SELECT ProductID, ProductName, UnitPrice
FROM JewelryProducts
WHERE MetalType = @Metal;  -- 执行计划会出现黄色警告三角形

-- 优化：确保参数类型与列类型完全一致
DECLARE @MetalCorrect NVARCHAR(50) = '黄金';
SELECT ProductID, ProductName, UnitPrice
FROM JewelryProducts
WHERE MetalType = @MetalCorrect;


-- ==================== 8.2 缺失索引 DMV 查询 ====================

PRINT '===== 缺失索引建议（基于当前缓存计划） =====';
SELECT TOP 10
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS ImprovementMeasure,
    OBJECT_NAME(mid.object_id) AS TableName,
    mid.equality_columns,      -- 等值过滤列
    mid.inequality_columns,    -- 不等值过滤列
    mid.included_columns,      -- 建议包含列
    migs.user_seeks,
    migs.user_scans,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_' + 
        REPLACE(REPLACE(REPLACE(COALESCE(mid.equality_columns,'') + 
        COALESCE(mid.inequality_columns,''), ',', '_'), '[', ''), ']', '') +
        ' ON ' + OBJECT_NAME(mid.object_id) + 
        ' (' + COALESCE(mid.equality_columns,'') + 
        CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END +
        COALESCE(mid.inequality_columns,'') + ')' +
        CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END AS CreateIndexStatement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY ImprovementMeasure DESC;


-- ==================== 8.3 索引碎片检查与维护 ====================

PRINT '===== 索引碎片检查 =====';
SELECT 
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.avg_fragmentation_in_percent,
    ps.page_count,
    CASE 
        WHEN ps.avg_fragmentation_in_percent > 30 THEN '建议重建索引 (REBUILD)'
        WHEN ps.avg_fragmentation_in_percent > 5 THEN '建议重组索引 (REORGANIZE)'
        ELSE '无需维护'
    END AS Recommendation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 5
  AND ps.page_count > 100
ORDER BY ps.avg_fragmentation_in_percent DESC;

-- 索引维护示例（按需执行）
-- ALTER INDEX IX_JewelryOrders_Status_Covering ON JewelryOrders REORGANIZE;
-- ALTER INDEX ALL ON JewelryOrders REBUILD WITH (ONLINE = ON);  -- 企业版支持联机重建


-- ==================== 8.4 查询存储（Query Store）分析 ====================
-- SQL Server 2019 推荐开启 Query Store 进行长期性能追踪

-- 检查 Query Store 是否开启
SELECT 
    name,
    is_query_store_on,
    query_store_retention_interval_in_minutes,
    max_storage_size_mb
FROM sys.databases
WHERE name = DB_NAME();

-- 若未开启，可执行以下命令开启（需要 ALTER DATABASE 权限）
-- ALTER DATABASE CURRENT SET QUERY_STORE = ON;
-- ALTER DATABASE CURRENT SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

-- 查询最耗时的前10个查询
PRINT '===== Query Store：最耗时查询 Top 10 =====';
SELECT TOP 10
    qsqt.query_sql_text,
    qrs.avg_duration / 1000.0 AS AvgDurationMS,
    qrs.count_executions,
    qrs.last_execution_time
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query qsq ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_runtime_stats qrs ON qsq.query_id = qrs.query_id
WHERE qrs.last_execution_time > DATEADD(DAY, -7, GETDATE())
ORDER BY qrs.avg_duration DESC;


-- ==================== 8.5 执行计划对比模板 ====================

PRINT '===== 执行计划对比：优化前 vs 优化后 =====';

-- 优化前：使用 OFFSET-FETCH 分页（大偏移量性能差）
SELECT ProductID, ProductName, UnitPrice
FROM JewelryProducts
ORDER BY UnitPrice DESC
OFFSET 10000 ROWS FETCH NEXT 10 ROWS ONLY;

-- 优化后：使用 Keyset 分页（性能稳定）
DECLARE @LastPrice DECIMAL(12,2) = 0;
DECLARE @LastID INT = 0;

SELECT TOP 10 ProductID, ProductName, UnitPrice
FROM JewelryProducts
WHERE (UnitPrice < @LastPrice) 
   OR (UnitPrice = @LastPrice AND ProductID < @LastID)
ORDER BY UnitPrice DESC, ProductID DESC;


-- 关闭统计信息
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO


/* =====================================================================
   SQL Server 2019 珠宝行业高级开发实战完整脚本
   业务场景: 珠宝集团 SaaS 平台（多租户）→ 客户维度管理（SCD2）→ 销售分析（批模式+自适应联接）
   执行方式: 在 SSMS 中按 F5 整段执行
===================================================================== */
SET NOCOUNT ON;
GO

/* =====================================================================
0. 准备：创建数据库与基础配置
===================================================================== */
USE master;
GO
IF DB_ID('JewelryERP_Advanced') IS NOT NULL
BEGIN
    ALTER DATABASE JewelryERP_Advanced SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE JewelryERP_Advanced;
END
GO
CREATE DATABASE JewelryERP_Advanced;
GO
USE JewelryERP_Advanced;
GO
-- 设置兼容性级别为 150（SQL Server 2019），启用智能查询处理
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
PRINT '=== 数据库 JewelryERP_Advanced 已创建，兼容性级别 150 ===';
GO

/* =====================================================================
模块一：RLS 多租户 + 分区表解决数据倾斜
===================================================================== */
PRINT '=== 模块一：RLS + 分区表 ===';
GO

-- 1.1 创建文件组与数据文件（生产环境建议放不同磁盘）
ALTER DATABASE CURRENT ADD FILEGROUP FG_Tenant_Large;
ALTER DATABASE CURRENT ADD FILEGROUP FG_Tenant_Small;

ALTER DATABASE CURRENT ADD FILE 
    (NAME = 'Data_Large', FILENAME = 'D:\Data\TenantLarge.ndf', SIZE = 100MB, FILEGROWTH = 50MB)
    TO FILEGROUP FG_Tenant_Large;

ALTER DATABASE CURRENT ADD FILE 
    (NAME = 'Data_Small', FILENAME = 'D:\Data\TenantSmall.ndf', SIZE = 10MB, FILEGROWTH = 10MB)
    TO FILEGROUP FG_Tenant_Small;
GO

-- 1.2 创建分区函数与分区方案（按 TenantID 分区）
CREATE PARTITION FUNCTION PF_TenantRange (INT)
AS RANGE LEFT FOR VALUES (1);  -- <=1 归分区1（大租户），>1 归分区2（小租户）
GO

CREATE PARTITION SCHEME PS_TenantRange
AS PARTITION PF_TenantRange
TO (FG_Tenant_Large, FG_Tenant_Small);
GO

-- 1.3 创建分区表（珠宝订单表）
IF OBJECT_ID('dbo.JewelryOrders_Partitioned', 'U') IS NOT NULL DROP TABLE JewelryOrders_Partitioned;
CREATE TABLE JewelryOrders_Partitioned (
    OrderID BIGINT IDENTITY(1,1),
    TenantID INT NOT NULL,
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(12,2),
    Status NVARCHAR(20),
    ProductCategory NVARCHAR(50),
    CONSTRAINT PK_JewelryOrders_Partitioned PRIMARY KEY CLUSTERED (TenantID, OrderID)
) ON PS_TenantRange(TenantID);
GO

-- 1.4 插入模拟数据
-- 大租户（ID=1）：插入 100 万行
INSERT INTO JewelryOrders_Partitioned (TenantID, CustomerID, OrderDate, TotalAmount, Status, ProductCategory)
SELECT TOP 1000000
    1,
    ABS(CHECKSUM(NEWID())) % 10000 + 1,
    DATEADD(MINUTE, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 525600, '2024-01-01'),
    ROUND(RAND(CHECKSUM(NEWID())) * 50000 + 500, 2),
    CASE ABS(CHECKSUM(NEWID())) % 4 WHEN 0 THEN '已完成' WHEN 1 THEN '已支付' WHEN 2 THEN '已发货' ELSE '待支付' END,
    CASE ABS(CHECKSUM(NEWID())) % 5 WHEN 0 THEN '钻戒' WHEN 1 THEN '黄金项链' WHEN 2 THEN '铂金手镯' WHEN 3 THEN '翡翠吊坠' ELSE '珍珠耳环' END
FROM sys.all_objects a, sys.all_objects b;

-- 小租户（ID=2,3,4）：各插入 1000 行
INSERT INTO JewelryOrders_Partitioned (TenantID, CustomerID, OrderDate, TotalAmount, Status, ProductCategory)
SELECT TOP 3000
    (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 3) + 2,
    ABS(CHECKSUM(NEWID())) % 1000 + 1,
    DATEADD(MINUTE, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 525600, '2024-01-01'),
    ROUND(RAND(CHECKSUM(NEWID())) * 5000 + 100, 2),
    '已完成',
    '黄金项链'
FROM sys.all_objects a;
GO

-- 1.5 创建 RLS 安全策略
CREATE SCHEMA Security;
GO

CREATE OR ALTER FUNCTION Security.fn_TenantPredicate(@TenantID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Result
WHERE CAST(SESSION_CONTEXT(N'TenantID') AS INT) = @TenantID;
GO

CREATE SECURITY POLICY Security.TenantPartitionPolicy
ADD FILTER PREDICATE Security.fn_TenantPredicate(TenantID) 
    ON dbo.JewelryOrders_Partitioned
WITH (STATE = ON);
GO

-- 1.6 验证分区消除与 RLS
SET STATISTICS IO ON;

PRINT '--- 大租户查询（应只扫描分区1）---';
EXEC sp_set_session_context N'TenantID', 1;
SELECT COUNT(*) AS LargeTenantOrders FROM JewelryOrders_Partitioned WHERE Status = '已完成';

PRINT '--- 小租户查询（应只扫描分区2）---';
EXEC sp_set_session_context N'TenantID', 2;
SELECT COUNT(*) AS SmallTenantOrders FROM JewelryOrders_Partitioned WHERE Status = '已完成';

SET STATISTICS IO OFF;
GO

-- 查看分区数据分布
SELECT 
    $PARTITION.PF_TenantRange(TenantID) AS PartitionNumber,
    TenantID,
    COUNT(*) AS RowCount
FROM JewelryOrders_Partitioned
GROUP BY $PARTITION.PF_TenantRange(TenantID), TenantID
ORDER BY PartitionNumber;
GO

PRINT '=== 模块一完成 ===';
GO

/* =====================================================================
模块二：SCD Type 2 增量 ETL（MERGE + OUTPUT）
===================================================================== */
PRINT '=== 模块二：SCD Type 2 ETL ===';
GO

-- 2.1 创建客户维度表（SCD Type 2）
IF OBJECT_ID('dbo.DimCustomer_SCD2', 'U') IS NOT NULL DROP TABLE DimCustomer_SCD2;
CREATE TABLE DimCustomer_SCD2 (
    CustomerKey INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    CustomerName NVARCHAR(100) NOT NULL,
    City NVARCHAR(50),
    MembershipLevel NVARCHAR(20),
    Email NVARCHAR(100),
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    IsCurrent BIT NOT NULL DEFAULT 1,
    DataHash AS CHECKSUM(CustomerName, City, MembershipLevel),
    INDEX IX_DimCustomer_Current (CustomerID, IsCurrent)
);
GO

-- 初始化维度数据
INSERT INTO DimCustomer_SCD2 (CustomerID, CustomerName, City, MembershipLevel, Email, StartDate, EndDate, IsCurrent)
VALUES
(1001, '张三', '北京', '金卡', 'zhangsan@example.com', '2023-01-01', '9999-12-31', 1),
(1002, '李四', '上海', '银卡', 'lisi@example.com', '2023-01-01', '9999-12-31', 1),
(1003, '王五', '广州', '普通', 'wangwu@example.com', '2023-01-01', '9999-12-31', 1);
GO

-- 2.2 创建临时表（存放新版本行）
IF OBJECT_ID('dbo.Temp_NewVersions', 'U') IS NOT NULL DROP TABLE Temp_NewVersions;
CREATE TABLE Temp_NewVersions (
    CustomerID INT,
    CustomerName NVARCHAR(100),
    City NVARCHAR(50),
    MembershipLevel NVARCHAR(20),
    StartDate DATE,
    EndDate DATE,
    IsCurrent BIT
);
GO

-- 2.3 模拟 CDC 增量数据源
IF OBJECT_ID('dbo.Stg_Customer_Changes', 'U') IS NOT NULL DROP TABLE Stg_Customer_Changes;
CREATE TABLE Stg_Customer_Changes (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    City NVARCHAR(50),
    MembershipLevel NVARCHAR(20),
    ChangeDate DATE,
    DataHash AS CHECKSUM(CustomerName, City, MembershipLevel)
);

-- 增量数据：张三城市变更，李四不变，赵五新增
INSERT INTO Stg_Customer_Changes (CustomerID, CustomerName, City, MembershipLevel, ChangeDate) VALUES
(1001, '张三', '深圳', '金卡', '2024-01-15'),
(1002, '李四', '上海', '银卡', '2024-01-15'),
(1004, '赵五', '成都', '普通', '2024-01-15');
GO

-- 2.4 核心 ETL：MERGE + OUTPUT
TRUNCATE TABLE Temp_NewVersions;

MERGE INTO DimCustomer_SCD2 AS Target
USING Stg_Customer_Changes AS Source
    ON Target.CustomerID = Source.CustomerID 
    AND Target.IsCurrent = 1
WHEN MATCHED AND Target.DataHash <> Source.DataHash THEN
    UPDATE SET 
        Target.EndDate = DATEADD(DAY, -1, Source.ChangeDate),
        Target.IsCurrent = 0
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, CustomerName, City, MembershipLevel, Email, StartDate, EndDate, IsCurrent, DataHash)
    VALUES (Source.CustomerID, Source.CustomerName, Source.City, Source.MembershipLevel, 
            NULL, Source.ChangeDate, '9999-12-31', 1, Source.DataHash)
OUTPUT 
    INSERTED.CustomerID,
    Source.CustomerName,
    Source.City,
    Source.MembershipLevel,
    Source.ChangeDate,
    '9999-12-31',
    1
INTO Temp_NewVersions (CustomerID, CustomerName, City, MembershipLevel, StartDate, EndDate, IsCurrent);

-- 插入新版本
INSERT INTO DimCustomer_SCD2 (CustomerID, CustomerName, City, MembershipLevel, StartDate, EndDate, IsCurrent)
SELECT CustomerID, CustomerName, City, MembershipLevel, StartDate, EndDate, IsCurrent
FROM Temp_NewVersions;
GO

-- 2.5 验证结果
PRINT '===== 张三的完整历史轨迹 =====';
SELECT CustomerKey, CustomerID, CustomerName, City, MembershipLevel, StartDate, EndDate, IsCurrent
FROM DimCustomer_SCD2
WHERE CustomerID = 1001
ORDER BY StartDate;

PRINT '===== 当前所有客户状态 =====';
SELECT CustomerID, CustomerName, City, MembershipLevel, IsCurrent
FROM DimCustomer_SCD2
WHERE IsCurrent = 1
ORDER BY CustomerID;
GO

-- 2.6 封装为存储过程
CREATE OR ALTER PROCEDURE dbo.usp_SCD2_Merge_ETL
    @ChangeDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE Temp_NewVersions;
    
    MERGE INTO DimCustomer_SCD2 AS Target
    USING Stg_Customer_Changes AS Source
        ON Target.CustomerID = Source.CustomerID 
        AND Target.IsCurrent = 1
    WHEN MATCHED AND Target.DataHash <> Source.DataHash THEN
        UPDATE SET 
            Target.EndDate = DATEADD(DAY, -1, Source.ChangeDate),
            Target.IsCurrent = 0
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CustomerID, CustomerName, City, MembershipLevel, Email, StartDate, EndDate, IsCurrent, DataHash)
        VALUES (Source.CustomerID, Source.CustomerName, Source.City, Source.MembershipLevel, 
                NULL, Source.ChangeDate, '9999-12-31', 1, Source.DataHash)
    OUTPUT 
        INSERTED.CustomerID, Source.CustomerName, Source.City, Source.MembershipLevel,
        Source.ChangeDate, '9999-12-31', 1
    INTO Temp_NewVersions (CustomerID, CustomerName, City, MembershipLevel, StartDate, EndDate, IsCurrent);
    
    INSERT INTO DimCustomer_SCD2 (CustomerID, CustomerName, City, MembershipLevel, StartDate, EndDate, IsCurrent)
    SELECT CustomerID, CustomerName, City, MembershipLevel, StartDate, EndDate, IsCurrent
    FROM Temp_NewVersions;
    
    PRINT 'ETL 完成：影响 ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 行';
END;
GO

PRINT '=== 模块二完成 ===';
GO

/* =====================================================================
模块三：批处理执行模式（Batch Mode）与自适应联接
===================================================================== */
PRINT '=== 模块三：批模式与自适应联接 ===';
GO

-- 3.1 创建销售事实表（行存储）
IF OBJECT_ID('dbo.Fact_Sales_BatchTest', 'U') IS NOT NULL DROP TABLE Fact_Sales_BatchTest;
CREATE TABLE Fact_Sales_BatchTest (
    SaleID INT IDENTITY(1,1) PRIMARY KEY,
    ProductID INT,
    SaleDate DATE,
    Amount DECIMAL(12,2),
    Quantity INT,
    Region NVARCHAR(50)
);

-- 插入 500 万行数据
;WITH Numbers AS (
    SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
    FROM sys.all_objects a, sys.all_objects b, sys.all_objects c
)
INSERT INTO Fact_Sales_BatchTest (ProductID, SaleDate, Amount, Quantity, Region)
SELECT 
    N % 1000 + 1,
    DATEADD(DAY, N % 1095, '2022-01-01'),
    ROUND(RAND(CHECKSUM(NEWID())) * 10000 + 100, 2),
    N % 20 + 1,
    CASE N % 5 WHEN 0 THEN '华东' WHEN 1 THEN '华南' WHEN 2 THEN '华北' WHEN 3 THEN '西南' ELSE '华中' END
FROM Numbers;
GO

-- 3.2 创建行存储索引 + 列存储索引（触发批模式）
CREATE NONCLUSTERED INDEX IX_Fact_Sales_Region 
ON Fact_Sales_BatchTest (Region) INCLUDE (Amount, Quantity);

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Fact_Sales
ON Fact_Sales_BatchTest (ProductID, Amount, Quantity, Region);
GO

-- 3.3 创建维度表
IF OBJECT_ID('dbo.Dim_Product', 'U') IS NOT NULL DROP TABLE Dim_Product;
CREATE TABLE Dim_Product (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(200),
    Category NVARCHAR(50)
);

INSERT INTO Dim_Product (ProductID, ProductName, Category)
SELECT TOP 1000
    N,
    '产品_' + CAST(N AS VARCHAR),
    CASE N % 5 WHEN 0 THEN '黄金' WHEN 1 THEN '铂金' WHEN 2 THEN '钻石' WHEN 3 THEN '银' ELSE '玉石' END
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM sys.all_objects a) T;
GO

-- 3.4 执行分析查询（观察批模式与自适应联接）
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- 查询1：聚合分析（触发批模式）
PRINT '--- 聚合查询（批模式）---';
SELECT Region, SUM(Amount) AS TotalSales, AVG(Quantity) AS AvgQty
FROM Fact_Sales_BatchTest
GROUP BY Region;

-- 查询2：Join 查询（触发自适应联接）
PRINT '--- Join 查询（自适应联接）---';
SELECT p.Category, SUM(f.Amount) AS TotalSales
FROM Fact_Sales_BatchTest f
JOIN Dim_Product p ON f.ProductID = p.ProductID
WHERE p.Category = '黄金'
GROUP BY p.Category;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- 3.5 通过 DMV 查看执行模式
SELECT TOP 5
    qsqt.query_sql_text,
    qsp.query_plan,
    qrs.avg_duration / 1000.0 AS AvgDurationMS
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query qsq ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qrs ON qsp.plan_id = qrs.plan_id
WHERE qsqt.query_sql_text LIKE '%Fact_Sales_BatchTest%'
  AND qrs.last_execution_time > DATEADD(MINUTE, -10, GETDATE())
ORDER BY qrs.last_execution_time DESC;
GO

PRINT '=== 模块三完成 ===';
GO

/* =====================================================================
收尾：清理与总结
===================================================================== */
PRINT '============================================';
PRINT '所有模块执行完毕！';
PRINT '============================================';
PRINT '关键验证点：';
PRINT '1. 模块一：查看 STATISTICS IO 输出，确认大/小租户 Scan count 均为 1（分区消除生效）';
PRINT '2. 模块二：查看张三的历史轨迹，确认有 2 行记录（旧版本+新版本）';
PRINT '3. 模块三：在 SSMS 中按 Ctrl+M 开启实际执行计划，右键算子属性查看：';
PRINT '   - Actual Execution Mode = Batch（批模式）';
PRINT '   - IsAdaptive = True（自适应联接）';
PRINT '============================================';