SQL 语法书写准则

1、准则目的

使公司内部的 SQL 语法统一规范；
便于项目团队相关人员的查看、修改；
提高存储过程执行效率，避免 SQL 语法错误导致服务器效率低而产生系统问题；

2、书写注释规范

2.1 签名、日期、用途
说明：每个存储过程必须要标明存储过程的编写者、编写日期、用途。
示例：
-- =============================================
-- Author: <Team >
-- Create date: <2022.8.13>
-- Description: <MES批号装配事务的装配参照清单获取>
-- Rev: 10.00 同时汇总各需求项的是否完成装配的状态
-- =============================================

2.2 局部变量的注释说明
说明：创建局部变量时，请标明变量的用途。
示例：
declare @IsMOItem char(1) --装配参照清单类型，1-参照工单进行装配，0-参照BOM进行装配

2.3 表变量、临时表的字段说明
说明：创建表变量或临时表时，请标明表、字段的用途。

2.4 段落缩进注释
说明：注释每一段代码的作用，便于查阅理解代码含义。
 

Declare @IsStartLot bit
DECLARE table_cur CURSOR FOR
SELECT LineSequence,IsStartLot from @AssyBOMItem
--用游标循环
OPEN table_cur
FETCH NEXT FROM table_cur INTO @LineSequence,@IsStartLot
WHILE @@fetch_status = 0
BEGIN
IF @@fetch_status = -2
CONTINUE
if @IsStartLot=1
begin --保留制自件，但删下层物料
update @AssyBOMItem set IsDelete=1
where LineSequence like @LineSequence+'%'
end
else
begin --删自制件,但保留下层物料
if exists(select LineSequence from @AssyBOMItem where LineSequence
like @LineSequence+'%')
update @AssyBOMItem set IsDelete=1
where LineSequence=@LineSequence
end
FETCH NEXT FROM table_cur INTO @LineSequence,@IsStartLot
END
DEALLOCATE table_cur

3、SQL 影响执行效率的注意点

3.1 Where 语句书写及效率影响
示例【1】：
在下面两条select语句中:
select * from table1 where field1<=10000 and field1>=0;
select * from table1 where field1>=0 and field1<=10000;
说明【1】：
如果数据表中的数据 field1 都>=0,则第一条 select 语句要比第二条 select
语句效率高的多，因为第二条 select 语句的第一个条件耗费了大量的系统资源。
原则：在 where 子句中应把最具限制性的条件放在最前面。
示例【2】：
在下面两条select语句中:
select * from tab where a=...and b=...and c=...;
说明【2】：
若有索引 index(a,b,c)，则 where 子句中字段的顺序应和索引中字段顺序一致。
原则：where 子句中字段的顺序应和索引中字段顺序一致。
示例【3】：
以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。
select field3,field4 from tb where field1='sdf' 快
select * from tb where field1='sdf' 慢
说明【3】：
因为后者在索引扫描后要多一步 ROWID 表访问
示例【4】：
在下面两条select语句中:
select field3,field4 from tb where field1>='sdf' 快
select field3,field4 from tb where field1>'sdf' 慢
说明【4】：
因为前者可以迅速定位索引。
示例【5】：
在下面select语句:
select field3,field4 from tb where upper(field2)='RMN'不使用索引。
说明【5】：
如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，
严格禁止使用函数！两万条记录以下没有限制。例如：Subtring、Like、Datepart
函数会降低搜索效率。
示例【6】：
select field3,field4 from tb where field2!='TOM'不使用索引。
说明【6】：
条件中尽量不用!=, Not in, Like, OR, NOT EXISTS 的条件语句。
示例【7】：
select field3,field4 from tb where field2 is not null 不使用索引。
说明【7】：
空值不在索引中存储，所以上面语句执行不会使用索引检索。

3.2 避免多表关联

1. 对单条记录检索时，请对 SQL 语句进行拆分，减少表关联。
示例：
改前
SELECT Lot.LotSN
FROM MO INNER JOIN
Lot ON MO.MOId = Lot.MOId
WHERE (MO.MOName = 'S20100408001-900G')
改后
declare @MOId char(12)
--先获取MOid
SELECT @MOId = MOId
FROM MO
WHERE (MOName = 'S20100408001-900G')
--单独查询Lot表，避免表关联
SELECT Lot.LotSN
FROM Lot
WHERE (Lot.MOId = @MOId)
2. 利用表变量来替换大表关联,表变量的作用域为一个批处理,批处理完了,表变量也会
随之失效,比起临时表有它独特的优点：不用手动去删除表变量以释放内存。
示例：
declare @t table
(cardNo int)
insert @t
select cardNo from member where in('a-01',代理号二)
select 字段 from order
inner join @t on
order.cardNo=@t.cardNo
3. 利用索引视图来提高大表关联的性能.

3.3 临时表和表变量的灵活使用

表变量的好处：
1. 避免重编译：表标量可以避免临时表引起的重编译。
2. 没有事务日志开销：表变量不执行事务日志活动，临时表执行该活动。
3. 没有锁开销：表变量被看作局部变量，不错在临时表的锁开销。
4. 没有回滚开销：没有事务日志就不存在回滚事务。
临时表的好处：
1. 临时表空间一般利用虚拟内存，大大减少了硬盘的 I/O 次数，因此也提高了系统效率。
2. 事务完毕或会话完毕数据自动清空，不必记得用完后删除数据。
3. 数据当前会话期可见，其它的会话只能看到其结构，只能看到自己的数据，各会话的
数据互不干扰。

3.5 防范死锁问题

1.一定要在 Delete 前加上 If Existed(select XID from youtable where 删除的条件), 判
断之后再进行删除。以减少表锁的机会
示例：
If Exists(select LotId from Lot where LotSN = 'FG00000012')
begin
Delete Lot where LotSN = 'FG00000012'
end
2. 地方一定要在自已写的存储过程前加上
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED –允许脏读
3. 客户化程序在 ADO.NET 编写时，数据库操作完成后，一定需要释放 connection 连
接对象，不要用全局的 connection 对象不释放。

3.6 游标的谨慎使用

(1) 尽管使用游标比较灵活，可以实现对数据集中单行数据的直接操作，但游标会在下
面几个方面影响系统的性能：
使用游标会导致页锁与表锁的增加
导致网络通信量的增加
增加了服务器处理相应指令的额外开销
(2) 使用游标时的优化问题：
明确指出游标的用途：for read only 或 for update，在 for update 后指定被修改
的列。
示例：
定义一个标准游标：
declare mycursor cursor for select * from yuangong
定义一个只读游标：
declare mycursor cursor for select * from yuangong for read only
定义一个可写游标
declare mycursor cursor for select * from yuangong for udpate of 列名