SQL Server 特定语法

概述

Microsoft SQL Server 是一个功能强大的关系型数据库管理系统，广泛应用于企业级应用。本章节详细介绍SQL Server的特定语法、高级功能和最佳实践。

SQL Server 数据类型

1. 基本数据类型

-- 创建包含各种数据类型的表
CREATE TABLE DataTypesDemo (
    -- 整数类型
    TinyIntVal TINYINT,                    -- 0-255
    SmallIntVal SMALLINT,                  -- -32,768 到 32,767
    IntVal INT,                            -- -2,147,483,648 到 2,147,483,647
    BigIntVal BIGINT,                      -- 大整数
    
    -- 小数类型
    DecimalVal DECIMAL(10,2),              -- 精确小数
    NumericVal NUMERIC(12,4),              -- 精确小数
    FloatVal FLOAT,                        -- 浮点数
    RealVal REAL,                          -- 单精度浮点
    MoneyVal MONEY,                        -- 货币类型
    SmallMoneyVal SMALLMONEY,              -- 小货币类型
    
    -- 字符类型
    CharVal CHAR(10),                      -- 定长字符
    VarCharVal VARCHAR(255),               -- 变长字符
    NCharVal NCHAR(10),                    -- 定长Unicode字符
    NVarCharVal NVARCHAR(255),             -- 变长Unicode字符
    TextVal TEXT,                          -- 大文本（已弃用）
    NTextVal NTEXT,                        -- Unicode大文本（已弃用）
    
    -- 日期时间类型
    DateTimeVal DATETIME,                  -- 日期时间
    DateTime2Val DATETIME2,                -- 高精度日期时间
    SmallDateTimeVal SMALLDATETIME,        -- 小日期时间
    DateVal DATE,                          -- 日期
    TimeVal TIME,                          -- 时间
    DateTimeOffsetVal DATETIMEOFFSET,      -- 带时区偏移的日期时间
    
    -- 二进制类型
    BinaryVal BINARY(16),                  -- 定长二进制
    VarBinaryVal VARBINARY(255),           -- 变长二进制
    ImageVal IMAGE,                        -- 大二进制（已弃用）
    
    -- 其他类型
    BitVal BIT,                            -- 位类型
    UniqueIdentifierVal UNIQUEIDENTIFIER,  -- GUID
    TimestampVal TIMESTAMP,                -- 行版本
    XmlVal XML,                            -- XML类型
    SqlVariantVal SQL_VARIANT              -- 变体类型
);

-- 插入示例数据
INSERT INTO DataTypesDemo (
    TinyIntVal, SmallIntVal, IntVal, BigIntVal,
    DecimalVal, NumericVal, FloatVal, RealVal, MoneyVal, SmallMoneyVal,
    CharVal, VarCharVal, NCharVal, NVarCharVal,
    DateTimeVal, DateTime2Val, SmallDateTimeVal, DateVal, TimeVal, DateTimeOffsetVal,
    BinaryVal, VarBinaryVal,
    BitVal, UniqueIdentifierVal, XmlVal, SqlVariantVal
) VALUES (
    255, 32767, 2147483647, 9223372036854775807,
    12345.67, 123456.7890, 123.45, 123.45, 12345.67, 123.45,
    'CHAR_VAL', 'VARCHAR示例', N'NCHAR_VAL', N'NVARCHAR示例',
    '2023-12-25 14:30:00', '2023-12-25 14:30:00.1234567', '2023-12-25 14:30:00', '2023-12-25', '14:30:00', '2023-12-25 14:30:00.123 +08:00',
    0x48656C6C6F, 0x576F726C64,
    1, NEWID(), '<root><item>示例XML</item></root>', 'SQL_VARIANT示例'
);

-- 查询数据类型信息
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH,
    NUMERIC_PRECISION,
    NUMERIC_SCALE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'DataTypesDemo'
ORDER BY ORDINAL_POSITION;

2. 用户定义数据类型

-- 创建用户定义数据类型
CREATE TYPE EmailAddress FROM NVARCHAR(255) NOT NULL;
CREATE TYPE PhoneNumber FROM VARCHAR(20) NULL;
CREATE TYPE ProductCode FROM CHAR(10) NOT NULL;

-- 使用用户定义类型
CREATE TABLE Customers (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    Email EmailAddress,
    Phone PhoneNumber,
    ProductCode ProductCode
);

