SQLServer SQL书写规范

SQL书写规范基础

良好的SQL书写规范不仅可以提高代码的可读性和可维护性，还可以避免一些常见的性能问题。制定和遵循SQL书写规范是DBA和开发人员的重要工作之一。

规范的重要性

提高代码的可读性和可维护性
减少因书写不规范导致的错误
避免常见的性能问题
便于团队协作和代码 review
确保SQL代码的一致性和专业性

规范的制定原则

简洁明了，易于理解和遵循
兼顾可读性和性能
与团队的开发习惯保持一致
考虑SQL Server的特性和最佳实践
定期更新和完善

基本书写规范

1. 命名规范

表名：使用名词或名词短语，采用PascalCase命名法，如Orders、OrderDetails
列名：使用名词或名词短语，采用PascalCase命名法，如OrderID、CustomerID
索引名：使用IX_表名_列名格式，如IX_Orders_CustomerID
存储过程名：使用usp_功能描述格式，如usp_GetOrdersByCustomer
函数名：使用fn_功能描述格式，如fn_CalculateTotalAmount
视图名：使用vw_视图描述格式，如vw_DailySales
变量名：使用@驼峰命名法，如@CustomerID、@OrderDate

2. 格式规范

缩进：使用4个空格进行缩进，避免使用制表符
换行：每个关键字、子句单独一行，如SELECT、FROM、WHERE等
大小写：关键字使用大写，表名、列名、变量名使用PascalCase或驼峰命名法
空格：操作符前后、逗号后使用空格，如SELECT Column1, Column2 FROM Table WHERE ID = 1
括号：括号内的内容与括号之间使用空格，如SELECT * FROM Table WHERE (Column1 = 1 AND Column2 = 2)

示例：

sql

-- 推荐的书写格式
SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount
FROM Orders
WHERE 
    CustomerID = @CustomerID
    AND OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY OrderDate DESC;

-- 不推荐的书写格式
select orderid,customerid,orderdate,totalamount from orders where customerid=@CustomerID and orderdate between @StartDate and @EndDate order by orderdate desc;

3. 注释规范

单行注释：使用--进行单行注释，注释内容与--之间使用空格
多行注释：使用/* */进行多行注释，适合较长的注释内容
注释内容：清晰、简洁，说明SQL的功能、目的和特殊处理
重要SQL：为复杂的SQL语句添加注释，说明其逻辑和优化思路

示例：

sql

-- 单行注释：获取指定客户的订单信息
SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount
FROM Orders
WHERE CustomerID = @CustomerID;

/*
多行注释：
获取指定日期范围内的销售统计信息
包括订单数量、总销售额和平均订单金额
*/
SELECT 
    COUNT(*) AS OrderCount,
    SUM(TotalAmount) AS TotalSales,
    AVG(TotalAmount) AS AvgOrderAmount
FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate;

SQL性能优化规范

1. 避免使用SELECT *

只选择需要的列，避免返回不必要的数据
减少网络传输量和IO操作
提高查询性能和缓存命中率
避免因表结构变更导致的应用程序错误

示例：

sql

-- 推荐：只选择需要的列
SELECT OrderID, CustomerID, OrderDate FROM Orders;

-- 不推荐：使用SELECT *
SELECT * FROM Orders;

2. 合理使用WHERE子句

尽量将过滤条件放在WHERE子句中，减少结果集大小
避免在WHERE子句中使用函数，会导致索引失效
避免使用!=、<>、NOT IN等操作符，会导致索引失效
对于NULL值比较，使用IS NULL或IS NOT NULL，避免使用= NULL或!= NULL

示例：

sql

-- 推荐：直接比较列值
SELECT * FROM Orders WHERE OrderDate > '2023-01-01';

-- 不推荐：在WHERE子句中使用函数
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023;

-- 推荐：使用IN代替OR
SELECT * FROM Orders WHERE CustomerID IN (1, 2, 3);

-- 不推荐：使用多个OR条件
SELECT * FROM Orders WHERE CustomerID = 1 OR CustomerID = 2 OR CustomerID = 3;

3. 优化JOIN操作

