SQLServer 数据库对象开发

存储过程

存储过程概述

存储过程是预编译的 SQL 代码块，可以接受参数、执行复杂的逻辑并返回结果。存储过程在 SQLServer 中广泛用于封装业务逻辑、提高性能和确保数据一致性。

存储过程类型

系统存储过程

系统存储过程以 sp_ 开头，由 SQLServer 提供，用于管理和维护数据库。例如：

sql

-- 查看数据库信息
exec sp_helpdb;

-- 查看表信息
exec sp_help 'dbo.Products';

用户自定义存储过程

用户自定义存储过程由开发人员创建，用于实现特定的业务逻辑。例如：

sql

CREATE PROCEDURE dbo.GetProductById
    @ProductId INT
AS
BEGIN
    SELECT * FROM dbo.Products WHERE ProductId = @ProductId;
END;

扩展存储过程

扩展存储过程以 xp_ 开头，用于调用外部 DLL 中的函数。在 SQLServer 2012 及以上版本中，建议使用 CLR 存储过程替代扩展存储过程。

存储过程最佳实践

命名规范：使用有意义的名称，避免使用 sp_ 前缀（会导致 SQLServer 先搜索 master 数据库）
参数验证：在存储过程中验证输入参数的合法性
错误处理：使用 TRY...CATCH 块处理异常
事务管理：合理使用事务，避免长时间锁定
性能优化：避免使用 SELECT *，使用 SET NOCOUNT ON 减少网络流量
版本兼容性：考虑不同 SQLServer 版本的语法差异

函数

函数类型

标量函数

返回单个值的函数，可以在 SELECT 语句中使用。

sql