-- 创建表类型（用于表值参数）
CREATE TYPE CustomerTableType AS TABLE (
    CustomerName NVARCHAR(100),
    Email NVARCHAR(255),
    Phone VARCHAR(20)
);

-- 查看用户定义类型
SELECT 
    name,
    system_type_id,
    user_type_id,
    max_length,
    precision,
    scale,
    is_nullable
FROM sys.types
WHERE is_user_defined = 1;

SQL Server 特有功能

1. IDENTITY 列和序列

-- IDENTITY列
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,  -- 从1开始，步长为1
    CustomerID INT,
    OrderDate DATETIME2 DEFAULT GETDATE(),
    Amount DECIMAL(10,2)
);

-- 插入数据（IDENTITY列自动生成）
INSERT INTO Orders (CustomerID, Amount) VALUES
(1001, 299.99),
(1002, 599.99),
(1003, 199.99);

-- 获取最后插入的IDENTITY值
SELECT SCOPE_IDENTITY() AS LastInsertedID;
SELECT @@IDENTITY AS LastIdentity;
SELECT IDENT_CURRENT('Orders') AS CurrentIdentity;

-- 重置IDENTITY值
DBCC CHECKIDENT('Orders', RESEED, 1000);

-- 创建序列（SQL Server 2012+）
CREATE SEQUENCE OrderNumberSequence
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999
    CYCLE
    CACHE 50;

-- 使用序列
CREATE TABLE OrdersWithSequence (
    OrderID INT PRIMARY KEY DEFAULT NEXT VALUE FOR OrderNumberSequence,
    CustomerID INT,
    OrderDate DATETIME2 DEFAULT GETDATE(),
    Amount DECIMAL(10,2)
);

-- 获取序列值
SELECT NEXT VALUE FOR OrderNumberSequence AS NextOrderNumber;

-- 查看序列信息
SELECT 
    name,
    current_value,
    increment,
    minimum_value,
    maximum_value,
    is_cycling,
    cache_size
FROM sys.sequences;

2. 窗口函数和分析函数

-- 创建销售数据表
CREATE TABLE Sales (
    SaleID INT IDENTITY(1,1) PRIMARY KEY,
    Salesperson NVARCHAR(50),
    Department NVARCHAR(50),
    SaleAmount DECIMAL(10,2),
    SaleDate DATE
);

-- 插入测试数据
INSERT INTO Sales (Salesperson, Department, SaleAmount, SaleDate) VALUES
(N'张三', N'销售部', 10000, '2023-01-15'),
(N'李四', N'销售部', 15000, '2023-01-20'),
(N'王五', N'市场部', 12000, '2023-02-10'),
(N'张三', N'销售部', 8000, '2023-02-15'),
(N'李四', N'销售部', 20000, '2023-03-05'),
(N'王五', N'市场部', 18000, '2023-03-10');

-- 窗口函数示例
SELECT 
    Salesperson,
    Department,
    SaleAmount,
    SaleDate,
    
    -- 排名函数
    ROW_NUMBER() OVER (ORDER BY SaleAmount DESC) AS RowNum,
    RANK() OVER (ORDER BY SaleAmount DESC) AS RankNum,
    DENSE_RANK() OVER (ORDER BY SaleAmount DESC) AS DenseRank,
    PERCENT_RANK() OVER (ORDER BY SaleAmount) AS PercentRank,
    CUME_DIST() OVER (ORDER BY SaleAmount) AS CumeDist,
    
    -- 分区排名
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY SaleAmount DESC) AS DeptRank,
    
    -- 聚合窗口函数
    SUM(SaleAmount) OVER (PARTITION BY Department) AS DeptTotal,
    AVG(SaleAmount) OVER (PARTITION BY Salesperson) AS PersonAvg,
    COUNT(*) OVER (PARTITION BY Department) AS DeptCount,
    
    -- 移动窗口
    SUM(SaleAmount) OVER (
        ORDER BY SaleDate 
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS MovingSum,
    
    -- 累计计算
    SUM(SaleAmount) OVER (
        ORDER BY SaleDate 
        ROWS UNBOUNDED PRECEDING
    ) AS CumulativeSum,
    
    -- 前后值比较
    LAG(SaleAmount, 1) OVER (ORDER BY SaleDate) AS PrevAmount,
    LEAD(SaleAmount, 1) OVER (ORDER BY SaleDate) AS NextAmount,
    
    -- 首尾值
    FIRST_VALUE(SaleAmount) OVER (
        PARTITION BY Department ORDER BY SaleDate
        ROWS UNBOUNDED PRECEDING
    ) AS FirstSale,
    LAST_VALUE(SaleAmount) OVER (
        PARTITION BY Department ORDER BY SaleDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastSale,
    
    -- 分位数
    NTILE(4) OVER (ORDER BY SaleAmount) AS Quartile,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SaleAmount) OVER (PARTITION BY Department) AS MedianAmount,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SaleAmount) OVER (PARTITION BY Department) AS MedianAmountDisc
    