优先使用INNER JOIN，避免使用笛卡尔积
为连接列创建索引，提高JOIN性能
小表驱动大表，将小结果集的表放在前面
避免在JOIN条件中使用函数，会导致索引失效

示例：

sql

-- 推荐：小表驱动大表
SELECT * 
FROM SmallTable s 
JOIN LargeTable l ON s.ID = l.SmallTableID;

-- 推荐：为连接列创建索引
CREATE INDEX IX_OrderDetails_OrderID ON OrderDetails(OrderID);
SELECT * 
FROM Orders o 
JOIN OrderDetails od ON o.OrderID = od.OrderID;

-- 不推荐：缺少连接条件，导致笛卡尔积
SELECT * FROM TableA, TableB;

4. 优化ORDER BY和GROUP BY

为ORDER BY和GROUP BY子句中的列创建索引
避免在ORDER BY和GROUP BY子句中使用函数
尽量减少排序和分组的数据量
考虑使用索引视图或列存储索引优化聚合查询

示例：

sql

-- 推荐：为ORDER BY列创建索引
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);
SELECT * FROM Orders ORDER BY OrderDate DESC;

-- 推荐：为GROUP BY列创建索引
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID);
SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID;

-- 不推荐：在ORDER BY中使用函数
SELECT * FROM Orders ORDER BY YEAR(OrderDate);

5. 避免使用游标

尽量使用集合操作代替游标，集合操作的性能更高
如果必须使用游标，选择合适的游标类型，如FAST_FORWARD或STATIC
及时关闭和释放游标，避免资源泄漏
考虑使用WHILE循环或其他替代方案

示例：

sql

-- 推荐：使用集合操作
UPDATE Orders SET Status = 'Processed' WHERE OrderDate < '2023-01-01';

-- 不推荐：使用游标
DECLARE @OrderID INT;
DECLARE OrderCursor CURSOR FOR SELECT OrderID FROM Orders WHERE OrderDate < '2023-01-01';
OPEN OrderCursor;
FETCH NEXT FROM OrderCursor INTO @OrderID;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Orders SET Status = 'Processed' WHERE OrderID = @OrderID;
    FETCH NEXT FROM OrderCursor INTO @OrderID;
END;
CLOSE OrderCursor;
DEALLOCATE OrderCursor;

6. 合理使用子查询

尽量使用JOIN代替相关子查询，相关子查询的性能较低
考虑使用Common Table Expressions (CTE)代替复杂子查询，提高可读性
避免在WHERE子句中使用IN (SELECT ...)，考虑使用EXISTS代替

示例：

sql

-- 推荐：使用JOIN代替相关子查询
SELECT o.* 
FROM Orders o 
JOIN (SELECT CustomerID FROM Customers WHERE Country = 'China') c ON o.CustomerID = c.CustomerID;

-- 推荐：使用CTE提高可读性
WITH ActiveCustomers AS (
    SELECT CustomerID FROM Customers WHERE Status = 'Active'
)
SELECT o.* FROM Orders o JOIN ActiveCustomers c ON o.CustomerID = c.CustomerID;

-- 推荐：使用EXISTS代替IN
SELECT * FROM Orders o 
WHERE EXISTS (SELECT 1 FROM Customers c WHERE c.CustomerID = o.CustomerID AND c.Country = 'China');

-- 不推荐：使用相关子查询
SELECT * FROM Orders o 
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'China');

7. 优化INSERT、UPDATE、DELETE操作

使用批量插入操作，如BULK INSERT、INSERT INTO ... SELECT等
避免在循环中执行单条INSERT、UPDATE、DELETE操作
考虑使用MERGE语句代替单独的INSERT、UPDATE、DELETE操作
对于大型表的更新，考虑分批处理，避免长时间锁定表

示例：

sql

-- 推荐：批量插入
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount) 
SELECT CustomerID, GETDATE(), 0 FROM Customers WHERE Status = 'Active';

-- 推荐：使用MERGE语句
MERGE INTO TargetTable AS t
USING SourceTable AS s ON t.ID = s.ID
WHEN MATCHED THEN UPDATE SET t.Column1 = s.Column1
WHEN NOT MATCHED THEN INSERT (ID, Column1) VALUES (s.ID, s.Column1);

