印刻数据库

外观

SQLServer SQL编写规范 ​

规范概述 ​

规范的重要性 ​

在生产环境中，统一的SQL编写规范对于数据库系统的开发、维护和性能至关重要。良好的SQL规范可以：

  • 提高代码的可读性和可维护性
  • 减少开发和维护成本
  • 提高SQL语句的性能
  • 增强系统的安全性
  • 便于团队协作和知识传承

规范的基本原则 ​

  • 一致性：在整个项目中保持一致的命名和格式
  • 可读性：编写易于理解和维护的SQL代码
  • 性能优先：优先考虑SQL语句的性能
  • 安全性：避免安全隐患，如SQL注入
  • 可扩展性：考虑未来的业务扩展和系统升级

规范的适用范围 ​

本规范适用于所有SQL Server数据库开发活动，包括：

  • 数据库设计和开发
  • 存储过程、函数和触发器编写
  • 视图和索引创建
  • SQL查询和报表编写

命名规范 ​

数据库命名 ​

生产环境实践：

  • 数据库名称应反映业务功能，使用简洁明了的英文名称
  • 命名格式：[业务域]_[功能]，如 ECommerce_OrderManagement
  • 避免使用特殊字符和空格
  • 长度控制在30个字符以内

表命名 ​

生产环境实践：

  • 表名应使用名词或名词短语，反映表的用途
  • 命名格式：t_[业务模块]_[表名]，如 t_Order_Header、t_Customer_Info
  • 使用复数形式表示包含多个实例的表
  • 避免使用SQL Server保留字

列命名 ​

生产环境实践：

  • 列名应使用简洁明了的英文名称，反映列的用途
  • 使用小写字母加下划线分隔，如 order_id、customer_name
  • 主键列命名为 [表名缩写]_id，如 order_id、product_id
  • 外键列命名为 [关联表名缩写]_id，如 customer_id、product_id
  • 日期列使用 _date 后缀，如 create_date、update_date
  • 时间列使用 _time 后缀，如 login_time、logout_time

索引命名 ​

生产环境实践：

  • 聚集索引命名格式：PK_[表名]，如 PK_t_Order_Header
  • 唯一索引命名格式：UK_[表名]_[列名]，如 UK_t_Customer_Email
  • 非聚集索引命名格式：IX_[表名]_[列名1]_[列名2]，如 IX_t_Order_Header_CustomerID_OrderDate
  • 覆盖索引命名格式：CX_[表名]_[列名1]_[列名2]，如 CX_t_Order_Header_CustomerID_TotalAmount

存储过程命名 ​

生产环境实践：

  • 存储过程名称应反映其功能，使用动词开头
  • 命名格式：sp_[功能模块]_[操作]_[对象]，如 sp_Order_GetOrderDetails、sp_Customer_InsertCustomer
  • 避免使用 sp_ 前缀（这是系统存储过程的保留前缀），建议使用 usp_ 或业务前缀
  • 示例：usp_Order_GetByCustomerID、usp_Product_UpdateStock

函数命名 ​

生产环境实践：

  • 函数名称应反映其功能，使用动词开头
  • 标量函数命名格式：fn_[功能]，如 fn_CalculateDiscount、fn_FormatPhoneNumber
  • 表值函数命名格式：tvf_[功能]，如 tvf_GetActiveCustomers、tvf_GetOrderItems

触发器命名 ​

生产环境实践：

  • 触发器名称应反映触发事件和表名
  • 命名格式：tr_[表名]_[触发事件]，如 tr_t_Order_Header_Insert、tr_t_Customer_Update
  • 触发事件包括：Insert、Update、Delete、InsteadOfInsert 等

视图命名 ​

生产环境实践：

  • 视图名称应反映其用途，使用 v_ 前缀
  • 命名格式：v_[业务模块]_[视图名]，如 v_Order_CustomerOrders、v_Product_Inventory

变量命名 ​

生产环境实践：

  • 变量名应使用有意义的英文名称
  • 使用 @ 符号开头，如 @CustomerID、@OrderDate
  • 局部变量使用驼峰命名法，如 @customerName、@orderTotal
  • 避免使用单个字符作为变量名（除了循环变量）

常量命名 ​

生产环境实践：

  • 常量名应使用大写字母，下划线分隔
  • 使用 @ 符号开头，如 @MAX_RETRY_COUNT、@DEFAULT_PAGE_SIZE
  • 在存储过程或函数开头声明所有常量

格式规范 ​

SQL语句格式 ​