FROM Sales
ORDER BY SaleDate;

3. 公共表表达式 (CTE)

-- 简单CTE
WITH DepartmentStats AS (
    SELECT 
        Department,
        COUNT(*) AS SaleCount,
        SUM(SaleAmount) AS TotalSales,
        AVG(SaleAmount) AS AvgSale
    FROM Sales
    GROUP BY Department
),
TopDepartments AS (
    SELECT Department, TotalSales
    FROM DepartmentStats
    WHERE TotalSales > 30000
)
SELECT 
    ds.Department,
    ds.SaleCount,
    ds.TotalSales,
    ds.AvgSale,
    CASE WHEN td.Department IS NOT NULL THEN N'顶级部门' ELSE N'普通部门' END AS DeptLevel
FROM DepartmentStats ds
LEFT JOIN TopDepartments td ON ds.Department = td.Department
ORDER BY ds.TotalSales DESC;

-- 递归CTE - 组织结构
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(50),
    ManagerID INT REFERENCES Employees(EmployeeID)
);

INSERT INTO Employees VALUES
(1, N'总经理', NULL),
(2, N'销售总监', 1),
(3, N'技术总监', 1),
(4, N'销售经理', 2),
(5, N'开发经理', 3),
(6, N'销售代表', 4),
(7, N'开发工程师', 5);

-- 递归查询组织层级
WITH EmployeeHierarchy AS (
    -- 锚点：顶级管理者
    SELECT 
        EmployeeID, 
        EmployeeName, 
        ManagerID, 
        0 AS Level,
        CAST(EmployeeName AS NVARCHAR(1000)) AS Path
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- 递归：下级员工
    SELECT 
        e.EmployeeID, 
        e.EmployeeName, 
        e.ManagerID, 
        eh.Level + 1,
        CAST(eh.Path + N' -> ' + e.EmployeeName AS NVARCHAR(1000))
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT 
    REPLICATE('  ', Level) + EmployeeName AS Hierarchy,
    Level,
    Path
FROM EmployeeHierarchy
ORDER BY Path;

-- 递归CTE - 数字序列
WITH NumberSeries AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM NumberSeries
    WHERE n < 10
)
SELECT n, n * n AS Square, n * n * n AS Cube
FROM NumberSeries;

4. 存储过程和函数

-- 创建标量函数
CREATE FUNCTION dbo.CalculateAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE()) - 
           CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                THEN 1 ELSE 0 END;
END;
GO

-- 使用标量函数
SELECT dbo.CalculateAge('1990-05-15') AS Age;

-- 创建表值函数
CREATE FUNCTION dbo.GetSalesSummary(@DeptName NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT 
        Salesperson,
        SUM(SaleAmount) AS TotalAmount,
        COUNT(*) AS SaleCount,
        AVG(SaleAmount) AS AvgAmount
    FROM Sales
    WHERE Department = @DeptName
    GROUP BY Salesperson
);
GO

-- 调用表值函数
SELECT * FROM dbo.GetSalesSummary(N'销售部');

-- 创建多语句表值函数
CREATE FUNCTION dbo.GetSalesAnalysis(@StartDate DATE, @EndDate DATE)
RETURNS @Result TABLE (
    Department NVARCHAR(50),
    Salesperson NVARCHAR(50),
    TotalAmount DECIMAL(10,2),
    SaleCount INT,
    AvgAmount DECIMAL(10,2),
    Rank INT
)
AS
BEGIN
    INSERT INTO @Result
    SELECT 
        Department,
        Salesperson,
        SUM(SaleAmount) AS TotalAmount,
        COUNT(*) AS SaleCount,
        AVG(SaleAmount) AS AvgAmount,
        RANK() OVER (ORDER BY SUM(SaleAmount) DESC) AS Rank
    FROM Sales
    WHERE SaleDate BETWEEN @StartDate AND @EndDate
    GROUP BY Department, Salesperson;
    
    RETURN;