CREATE FUNCTION dbo.CalculateDiscount
    (@Price DECIMAL(10,2), @DiscountPercent INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * (1 - @DiscountPercent / 100.0);
END;

表值函数

返回结果集的函数，可以像表一样在 FROM 子句中使用。

内联表值函数

sql

CREATE FUNCTION dbo.GetProductsByCategory
    (@CategoryId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT ProductId, ProductName, Price 
    FROM dbo.Products 
    WHERE CategoryId = @CategoryId
);

多语句表值函数

sql

CREATE FUNCTION dbo.GetProductsWithInventory
    (@MinStock INT)
RETURNS @Result TABLE (
    ProductId INT,
    ProductName NVARCHAR(100),
    Price DECIMAL(10,2),
    Stock INT
)
AS
BEGIN
    INSERT INTO @Result
    SELECT p.ProductId, p.ProductName, p.Price, i.Stock
    FROM dbo.Products p
    JOIN dbo.Inventory i ON p.ProductId = i.ProductId
    WHERE i.Stock >= @MinStock;
    
    RETURN;
END;

函数最佳实践

避免在函数中使用副作用操作：函数应该是确定性的，不应该修改数据库状态
性能考虑：内联表值函数通常比多语句表值函数性能更好
参数使用：合理使用参数，避免过度复杂的逻辑
返回类型选择：根据实际需求选择合适的返回类型

触发器

触发器概述

触发器是一种特殊的存储过程，当表或视图发生特定事件（INSERT、UPDATE、DELETE）时自动执行。

触发器类型

DML 触发器

数据操作语言触发器，用于响应表或视图上的 INSERT、UPDATE、DELETE 操作。

sql

CREATE TRIGGER dbo.TR_Products_Audit
ON dbo.Products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 记录删除操作
    INSERT INTO dbo.ProductAudit (
        ActionType, ProductId, ProductName, Price, ActionDate, ActionBy
    )
    SELECT
        'DELETE', d.ProductId, d.ProductName, d.Price, GETDATE(), SYSTEM_USER
    FROM deleted d;
    
    -- 记录插入操作
    INSERT INTO dbo.ProductAudit (
        ActionType, ProductId, ProductName, Price, ActionDate, ActionBy
    )
    SELECT
        'INSERT', i.ProductId, i.ProductName, i.Price, GETDATE(), SYSTEM_USER
    FROM inserted i;
    
    -- 记录更新操作
    INSERT INTO dbo.ProductAudit (
        ActionType, ProductId, OldProductName, NewProductName, OldPrice, NewPrice, ActionDate, ActionBy
    )
    SELECT
        'UPDATE', i.ProductId, d.ProductName, i.ProductName, d.Price, i.Price, GETDATE(), SYSTEM_USER
    FROM inserted i
    JOIN deleted d ON i.ProductId = d.ProductId;
END;

DDL 触发器

数据定义语言触发器，用于响应数据库或服务器上的 CREATE、ALTER、DROP 等操作。

sql

CREATE TRIGGER dbo.TR_Database_Change_Audit
ON DATABASE
AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @EventData XML = EVENTDATA();
    
    INSERT INTO dbo.DatabaseChangeAudit (
        EventType, ObjectName, ObjectType, SqlCommand, EventDate, EventBy
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'),
        GETDATE(),
        SYSTEM_USER;
END;

触发器最佳实践

保持简洁：触发器逻辑应该简单高效，避免复杂操作
避免递归：默认情况下，SQLServer 允许触发器递归调用，应根据需要禁用
事务管理：触发器在事务上下文中执行，应注意事务长度
性能考虑：频繁触发的触发器会影响性能，应谨慎使用
错误处理：使用 TRY...CATCH 块处理异常

视图

视图概述

视图是虚拟表，其内容由查询定义。视图可以简化复杂查询、限制数据访问和提供数据抽象。

视图类型

标准视图

基于一个或多个表的查询定义的视图。

sql

CREATE VIEW dbo.vw_ProductInventory
AS
SELECT 
    p.ProductId, 
    p.ProductName, 
    p.Price, 
    i.Stock,
    i.LastUpdated
FROM dbo.Products p
JOIN dbo.Inventory i ON p.ProductId = i.ProductId;

索引视图

创建了聚集索引的视图，可以提高查询性能，但会增加维护成本。

sql

CREATE VIEW dbo.vw_ProductSalesSummary
WITH SCHEMABINDING
AS
SELECT 
    p.CategoryId,
    SUM(s.Quantity) AS TotalQuantity,
    SUM(s.Quantity * p.Price) AS TotalSales,
    COUNT_BIG(*) AS RowCount
FROM dbo.Products p
JOIN dbo.Sales s ON p.ProductId = s.ProductId
GROUP BY p.CategoryId;

-- 创建聚集索引
CREATE UNIQUE CLUSTERED INDEX IX_vw_ProductSalesSummary_CategoryId
ON dbo.vw_ProductSalesSummary (CategoryId);

分区视图

将多个表的数据组合成一个视图，用于水平分区场景。

视图最佳实践

命名规范：使用 vw_ 前缀标识视图
性能考虑：索引视图适用于频繁查询的聚合数据
安全性：使用视图限制用户对敏感数据的访问
维护性：定期审查和更新视图定义

版本差异

SQLServer 2016+ 新特性

STRING_SPLIT 函数：用于拆分字符串
JSON 支持：增加了 JSON 数据类型和相关函数
TEMPORAL TABLES：系统版本化临时表，自动跟踪数据变更历史

SQLServer 2019+ 新特性

批处理模式改进：在更多场景下自动使用批处理模式
内存优化表改进：支持更多数据类型和功能
智能查询处理：包括近似查询处理、内存授予反馈等

Azure SQL Database 特性

自动优化：自动创建和删除索引
弹性池：资源共享的数据库池
无服务器计算层级：根据需求自动缩放计算资源

常见问题 (FAQ)

Q: 存储过程和函数的主要区别是什么？

A: 主要区别包括：

函数必须返回值，存储过程可以返回也可以不返回
函数可以在 SELECT 语句中使用，存储过程不行
函数不允许执行副作用操作（如修改表数据），存储过程可以

Q: 什么时候应该使用索引视图？

A: 索引视图适用于以下场景：

数据不经常变更
需要频繁查询聚合数据
查询结果集相对较小
服务器有足够的磁盘空间存储索引

Q: 触发器会影响性能吗？

A: 是的，触发器会影响性能，特别是：

频繁触发的触发器
包含复杂逻辑的触发器
执行大量操作的触发器

建议保持触发器逻辑简洁，只在必要时使用。

Q: 如何调试存储过程？

A: 可以使用以下方法调试存储过程：

在 SSMS 中使用调试器
添加 PRINT 语句输出调试信息
使用 SELECT 语句返回中间结果
使用 SQL Server Profiler 跟踪执行过程

Q: 如何优化存储过程性能？

A: 优化存储过程性能的方法包括：

使用 SET NOCOUNT ON 减少网络流量
避免使用 SELECT *
合理使用索引
避免在存储过程中使用游标
优化查询计划
合理使用参数化查询

Q: 如何处理存储过程中的错误？

A: 建议使用 TRY...CATCH 块处理存储过程中的错误，并使用 RAISERROR 或 THROW 语句返回错误信息。

sql

CREATE PROCEDURE dbo.UpdateProductPrice
    @ProductId INT,
    @NewPrice DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        UPDATE dbo.Products 
        SET Price = @NewPrice 
        WHERE ProductId = @ProductId;
        
        -- 其他操作...
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        
        -- 记录错误信息
        INSERT INTO dbo.ErrorLog (
            ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDate
        )
        VALUES (
            ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE()
        );
        
        -- 重新抛出错误
        THROW;
    END CATCH;
END;

SQLServer 数据库对象开发 ​

存储过程 ​

存储过程概述 ​

存储过程类型 ​

系统存储过程 ​

用户自定义存储过程 ​

扩展存储过程 ​

存储过程最佳实践 ​

函数 ​

函数类型 ​

标量函数 ​

表值函数 ​

内联表值函数 ​

多语句表值函数 ​

函数最佳实践 ​

触发器 ​

触发器概述 ​

触发器类型 ​

DML 触发器 ​

DDL 触发器 ​

触发器最佳实践 ​

视图 ​

视图概述 ​

视图类型 ​

标准视图 ​

索引视图 ​

分区视图 ​

视图最佳实践 ​

版本差异 ​

SQLServer 2016+ 新特性 ​

SQLServer 2019+ 新特性 ​

Azure SQL Database 特性 ​

常见问题 (FAQ) ​

Q: 存储过程和函数的主要区别是什么？ ​

Q: 什么时候应该使用索引视图？ ​

Q: 触发器会影响性能吗？ ​

Q: 如何调试存储过程？ ​

Q: 如何优化存储过程性能？ ​

Q: 如何处理存储过程中的错误？ ​