-- 推荐：分批更新大型表
DECLARE @BatchSize INT = 1000;
DECLARE @RowCount INT = 1;

WHILE @RowCount > 0
BEGIN
    UPDATE TOP (@BatchSize) LargeTable 
    SET Status = 'Processed' 
    WHERE Status = 'Pending';
    
    SET @RowCount = @@ROWCOUNT;
END;

高级书写规范

1. 使用参数化查询

避免使用动态SQL，防止SQL注入攻击
提高查询计划的重用率，减少编译开销
使用sp_executesql执行动态SQL，传递参数
避免在循环中拼接SQL字符串

示例：

sql

-- 推荐：使用参数化查询
DECLARE @CustomerID INT = 123;
DECLARE @StartDate DATETIME = '2023-01-01';

SELECT * FROM Orders 
WHERE CustomerID = @CustomerID 
AND OrderDate >= @StartDate;

-- 推荐：使用sp_executesql执行动态SQL
DECLARE @SQL NVARCHAR(MAX);
DECLARE @CustomerID INT = 123;

SET @SQL = N'SELECT * FROM Orders WHERE CustomerID = @CustomerID';

EXEC sp_executesql @SQL, 
    N'@CustomerID INT', 
    @CustomerID = @CustomerID;

-- 不推荐：拼接SQL字符串，存在SQL注入风险
DECLARE @CustomerID INT = 123;
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Orders WHERE CustomerID = ' + CAST(@CustomerID AS NVARCHAR(10));
EXEC(@SQL);

2. 合理使用事务

缩小事务范围，只包含必要的操作
避免在事务中进行网络调用或等待用户输入
及时提交或回滚事务，减少锁持有时间
考虑使用READ COMMITTED SNAPSHOT隔离级别，减少锁竞争

示例：

sql

-- 推荐：缩小事务范围
BEGIN TRANSACTION;

-- 只包含必要的操作
UPDATE Orders SET Status = 'Paid' WHERE OrderID = @OrderID;
INSERT INTO PaymentHistory (OrderID, PaymentDate, Amount) VALUES (@OrderID, GETDATE(), @Amount);

COMMIT TRANSACTION;

-- 不推荐：事务范围过大
BEGIN TRANSACTION;

-- 必要的操作
UPDATE Orders SET Status = 'Paid' WHERE OrderID = @OrderID;
INSERT INTO PaymentHistory (OrderID, PaymentDate, Amount) VALUES (@OrderID, GETDATE(), @Amount);

-- 不必要的操作，不应包含在事务中
EXEC usp_SendEmail @CustomerID, 'Order Paid';

COMMIT TRANSACTION;

3. 使用CTE和临时表

对于复杂查询，使用CTE提高可读性和可维护性
对于需要多次使用的结果集，考虑使用临时表或表变量
临时表适合大数据量，表变量适合小数据量
及时清理临时对象，避免资源泄漏

示例：

sql

-- 使用CTE简化复杂查询
WITH OrderSummary AS (
    SELECT 
        CustomerID,
        COUNT(*) AS OrderCount,
        SUM(TotalAmount) AS TotalSales
    FROM Orders
    GROUP BY CustomerID
),
CustomerRank AS (
    SELECT 
        CustomerID,
        OrderCount,
        TotalSales,
        ROW_NUMBER() OVER (ORDER BY TotalSales DESC) AS Rank
    FROM OrderSummary
)
SELECT * FROM CustomerRank WHERE Rank <= 10;

-- 使用临时表存储中间结果
CREATE TABLE #TempOrders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(18, 2)
);

INSERT INTO #TempOrders 
SELECT OrderID, CustomerID, OrderDate, TotalAmount 
FROM Orders WHERE OrderDate >= '2023-01-01';

-- 多次使用临时表
SELECT COUNT(*) FROM #TempOrders;
SELECT AVG(TotalAmount) FROM #TempOrders;

-- 清理临时表
DROP TABLE #TempOrders;

实际生产场景应用

场景1：电商网站的订单查询优化