生产环境实践：

  • 每个关键字独占一行，如 SELECT、FROM、WHERE、JOIN 等
  • 列名垂直对齐，便于阅读
  • 示例：
    sql
    SELECT
    o.order_id,
    o.customer_id,
    o.order_date,
    o.total_amount,
    c.customer_name,
    c.email
FROM
    t_Order_Header o
INNER JOIN
    t_Customer_Info c ON o.customer_id = c.customer_id
WHERE
    o.order_date >= '2023-01-01'
    AND o.total_amount > 1000;

缩进与对齐 ​

生产环境实践：

  • 使用4个空格进行缩进，避免使用制表符
  • 子句（如 WHERE、JOIN）缩进一级
  • 条件表达式垂直对齐
  • 示例：
    sql
    SELECT
    column1,
    column2
FROM
    table1
WHERE
    condition1 = value1
    AND condition2 = value2
    OR (
        condition3 = value3
        AND condition4 = value4
    );

大小写规范 ​

生产环境实践：

  • SQL关键字使用大写，如 SELECT、FROM、WHERE、JOIN 等
  • 数据库对象名（表、列、存储过程等）使用小写字母加下划线
  • 函数名使用大写，如 GETDATE()、SUM()、COUNT() 等
  • 变量名使用驼峰命名法

注释规范 ​

生产环境实践：

  • 为复杂的SQL语句添加注释，说明其功能和逻辑
  • 使用 -- 进行单行注释，/* */ 进行多行注释
  • 注释应简洁明了，避免冗余
  • 示例：
    sql
    -- 获取2023年1月1日以后的订单，金额大于1000
SELECT
    o.order_id,
    o.total_amount
FROM
    t_Order_Header o
WHERE
    o.order_date >= '2023-01-01' -- 筛选日期范围
    AND o.total_amount > 1000; -- 筛选金额

空格与换行 ​

生产环境实践：

  • 在逗号后添加空格，如 column1, column2, column3
  • 在操作符前后添加空格，如 a = b、c > d
  • 避免行尾空格
  • 长SQL语句应适当换行，每行长度控制在120个字符以内

性能规范 ​

查询性能规范 ​

生产环境实践：

  • 只查询必要的列，避免使用 SELECT *：
    sql
    -- 低效
SELECT * FROM t_Order_Header;

-- 高效
SELECT order_id, customer_id, order_date FROM t_Order_Header;
  • 使用 WHERE 子句过滤数据，减少返回行数
  • 避免在 WHERE 子句中使用函数，否则会导致索引失效：
    sql
    -- 低效：函数导致索引失效
SELECT * FROM t_Order_Header WHERE YEAR(order_date) = 2023;

-- 高效：直接比较列值
SELECT * FROM t_Order_Header WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
  • 使用 EXISTS 代替 IN，特别是当子查询返回大量结果时

索引使用规范 ​

生产环境实践：

  • 为 WHERE 子句、JOIN 条件和 ORDER BY 子句中频繁使用的列创建索引
  • 考虑索引的选择性，选择性越高，索引效果越好
  • 避免过度索引，每个表的索引数量建议不超过5-10个
  • 定期维护索引，重建或重新组织碎片化的索引

SQL Server 2016+：

  • 使用 Query Store 识别需要索引的查询
  • 利用自动调优功能，让SQL Server推荐和创建索引

事务使用规范 ​

生产环境实践：

  • 尽量将事务保持在最短时间内
  • 避免在事务中进行网络调用、文件操作或用户交互
  • 使用显式事务而非隐式事务，便于控制事务范围
  • 示例：
    sql
    BEGIN TRANSACTION;

-- 执行操作1
INSERT INTO t_Order_Header (...) VALUES (...);

-- 执行操作2
INSERT INTO t_Order_Item (...) VALUES (...);

-- 提交事务
COMMIT TRANSACTION;

存储过程性能规范 ​

生产环境实践：

  • 避免在存储过程中使用动态SQL，如必须使用，应使用参数化查询
  • 优化存储过程中的查询，确保使用了合适的索引
  • 避免在循环中执行昂贵的操作
  • 使用 SET NOCOUNT ON 减少网络流量
  • 考虑使用 natively compiled 存储过程（SQL Server 2014+）提高性能

安全规范 ​

SQL注入防护 ​

生产环境实践：

  • 使用参数化查询或存储过程，避免直接拼接SQL语句
  • 示例：
    sql
    -- 不安全：直接拼接SQL
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM t_User WHERE username = ''' + @Username + '''';
EXEC(@SQL);

