T-SQL脚本规范
[TOC]
必须使用：
- 如无特殊情况，为提高系统并发性能，所有查询必须有WITH(NOLOCK)；
- 涉及数据变更的事务，必须有异常捕获处理机制，并且要设置LOCK_TIMEOUT，避免长时间资源锁定；
建议15毫秒：SET LOCK_TIMEOUT 15
- 变量 / 参数 / 多表关联字段类型，必须与数据表字段类型一致，避免类型转换而造成额外的CPU及扫描；
- 多表连接时，必须使用表的别名来引用列，即：表名.字段名；
请使用表的别名并把别名前缀于每个Column上，以指明字段是属于哪个表，否则未来表结构变更后，有可能因Column歧义而引起语法错误；
- 时效性数据，必须包括<最近更新日期>字段；
- 数据表、数据字段必须加入中文注释；
解读：N年后谁tm知道这个r1,r2,r3字段是干嘛的
禁止使用：
- 禁止在数据库做复杂运算，建议放到程序端处理，因为程序端扩容方便，数据库扩容能力有限；
- 禁止使用select *，只返回需要的列；
a）读取不需要的列会增加CPU、IO、Memory、NET消耗；
b）索引不能覆盖查询所需的列，就会造成键值查找或RID查找；
c）表结构变化时容易引起查询出错； - 禁止使用INSERT INTO tbl VALUES(xxx,yyy,zzz)，必须显示指定字段清单；
解读：容易在增加或者删除字段后出现程序BUG；
- 禁止全表更新/删除，即 UPDATE / DELETE 必须带 WHERE 或 ON；
- 禁止使用drop / truncate（特殊情况请与DBA确认）；
- 禁止使用LinkedServers(效率过低) / 游标(性能不好) / 触发器(对应用不透明)；
解读：使用LinkedServer存在安全风险，也不便于追踪排障，而且网络拉取或推送的效率过低，数据量多了会有性能问题；
游标遍历效率太低；
触发器对应用不透明，效率也不高，增加迁移及维护风险； - 禁止使用存储过程、计划任务；
a）数据库(单台瓶颈)擅长存储与索引，涉及CPU计算请交给应用服务器(易扩展)；
b）调用关系复杂不利于表下线或迁移，也不利于排障；
c）执行存储过程的时候，不便于备份数据； - 禁止使用外键，一切外键概念必须在应用层解决；
- 禁止跨库查询，尽量不做跨表查询；
- 禁止大表使用JOIN查询，禁止大表使用子查询；
解读：大表子查询会产生临时表，消耗较多内存与CPU，极大影响数据库性能，且一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，请在子查询中过滤掉尽可能多的行。
- 禁止JOIN个数超过5个：
a）过多的JOIN会导致查询分析器走错执行计划；
b）过多JOIN在编译执行计划时消耗很大； - 禁止IN子句中条件个数超过100个，单次查询返回过多数据会降低数据库的并发能力；
- 禁止在程序端大批量更新/删除数据，批量操作请与dba协商操作；
- 禁止大批量的查询数据结果，如果需要返回大量数据，请使用TOP或分页的方式；
- 禁止高并发业务(每秒访问超过5次)单次查询超过100条，比如select top(N)其中N必须<100；
- 禁止使用xml、text、image、blob、varchar(max)等大数据类型；
a）存储大字段会浪费更多的磁盘和内存空间；
b）非必要的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能； - 禁止存放大文件或者照片；
解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URL多好；
- 禁止新加的表为NULL；
a）允许NULL值，会增加应用程序的复杂性，必须得增加特定的逻辑代码，以防止出现各种意外的bug；
b）在where子句中对字段进行isnull值判断（本无必要），将导致引擎放弃使用索引而进行全表扫描，可能会引起性能瓶颈；
c）新加的表，所有字段禁止NULL，建议用0、特殊值或空串代替NULL值；
d）旧表新加字段，先允许为NULL+默认值（避免全表更新，长期持锁导致阻塞），再分批更新值；
e）三值（TRUE、FALSE、UNKNOWN）逻辑，所有等号（“=”）的查询都必须增加ISNULL的判断； - 禁止在JOIN ON或WHERE语句中使用IS NULL 或 IS NOT NULL；
- 禁止使用属性隐式转换；
解读：WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引；
- 禁止在索引列上使用函数或计算；
a）解读：WHERE CONVERT(VARCHAR(10),InsertTime,121) > '2017-02-15' 会导致全表扫描；
b）假设在字段col1上建有一个索引，下列场景将可以使用到索引：
col1 = 100
col1 > 100
col1 BETWEEN 0 AND 99
col1 LIKE 'abc%'
col1 IN (4,5,6) - 禁止非SARG查询，以及%开头的模糊查询；
a）非SARG查询：NOT、<>、NOT EXISTS、NOT IN、NOT LIKE和内部标量函数，会导致扫描；
b）LIKE子句应尽量前端匹配，避免通配符在前端，因为%开头的模糊查询，会导致索引扫描；
name like 'abc%' --index seek
name like '%abc%' --index scan
name like '%abc' --index scan - 禁止把table或sp创建在master、tempdb等系统数据库下；
- 禁止在代码中使用HINT索引提示with(index=xxx)或with(forceseek)；
a）随着数据的变化查询语句指定的索引性能可能并不最佳；
b）索引对应用应是透明的，如指定的索引被删除将会导致查询报错，不利于排障；
c）新建的索引无法被应用立即使用，必须通过发布代码才能生效； - 禁止使用ip直连数据库，建议使用内网域名；
解读：不只是数据库，缓存（memcache、redis）的连接，服务（service）的连接都必须使用内网域名，机器迁移/平滑升级/运维管理…太多太多的好处。
建议使用：
- 建议避免使用IN或OR运算符；
对于IN或OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集。
- 建议NOT EXISTS替代NOT IN；
- 建议UNION ALL替代UNION；
因为UNION会对SQL结果集去重排序，增加CPU、内存等消耗；
- 建议关闭(默认开启)影响的行计数信息返回；
在SQL语句中显示设置set nocount on，可取消影响的行计数信息返回，减少网络流量
- 建议定义游标时不要使用order by；
- 建议只读游标定义时不要指定FAST_FORWARD子句；
- 建议将长而复杂的SQL拆分成小段SQL运算；
- 建议不要使用holdlock子句；
- 建议使用count(1) 来代替count(*)；
- 建议不要使用GUID做聚集索引，基于碎片考虑；