场景描述：电商网站的订单查询页面，需要根据多个条件过滤订单，包括客户ID、订单日期、订单状态等，查询性能较差。

解决方案：

遵循SQL书写规范，只选择需要的列
为过滤条件创建合适的索引
使用参数化查询，提高查询计划重用率
优化JOIN操作，为连接列创建索引
考虑使用分页查询，减少返回的数据量

示例：

sql

-- 优化后的订单查询
SELECT 
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.TotalAmount,
    o.Status,
    c.CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE 
    o.CustomerID = @CustomerID
    AND o.OrderDate BETWEEN @StartDate AND @EndDate
    AND o.Status = @Status
ORDER BY o.OrderDate DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

-- 为查询创建合适的索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate_Include ON Orders(
    CustomerID,
    OrderDate,
    Status
) INCLUDE (
    TotalAmount
);

场景2：数据仓库的ETL过程优化

场景描述：数据仓库的ETL过程，需要处理大量数据，包括数据提取、转换和加载，性能较差。

解决方案：

使用批量操作，如BULK INSERT、INSERT INTO ... SELECT等
避免使用游标和循环，使用集合操作
优化JOIN和聚合操作，使用列存储索引
考虑使用分区表，提高查询和维护性能
分批处理大型表，避免长时间锁定

示例：

sql

-- ETL过程优化示例
-- 1. 提取数据到临时表
SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount,
    Status
INTO #StagingOrders
FROM SourceSystem.Orders
WHERE LastModifiedDate >= @LastETLDate;

-- 2. 转换数据
UPDATE #StagingOrders 
SET Status = CASE 
    WHEN Status = 'Open' THEN 1
    WHEN Status = 'Paid' THEN 2
    WHEN Status = 'Shipped' THEN 3
    WHEN Status = 'Completed' THEN 4
    ELSE 0
END;

-- 3. 加载数据到目标表
MERGE INTO DataWarehouse.Orders AS t
USING #StagingOrders AS s ON t.OrderID = s.OrderID
WHEN MATCHED THEN UPDATE SET 
    t.CustomerID = s.CustomerID,
    t.OrderDate = s.OrderDate,
    t.TotalAmount = s.TotalAmount,
    t.Status = s.Status
WHEN NOT MATCHED THEN INSERT (
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount,
    Status
) VALUES (
    s.OrderID,
    s.CustomerID,
    s.OrderDate,
    s.TotalAmount,
    s.Status
);

-- 4. 清理临时表
DROP TABLE #StagingOrders;

版本差异

SQL Server 2012及以前

缺少一些高级功能，如OFFSET FETCH、MERGE语句等
不支持行级安全性和动态数据掩码
缺少Columnstore索引
临时表和表变量的性能差异较大

SQL Server 2014-2016

引入Columnstore索引，支持更新操作
支持OFFSET FETCH分页
引入内存优化表和本地编译存储过程
增强了MERGE语句的功能

SQL Server 2017及以后

引入自适应查询处理，优化执行计划
增强了CTE功能，支持递归CTE的优化
引入自动计划修正，避免执行计划问题
增强了JSON和XML处理功能

SQL Server 2022

引入智能查询处理，进一步优化执行计划
增强了Columnstore索引的功能
引入索引建议功能，自动推荐缺失的索引
改进了MERGE语句的性能

常见问题（FAQ）

1. 为什么要避免使用SELECT *？

答案：

SELECT *会返回表中的所有列，包括不需要的列，增加网络传输量和IO操作
SELECT *会导致查询无法使用覆盖索引，必须回表获取数据
SELECT *会因表结构变更导致应用程序错误，如添加或删除列
SELECT *会降低查询的可读性和可维护性

2. 如何优化LIKE查询？

答案：

避免在LIKE查询的开头使用通配符，如LIKE '%abc'，会导致索引失效
尽量在LIKE查询的结尾使用通配符，如LIKE 'abc%'，可以使用索引
对于复杂的文本搜索，考虑使用全文索引
避免在大表上使用LIKE查询，考虑使用搜索引擎

3. 什么时候应该使用临时表，什么时候应该使用表变量？

答案：