-- 安全：使用参数化查询
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'SELECT * FROM t_User WHERE username = @Username';
EXEC sp_executesql @SQL, N'@Username NVARCHAR(50)', @Username = @Username;
  • 对用户输入进行验证和过滤
  • 限制数据库用户的权限，遵循最小权限原则

权限规范 ​

生产环境实践：

  • 避免直接授予用户权限，应通过角色管理
  • 为应用程序使用专用服务账户，只授予必要的权限
  • 定期审查和更新权限
  • 禁用或重命名 sa 登录名

数据加密规范 ​

生产环境实践：

  • 对敏感数据进行加密，如信用卡号、身份证号等
  • SQL Server 2008+：使用透明数据加密 (TDE) 加密整个数据库
  • SQL Server 2016+：使用 Always Encrypted 加密敏感列
  • 对备份文件进行加密

敏感数据处理 ​

生产环境实践：

  • 识别和分类敏感数据
  • SQL Server 2016+：使用动态数据掩码限制敏感数据的可见性
  • SQL Server 2016+：使用行级安全控制对敏感数据的访问
  • 避免在日志中记录敏感数据

可维护性规范 ​

代码可读性 ​

生产环境实践：

  • 编写简洁明了的SQL代码，避免复杂的嵌套查询
  • 使用视图或表值函数简化复杂查询
  • 为复杂的逻辑添加注释
  • 遵循一致的命名和格式规范

代码模块化 ​

生产环境实践：

  • 将复杂的业务逻辑拆分为多个存储过程或函数
  • 使用视图封装常用的查询逻辑
  • 建立代码库，复用常用的SQL代码片段

错误处理 ​

生产环境实践：

  • 在存储过程和函数中添加错误处理
  • 使用 TRY...CATCH 块捕获和处理错误
  • 记录错误信息，包括错误号、错误消息和发生位置
  • 示例：
    sql
    BEGIN TRY
    -- 执行操作
    INSERT INTO t_Order_Header (...) VALUES (...);
END TRY
BEGIN CATCH
    -- 处理错误
    DECLARE @ErrorMessage NVARCHAR(4000);
    SET @ErrorMessage = ERROR_MESSAGE();
    
    -- 记录错误
    INSERT INTO t_Error_Log (error_message, error_date) VALUES (@ErrorMessage, GETDATE());
    
    -- 重新抛出错误
    THROW;
END CATCH;

日志记录 ​

生产环境实践：

  • 为重要的操作添加日志记录
  • 记录操作的执行时间、执行结果和执行用户
  • 定期清理日志表，避免日志表过大
  • 示例：
    sql
    -- 记录操作开始
INSERT INTO t_Operation_Log (operation_name, start_time, executed_by) 
VALUES ('UpdateOrderStatus', GETDATE(), USER_NAME());

-- 执行操作
UPDATE t_Order_Header SET order_status = 'Shipped' WHERE order_id = @OrderID;

-- 记录操作结束
UPDATE t_Operation_Log 
SET end_time = GETDATE(), status = 'Success' 
WHERE operation_log_id = SCOPE_IDENTITY();

最佳实践 ​

开发工具使用 ​

生产环境实践：

  • 使用 SQL Server Management Studio (SSMS) 进行数据库开发和管理
  • SQL Server 2012+：使用 SQL Server Data Tools (SSDT) 进行数据库项目开发
  • 利用代码格式化工具，如 SQL Prompt、ApexSQL Refactor 等
  • 使用版本控制工具管理SQL代码

版本控制 ​

生产环境实践：

  • 将所有数据库对象和脚本纳入版本控制
  • 使用 Git、SVN 等版本控制工具
  • 建立分支管理策略，如 Git Flow
  • 为每个版本创建标签

代码审查 ​

生产环境实践：

  • 建立代码审查制度，确保代码质量
  • 审查代码的正确性、性能和安全性
  • 使用代码审查工具，如 Pull Request、Code Review Board 等
  • 记录审查意见和修改建议

测试规范 ​

生产环境实践：

  • 为SQL代码编写测试用例
  • 使用单元测试工具，如 tSQLt、SQL Server Unit Testing 等
  • 测试边界条件和异常情况
  • 自动化测试，便于持续集成

版本差异 ​

SQL Server 2008/2008 R2 ​

  • 支持基本的T-SQL语法和功能
  • 支持透明数据加密 (TDE)
  • 支持MERGE语句
  • 有限的并行查询支持