END;
GO

-- 调用多语句表值函数
SELECT * FROM dbo.GetSalesAnalysis('2023-01-01', '2023-12-31');

-- 创建存储过程
CREATE PROCEDURE dbo.UpdateSalesBonus
    @DeptName NVARCHAR(50),
    @BonusRate DECIMAL(5,4),
    @UpdatedCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 添加bonus列（如果不存在）
    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('Sales') AND name = 'Bonus')
    BEGIN
        ALTER TABLE Sales ADD Bonus DECIMAL(10,2) DEFAULT 0;
    END
    
    -- 更新奖金
    UPDATE Sales 
    SET Bonus = SaleAmount * @BonusRate 
    WHERE Department = @DeptName;
    
    SET @UpdatedCount = @@ROWCOUNT;
    
    PRINT N'更新了 ' + CAST(@UpdatedCount AS NVARCHAR(10)) + N' 条记录的奖金';
END;
GO

-- 调用存储过程
DECLARE @Count INT;
EXEC dbo.UpdateSalesBonus N'销售部', 0.1, @Count OUTPUT;
SELECT @Count AS UpdatedRecords;

-- 创建带事务的存储过程
CREATE PROCEDURE dbo.TransferSales
    @FromSalesperson NVARCHAR(50),
    @ToSalesperson NVARCHAR(50),
    @SaleID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 检查销售记录是否存在
        IF NOT EXISTS (SELECT 1 FROM Sales WHERE SaleID = @SaleID AND Salesperson = @FromSalesperson)
        BEGIN
            RAISERROR(N'销售记录不存在或不属于指定销售员', 16, 1);
            RETURN;
        END
        
        -- 更新销售记录
        UPDATE Sales 
        SET Salesperson = @ToSalesperson
        WHERE SaleID = @SaleID;
        
        -- 记录操作日志
        INSERT INTO SalesAuditLog (SaleID, OldSalesperson, NewSalesperson, TransferDate)
        VALUES (@SaleID, @FromSalesperson, @ToSalesperson, GETDATE());
        
        COMMIT TRANSACTION;
        PRINT N'销售记录转移成功';
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
            
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END;
GO

5. 触发器

-- 创建审计日志表
CREATE TABLE SalesAuditLog (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    SaleID INT,
    Operation NVARCHAR(10),
    OldSalesperson NVARCHAR(50),
    NewSalesperson NVARCHAR(50),
    OldAmount DECIMAL(10,2),
    NewAmount DECIMAL(10,2),
    TransferDate DATETIME2 DEFAULT GETDATE(),
    UserName NVARCHAR(128) DEFAULT SUSER_SNAME()
);

-- 创建AFTER触发器
CREATE TRIGGER tr_Sales_Audit
ON Sales
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 处理INSERT
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO SalesAuditLog (SaleID, Operation, NewSalesperson, NewAmount)
        SELECT SaleID, 'INSERT', Salesperson, SaleAmount
        FROM inserted;
    END
    
    -- 处理UPDATE
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO SalesAuditLog (SaleID, Operation, OldSalesperson, NewSalesperson, OldAmount, NewAmount)
        SELECT 
            i.SaleID, 
            'UPDATE', 
            d.Salesperson, 
            i.Salesperson,
            d.SaleAmount,
            i.SaleAmount
        FROM inserted i
        INNER JOIN deleted d ON i.SaleID = d.SaleID;
    END
    
    -- 处理DELETE
    IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO SalesAuditLog (SaleID, Operation, OldSalesperson, OldAmount)
        SELECT SaleID, 'DELETE', Salesperson, SaleAmount
        FROM deleted;
    END
END;
GO

-- 创建INSTEAD OF触发器（用于视图）
CREATE VIEW vw_SalesSummary AS
SELECT 
    Department,
    Salesperson,
    COUNT(*) AS SaleCount,
    SUM(SaleAmount) AS TotalAmount
FROM Sales
GROUP BY Department, Salesperson;
GO

CREATE TRIGGER tr_SalesSummary_Update
ON vw_SalesSummary
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 这里可以定义如何处理对视图的更新
    RAISERROR(N'不能直接更新汇总视图', 16, 1);
END;
GO

-- 创建DDL触发器
CREATE TRIGGER tr_Database_DDL
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @EventData XML = EVENTDATA();
    
    INSERT INTO DDLAuditLog (EventType, ObjectName, UserName, EventTime, EventData)
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME2'),
        @EventData
    );