临时表：适合大数据量（>10,000行），支持索引，支持统计信息
表变量：适合小数据量（<10,000行），不支持统计信息，性能相对较低
临时表的作用域是当前会话，表变量的作用域是当前批处理
临时表会写入tempdb，表变量主要存储在内存中

4. 如何优化ORDER BY操作？

答案：

为ORDER BY子句中的列创建索引
避免在ORDER BY中使用函数
尽量减少排序的数据量
考虑使用索引视图或列存储索引优化排序操作
对于大型结果集的排序，考虑使用tempdb优化

5. 如何避免SQL注入攻击？

答案：

使用参数化查询，避免拼接SQL字符串
验证用户输入，过滤特殊字符
限制数据库用户的权限，遵循最小权限原则
使用存储过程，减少直接执行动态SQL
启用SQL Server的防注入功能

6. 如何优化JOIN操作的性能？

答案：

为连接列创建合适的索引
小表驱动大表，将小结果集的表放在前面
避免笛卡尔积，确保连接条件完整
考虑使用HASH JOIN或MERGE JOIN，避免Nested Loops JOIN
减少JOIN的表数量，优化查询逻辑

7. 什么时候应该使用存储过程？

答案：

当SQL代码需要被多次调用时
当需要封装复杂的业务逻辑时
当需要提高查询性能，减少网络传输时
当需要增强安全性，限制直接访问表时
当需要实现事务管理时

8. 如何优化批量插入操作？

答案：

使用BULK INSERT或bcp工具，提高插入性能
关闭自动提交，使用显式事务
禁用触发器和约束，插入后再启用
考虑使用分区表，将数据分布到多个文件组
调整批量大小，找到最佳的插入性能

最佳实践

遵循命名规范：使用统一的命名规范，提高代码的可读性和可维护性
只选择需要的列：避免使用SELECT *，只选择查询需要的列
使用参数化查询：提高查询计划重用率，防止SQL注入攻击
优化WHERE子句：将过滤条件放在WHERE子句中，避免在WHERE子句中使用函数
为过滤和连接列创建索引：提高查询性能，减少IO操作
避免使用游标：尽量使用集合操作代替游标，提高性能
缩小事务范围：只包含必要的操作，减少锁持有时间
使用CTE和临时表：简化复杂查询，提高可读性和可维护性
定期审查和优化SQL代码：定期检查和优化慢查询，提高系统性能
培训开发人员：培训开发人员遵循SQL书写规范和最佳实践

通过遵循SQL书写规范和最佳实践，DBA和开发人员可以编写高效、可读、可维护的SQL代码，提高数据库系统的性能和可靠性。SQL书写规范是数据库运维的重要组成部分，需要持续关注和完善。

SQLServer SQL书写规范 ​

SQL书写规范基础 ​

规范的重要性 ​

规范的制定原则 ​

基本书写规范 ​

1. 命名规范 ​

2. 格式规范 ​

3. 注释规范 ​

SQL性能优化规范 ​

1. 避免使用SELECT * ​

2. 合理使用WHERE子句 ​

3. 优化JOIN操作 ​

4. 优化ORDER BY和GROUP BY ​

5. 避免使用游标 ​

6. 合理使用子查询 ​

7. 优化INSERT、UPDATE、DELETE操作 ​

高级书写规范 ​

1. 使用参数化查询 ​

2. 合理使用事务 ​

3. 使用CTE和临时表 ​

实际生产场景应用 ​

场景1：电商网站的订单查询优化 ​

场景2：数据仓库的ETL过程优化 ​

版本差异 ​

SQL Server 2012及以前 ​

SQL Server 2014-2016 ​

SQL Server 2017及以后 ​

SQL Server 2022 ​

常见问题（FAQ） ​

1. 为什么要避免使用SELECT *？ ​

2. 如何优化LIKE查询？ ​

3. 什么时候应该使用临时表，什么时候应该使用表变量？ ​

4. 如何优化ORDER BY操作？ ​

5. 如何避免SQL注入攻击？ ​

6. 如何优化JOIN操作的性能？ ​

7. 什么时候应该使用存储过程？ ​

8. 如何优化批量插入操作？ ​

最佳实践 ​