SQL Server 2012 ​

  • 引入序列 (SEQUENCE) 对象
  • 增强了窗口函数支持
  • 引入 OFFSET FETCH 子句，简化分页查询
  • 支持 Columnstore 索引，优化数据仓库查询

SQL Server 2014 ​

  • 引入内存优化表和 natively compiled 存储过程
  • 增强了 Cardinality Estimator
  • 引入备份加密功能
  • 增强了 AlwaysOn 可用性组

SQL Server 2016 ​

  • 支持 JSON 数据类型和 JSON 函数
  • 引入 Always Encrypted 功能
  • 引入动态数据掩码和行级安全
  • 引入 Query Store，便于监控和分析查询性能
  • 支持 R 语言集成

SQL Server 2017 ​

  • 支持 Linux 和 Docker 容器
  • 增强了 Intelligent Query Processing (IQP) 功能
  • 引入自适应查询处理
  • 支持 Python 语言集成
  • 增强了图形数据库支持

SQL Server 2019 ​

  • 引入 Big Data Clusters
  • 增强了 Intelligent Query Processing (IQP) 功能
  • 支持 UTF-8 字符集
  • 引入数据虚拟化功能
  • 增强了内存中 OLTP 功能

SQL Server 2022 ​

  • 引入 Ledger 功能，确保数据完整性
  • 增强了 Intelligent Query Processing 功能
  • 支持 Azure Synapse Link，实现实时数据分析
  • 增强了 Always Encrypted 功能
  • 支持 TLS 1.3

FAQ ​

为什么需要SQL编写规范？ ​

统一的SQL编写规范对于数据库系统的开发、维护和性能至关重要。良好的SQL规范可以提高代码的可读性和可维护性，减少开发和维护成本，提高SQL语句的性能，增强系统的安全性，便于团队协作和知识传承。

如何命名数据库对象？ ​

数据库对象命名应遵循简洁明了、反映功能、避免保留字的原则。具体命名规范包括：数据库使用业务域_功能格式，表使用t_业务模块_表名格式，列使用小写字母加下划线分隔，索引使用PK/UK/IX/CX前缀，存储过程使用usp_功能模块_操作_对象格式等。

如何格式化SQL语句？ ​

SQL语句格式化应遵循一致性、可读性和性能优先的原则。具体包括：每个关键字独占一行，列名垂直对齐，使用4个空格缩进，SQL关键字使用大写，数据库对象名使用小写字母加下划线，适当添加注释等。

如何编写高性能的SQL？ ​

编写高性能SQL的关键原则包括：只查询必要的列，避免使用SELECT *；使用WHERE子句过滤数据；避免在WHERE子句中使用函数；合理使用JOIN操作；创建合适的索引；优化事务管理；避免过度索引等。

如何编写安全的SQL？ ​

编写安全SQL的关键原则包括：使用参数化查询或存储过程，避免SQL注入；对用户输入进行验证和过滤；限制数据库用户的权限，遵循最小权限原则；对敏感数据进行加密；使用动态数据掩码和行级安全保护敏感数据等。

如何处理SQL错误？ ​

处理SQL错误应使用TRY...CATCH块捕获和处理错误，记录错误信息，包括错误号、错误消息和发生位置，并在必要时重新抛出错误。这样可以提高系统的可靠性和可维护性，便于问题排查和修复。

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

优化存储过程性能的关键措施包括：避免在存储过程中使用动态SQL；优化存储过程中的查询，确保使用了合适的索引；避免在循环中执行昂贵的操作；使用SET NOCOUNT ON减少网络流量；考虑使用natively compiled存储过程（SQL Server 2014+）提高性能等。

如何选择合适的索引？ ​

选择合适的索引需要考虑多个因素，包括：索引的选择性，选择性越高，索引效果越好；索引的覆盖范围，尽量覆盖查询所需的所有列；索引的维护成本，避免过度索引；查询的频率和复杂度等。

如何进行SQL代码审查？ ​

SQL代码审查应建立专门的审查制度，使用代码审查工具，审查代码的正确性、性能和安全性，记录审查意见和修改建议，并跟踪修改情况。代码审查可以提高代码质量，减少生产环境的问题和风险。

如何适应不同SQL Server版本的差异？ ​

适应不同SQL Server版本差异的关键是：了解各版本的新功能和改进；在开发过程中考虑版本兼容性；使用条件编译或版本检查处理版本差异；优先使用跨版本兼容的功能；定期升级到新版本，利用新功能提高性能和安全性。