END;
GO

6. 索引和分区

-- 创建聚集索引
CREATE CLUSTERED INDEX IX_Sales_SaleDate ON Sales (SaleDate);

-- 创建非聚集索引
CREATE NONCLUSTERED INDEX IX_Sales_Salesperson ON Sales (Salesperson);

-- 创建复合索引
CREATE NONCLUSTERED INDEX IX_Sales_Dept_Amount ON Sales (Department, SaleAmount);

-- 创建包含列的索引
CREATE NONCLUSTERED INDEX IX_Sales_Dept_Include 
ON Sales (Department) 
INCLUDE (Salesperson, SaleAmount);

-- 创建筛选索引
CREATE NONCLUSTERED INDEX IX_Sales_HighAmount 
ON Sales (SaleAmount) 
WHERE SaleAmount > 10000;

-- 创建列存储索引
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore 
ON Sales (Department, Salesperson, SaleAmount, SaleDate);

-- 分区函数和方案
CREATE PARTITION FUNCTION pf_SalesDate (DATE)
AS RANGE RIGHT FOR VALUES 
('2023-01-01', '2023-04-01', '2023-07-01', '2023-10-01');

CREATE PARTITION SCHEME ps_SalesDate
AS PARTITION pf_SalesDate
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

-- 创建分区表
CREATE TABLE SalesPartitioned (
    SaleID INT IDENTITY(1,1),
    Salesperson NVARCHAR(50),
    Department NVARCHAR(50),
    SaleAmount DECIMAL(10,2),
    SaleDate DATE,
    CONSTRAINT PK_SalesPartitioned PRIMARY KEY (SaleID, SaleDate)
) ON ps_SalesDate (SaleDate);

-- 查看分区信息
SELECT 
    p.partition_number,
    p.rows,
    rv.value AS boundary_value,
    fg.name AS filegroup_name
FROM sys.partitions p
JOIN sys.partition_schemes ps ON p.partition_id = ps.data_space_id
JOIN sys.partition_range_values rv ON ps.function_id = rv.function_id 
    AND p.partition_number = rv.boundary_id + 1
JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id 
    AND p.partition_number = dds.destination_id
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
WHERE p.object_id = OBJECT_ID('SalesPartitioned');

XML 数据处理

1. XML 数据类型

-- 创建包含XML的表
CREATE TABLE ProductCatalog (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100),
    ProductDetails XML
);

-- 插入XML数据
INSERT INTO ProductCatalog (ProductName, ProductDetails) VALUES
(N'笔记本电脑', '
<product>
    <specifications>
        <cpu>Intel i7</cpu>
        <memory>16GB</memory>
        <storage>512GB SSD</storage>
        <display>15.6英寸</display>
    </specifications>
    <features>
        <feature>轻薄便携</feature>
        <feature>长续航</feature>
        <feature>高性能</feature>
    </features>
    <price currency="CNY">8999</price>
</product>'),
(N'智能手机', '
<product>
    <specifications>
        <cpu>骁龙888</cpu>
        <memory>8GB</memory>
        <storage>256GB</storage>
        <display>6.7英寸</display>
    </specifications>
    <features>
        <feature>5G网络</feature>
        <feature>快速充电</feature>
        <feature>多摄像头</feature>
    </features>
    <price currency="CNY">4999</price>
</product>');

-- XML查询方法
SELECT 
    ProductID,
    ProductName,
    -- 提取单个值
    ProductDetails.value('(/product/specifications/cpu)[1]', 'NVARCHAR(50)') AS CPU,
    ProductDetails.value('(/product/specifications/memory)[1]', 'NVARCHAR(20)') AS Memory,
    ProductDetails.value('(/product/price)[1]', 'DECIMAL(10,2)') AS Price,
    ProductDetails.value('(/product/price/@currency)[1]', 'NVARCHAR(10)') AS Currency,
    
    -- 检查节点是否存在
    ProductDetails.exist('/product/features/feature[text()="5G网络"]') AS Has5G,
    
    -- 提取多个值
    ProductDetails.query('/product/features/feature') AS Features
FROM ProductCatalog;

-- 使用nodes()方法展开XML
SELECT 
    pc.ProductID,
    pc.ProductName,
    Feature.value('.', 'NVARCHAR(50)') AS FeatureName
FROM ProductCatalog pc
CROSS APPLY pc.ProductDetails.nodes('/product/features/feature') AS T(Feature);

-- XML修改
UPDATE ProductCatalog
SET ProductDetails.modify('
    insert <feature>无线充电</feature>
    into (/product/features)[1]
')
WHERE ProductID = 2;

UPDATE ProductCatalog
SET ProductDetails.modify('
    replace value of (/product/price/text())[1]
    with "9999"
')
WHERE ProductID = 1;

UPDATE ProductCatalog
SET ProductDetails.modify('
    delete /product/features/feature[text()="高性能"]
')
WHERE ProductID = 1;

-- XML索引
CREATE PRIMARY XML INDEX IX_ProductDetails_Primary ON ProductCatalog (ProductDetails);
CREATE XML INDEX IX_ProductDetails_Path ON ProductCatalog (ProductDetails)
USING XML INDEX IX_ProductDetails_Primary FOR PATH;
CREATE XML INDEX IX_ProductDetails_Value ON ProductCatalog (ProductDetails)
USING XML INDEX IX_ProductDetails_Primary FOR VALUE;

2. FOR XML 查询

-- FOR XML RAW
SELECT 
    SaleID,
    Salesperson,
    Department,
    SaleAmount
FROM Sales
FOR XML RAW('Sale'), ROOT('Sales');

-- FOR XML AUTO
SELECT 
    s.Salesperson,
    s.Department,
    s.SaleAmount,
    s.SaleDate
FROM Sales s
FOR XML AUTO, ROOT('SalesData');

-- FOR XML EXPLICIT
SELECT 
    1 AS Tag,
    NULL AS Parent,
    Salesperson AS [Sale!1!Salesperson],
    Department AS [Sale!1!Department],
    SaleAmount AS [Sale!1!Amount]
FROM Sales
FOR XML EXPLICIT;

-- FOR XML PATH
SELECT 
    Salesperson AS '@name',
    Department AS '@dept',
    SaleAmount AS 'amount',
    SaleDate AS 'date'
FROM Sales
FOR XML PATH('sale'), ROOT('sales');

-- 嵌套XML结构
SELECT 
    Department AS '@name',
    (
        SELECT 
            Salesperson AS '@name',
            SaleAmount AS '@amount',
            SaleDate AS '@date'
        FROM Sales s2
        WHERE s2.Department = s1.Department
        FOR XML PATH('sale'), TYPE
    ) AS 'sales'
FROM (SELECT DISTINCT Department FROM Sales) s1
FOR XML PATH('department'), ROOT('company');

性能优化

1. 查询优化

-- 查看执行计划
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT 
    s.Salesperson,
    s.Department,
    SUM(s.SaleAmount) AS Total
FROM Sales s
WHERE s.SaleDate >= '2023-01-01'
GROUP BY s.Salesperson, s.Department
HAVING SUM(s.SaleAmount) > 20000
ORDER BY Total DESC;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- 使用查询提示
SELECT /*+ INDEX(Sales, IX_Sales_SaleDate) */
    Salesperson,
    SaleAmount
FROM Sales WITH (INDEX(IX_Sales_SaleDate))
WHERE SaleDate >= '2023-01-01';

-- 强制并行查询
SELECT 
    Department,
    SUM(SaleAmount) AS Total
FROM Sales
GROUP BY Department
OPTION (MAXDOP 4);

-- 查看查询统计信息
SELECT 
    qs.execution_count,
    qs.total_elapsed_time / 1000 AS total_elapsed_time_ms,
    qs.total_worker_time / 1000 AS total_cpu_time_ms,
    qs.total_logical_reads,
    qs.total_physical_reads,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;

-- 查看等待统计
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
    'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
    'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE'
)
ORDER BY wait_time_ms DESC;

2. 索引优化

-- 查看索引使用情况
SELECT 
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s 
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE i.object_id = OBJECT_ID('Sales')
ORDER BY s.user_seeks + s.user_scans + s.user_lookups DESC;

-- 查看缺失的索引
SELECT 
    mid.statement AS TableName,
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(mid.statement, '[', ''), ']', ''), '.', '_') + '_Missing'
    + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL(mid.inequality_columns, '') + ')' 
    + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY improvement_measure DESC;

-- 查看索引碎片
SELECT 
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Sales'), NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 重建索引
ALTER INDEX IX_Sales_SaleDate ON Sales REBUILD;

-- 重组索引
ALTER INDEX IX_Sales_Salesperson ON Sales REORGANIZE;

-- 更新统计信息
UPDATE STATISTICS Sales;

备份与恢复

1. 备份策略

-- 完整备份
BACKUP DATABASE [MyDatabase] 
TO DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH FORMAT, INIT, COMPRESSION, CHECKSUM;

-- 差异备份
BACKUP DATABASE [MyDatabase] 
TO DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM;

-- 事务日志备份
BACKUP LOG [MyDatabase] 
TO DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH COMPRESSION, CHECKSUM;

-- 查看备份历史
SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type,
    CASE bs.type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    bs.backup_size / 1024 / 1024 AS backup_size_mb,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'MyDatabase'
ORDER BY bs.backup_start_date DESC;

2. 恢复操作

-- 完整恢复
RESTORE DATABASE [MyDatabase] 
FROM DISK = 'C:\Backup\MyDatabase_Full.bak'
WITH REPLACE, NORECOVERY;

-- 差异恢复
RESTORE DATABASE [MyDatabase] 
FROM DISK = 'C:\Backup\MyDatabase_Diff.bak'
WITH NORECOVERY;

-- 日志恢复
RESTORE LOG [MyDatabase] 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH RECOVERY;

-- 时间点恢复
RESTORE LOG [MyDatabase] 
FROM DISK = 'C:\Backup\MyDatabase_Log.trn'
WITH RECOVERY, STOPAT = '2023-12-25 14:30:00';

-- 查看恢复进度
SELECT 
    session_id,
    command,
    percent_complete,
    estimated_completion_time,
    start_time
FROM sys.dm_exec_requests
WHERE command LIKE 'RESTORE%';

高可用性和灾难恢复

1. Always On 可用性组

-- 启用Always On
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'hadr enabled', 1;
RECONFIGURE;

-- 创建可用性组
CREATE AVAILABILITY GROUP [MyAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [MyDatabase]
REPLICA ON 
    'SERVER1' WITH (
        ENDPOINT_URL = 'TCP://SERVER1:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 30
    ),
    'SERVER2' WITH (
        ENDPOINT_URL = 'TCP://SERVER2:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 30
    ),
    'SERVER3' WITH (
        ENDPOINT_URL = 'TCP://SERVER3:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        FAILOVER_MODE = MANUAL,
        BACKUP_PRIORITY = 10
    );

-- 查看可用性组状态
SELECT 
    ag.name AS availability_group,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ars.role_desc,
    ars.operational_state_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

2. 数据库镜像

-- 在主体服务器上设置镜像
ALTER DATABASE [MyDatabase] 
SET PARTNER = 'TCP://MIRROR_SERVER:5022';

-- 在镜像服务器上设置镜像
ALTER DATABASE [MyDatabase] 
SET PARTNER = 'TCP://PRINCIPAL_SERVER:5022';

-- 设置见证服务器
ALTER DATABASE [MyDatabase] 
SET WITNESS = 'TCP://WITNESS_SERVER:5022';

-- 查看镜像状态
SELECT 
    database_name,
    mirroring_state_desc,
    mirroring_role_desc,
    mirroring_safety_level_desc,
    mirroring_partner_name,
    mirroring_witness_name
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;

最佳实践

1. 数据库设计

• 合理选择数据类型（使用NVARCHAR而不是VARCHAR存储Unicode字符）
• 使用IDENTITY列或SEQUENCE生成主键
• 适当使用约束和触发器
• 考虑使用分区表处理大数据量

2. 查询优化

• 使用SET NOCOUNT ON减少网络流量
• 避免在WHERE子句中使用函数
• 合理使用窗口函数替代子查询
• 使用参数化查询防止SQL注入

3. 索引策略

• 在经常查询的列上创建索引
• 使用包含列索引减少键查找
• 定期维护索引碎片
• 监控索引使用情况

4. 性能监控

• 使用动态管理视图监控性能
• 启用查询存储分析查询性能
• 定期检查等待统计信息
• 监控阻塞和死锁

5. 安全配置

• 使用Windows身份验证
• 实施最小权限原则
• 启用透明数据加密（TDE）
• 定期更新SQL Server版本

6. 备份策略

• 制定完整、差异和日志备份计划
• 定期测试备份恢复
• 考虑异地备份存储
• 使用压缩减少备份大小

通过掌握这些SQL Server特定的功能和最佳实践，可以充分利用SQL Server的强大特性，构建高性能、可靠的数据库应用。