SQLServer使⽤规范（转载）
SQLServer使⽤规范
常见的字段类型选择
1.字符类型建议采⽤varchar/nvarchar数据类型
2.⾦额货币建议采⽤money数据类型
3.科学计数建议采⽤numeric数据类型
4.⾃增长标识建议采⽤bigint数据类型  (数据量⼀⼤，⽤int类型就装不下，那以后改造就⿇烦了)
5.时间类型建议采⽤为datetime数据类型
6.禁⽌使⽤text、ntext、image⽼的数据类型
7.禁⽌使⽤xml数据类型、varchar(max)、nvarchar(max)
约束与索引
每张表必须有主键
    •每张表必须有主键，⽤于强制实体完整性
    •单表只能有⼀个主键（不允许为空及重复数据）
    •尽量使⽤单字段主键
不允许使⽤外键
    •外键增加了表结构变更及数据迁移的复杂性
    •外键对插⼊，更新的性能有影响，需要检查主外键约束
    •数据完整性由程序控制
NULL属性
    •新加的表，所有字段禁⽌NULL
（新表为什么不允许NULL？
允许NULL值，会增加应⽤程序的复杂性。你必须得增加特定的逻辑代码，以防⽌出现各种意外的bug
三值逻辑，所有等号（“=”）的查询都必须增加isnull的判断。
Null=Null、Null!=Null、not(Null=Null)、not(Null!=Null)都为unknown，不为true）
举例来说明⼀下：
如果表⾥⾯的数据如图所⽰：
你想来查除了name等于aa的所有数据，然后你就不经意间⽤了
SELECT * FROM NULLTEST WHERE NAME<>’aa’
结果发现与预期不⼀样，事实上它只查出了name=bb⽽没有查出name=NULL的数据记录
那我们如何查除了name等于aa的所有数据，只能⽤ISNULL函数了
SELECT * FROM NULLTEST WHERE ISNULL(NAME,1)<>’aa’
但是⼤家可能不知道ISNULL会引起很严重的性能瓶颈，所以很多时候最好是在应⽤层⾯限制⽤户的输⼊，确保⽤户输⼊有效的数据再进⾏查询。
    •旧表新加字段，需要允许为NULL（避免全表数据更新，长期持锁导致阻塞）（这个主要是考虑之前表的改造问题）
索引设计准则
•应该对 WHERE ⼦句中经常使⽤的列创建索引
•应该对经常⽤于连接表的列创建索引
•应该对 ORDER BY ⼦句中经常使⽤的列创建索引
•不应该对⼩型的表（仅使⽤⼏个页的表）创建索引，这是因为完全表扫描操作可能⽐使⽤索引执⾏的查询快
•单表索引数不超过6个
•不要给选择性低的字段建单列索引
•充分利⽤唯⼀约束
•索引包含的字段不超过5个（包括include列）
不要给选择性低的字段创建单列索引
•SQL SERVER对索引字段的选择性有要求，如果选择性太低SQL SERVER会放弃使⽤•
•不适合创建索引的字段：性别、0/1、TRUE/FALSE
•适合创建索引的字段：ORDERID、UID等
充分利⽤唯⼀索引
唯⼀索引给SQL Server提供了确保某⼀列绝对没有重复值的信息，当查询分析器通过唯⼀索引查到⼀条记录则会⽴刻退出，不会继续查索引
表索引数不超过6个
表索引数不超过6个（这个规则只是携程DBA经过试验之后制定的。。。）
•索引加快了查询速度，但是却会影响写⼊性能
•⼀个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并
•组合索引的原则是，过滤性越好的字段越靠前
•索引过多不仅会增加编译时间，也会影响数据库选择最佳执⾏计划
SQL查询
•禁⽌在数据库做复杂运算
•禁⽌使⽤SELECT *
•禁⽌在索引列上使⽤函数或计算
•禁⽌使⽤游标
•禁⽌使⽤触发器
•禁⽌在查询⾥指定索引
•变量/参数/关联字段类型必须与字段类型⼀致
•参数化查询
•限制JOIN个数
•限制SQL语句长度及IN⼦句个数
•尽量避免⼤事务操作
•关闭影响的⾏计数信息返回
•除⾮必要SELECT语句都必须加上NOLOCK
•使⽤UNION ALL替换UNION
•查询⼤量数据使⽤分页或TOP
•递归查询层级限制
•NOT EXISTS替代NOT IN
•临时表与表变量
•使⽤本地变量选择中庸执⾏计划
•尽量避免使⽤OR运算符
•增加事务异常处理机制
•输出列使⽤⼆段式命名格式
禁⽌在数据库做复杂运算
•XML解析
•字符串相似性⽐较
•字符串搜索（Charindex）
•复杂运算在程序端完成
禁⽌使⽤SELECT *
•减少内存消耗和⽹络带宽
•给查询优化器有机会从索引读取所需要的列
•表结构变化时容易引起查询出错
禁⽌在索引列上使⽤函数或计算
禁⽌在索引列上使⽤函数或计算
在where⼦句中，如果索引是函数的⼀部分，优化器将不再使⽤索引⽽使⽤全表扫描
假设在字段Col1上建有⼀个索引，则下列场景将⽆法使⽤到索引：
ABS[Col1]=1
[Col1]+1>9
再举例说明⼀下
像上⾯这样的查询，将⽆法⽤到O_OrderProcess表上的PrintTime索引，所以我们应⽤使⽤如下所⽰的查询SQL
禁⽌在索引列上使⽤函数或计算
假设在字段Col1上建有⼀个索引，则下列场景将可以使⽤到索引：
[Col1]=3.14
[Col1]>100
sql触发器的使用[Col1] BETWEEN 0 AND 99
[Col1] LIKE ‘abc%’
[Col1] IN(2,3,5,7)
LIKE查询的索引问题
1.[Col1] like "abc%"  --index seek  这个就⽤到了索引查询
2.[Col1] like "%abc%"  --index scan  ⽽这个就并未⽤到索引查询
3.[Col1] like "%abc"  --index scan 这个也并未⽤到索引查询
我想从上⽽三个例⼦中，⼤家应该明⽩，最好不要在LIKE条件前⾯⽤模糊匹配，否则就⽤不到索引查询。
禁⽌使⽤游标
•关系数据库适合集合操作，也就是对由WHERE⼦句和选择列确定的结果集作集合操作，游标是提供的⼀个⾮集合操作的途径。⼀般情况下，游标实现的功能往往相当于客户端的⼀个循环实现的功能。
•游标是把结果集放在服务器内存，并通过循环⼀条⼀条处理记录，对数据库资源（特别是内存和锁资源）的消耗是⾮常⼤的。
（再加上游标真⼼⽐较复杂，挺不好⽤的，尽量少⽤吧）
禁⽌使⽤触发器
触发器对应⽤不透明（应⽤层⾯都不知道会什么时候触发触发器，发⽣也也不知道，感觉莫名......）
禁⽌在查询⾥指定索引
With(index=XXX)（在查询⾥我们指定索引⼀般都⽤With(index=XXX)  ）
•随着数据的变化查询语句指定的索引性能可能并不最佳
•索引对应⽤应是透明的，如指定的索引被删除将会导致查询报错，不利于排障
•新建的索引⽆法被应⽤⽴即使⽤，必须通过发布代码才能⽣效
变量/参数/关联字段类型必须与字段类型⼀致（这是我之前不太关注的）
避免类型转换额外消耗的CPU，引起的⼤表scan尤为严重
看了上⾯这两个图，我想我不⽤解释说明，⼤家都应该已经清楚了吧。
如果数据库字段类型为VARCHAR，在应⽤⾥⾯最好类型指定为AnsiString并明确指定其长度
如果数据库字段类型为CHAR，在应⽤⾥⾯最好类型指定为AnsiStringFixedLength并明确指定其长度
如果数据库字段类型为NVARCHAR，在应⽤⾥⾯最好类型指定为String并明确指定其长度
参数化查询
以下⽅式可以对查询SQL进⾏参数化：
•sp_executesql
•Prepared Queries
•Stored procedures
⽤图来说明⼀下，哈哈。
限制JOIN个数
•单个SQL语句的表JOIN个数不能超过5个
•过多的JOIN个数会导致查询分析器⾛错执⾏计划
•过多JOIN在编译执⾏计划时消耗很⼤
限制IN⼦句中条件个数
•在 IN ⼦句中包括数量⾮常多的值（数以千计）可能会消耗资源并返回错误 8623 或 8632，要求IN⼦句中条件个数限制在100个以内
尽量避免⼤事务操作
•只在数据需要更新时开始事务，减少资源锁持有时间
•增加事务异常捕获预处理机制
•禁⽌使⽤数据库上的分布式事务
⽤图来说明⼀下
也就是说我们不应该在1000⾏数据都更新完成之后再commit tran，你想想你在更新这⼀千⾏数据的时候是不是独占资源导致其它事务⽆法处理。
关闭影响的⾏计数信息返回
在SQL语句中显⽰设置Set Nocount On，取消影响的⾏计数信息返回，减少⽹络流量
除⾮必要SELECT语句都必须加上NOLOCK
除⾮必要，尽量让所有的select语句都必须加上NOLOCK
指定允许脏读。不发布共享锁来阻⽌其他事务修改当前事务读取的数据，其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产⽣较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向⽤户显⽰从未提交过的数据，或者导致⽤户两次看到记录（或根本看不到记录）
使⽤UNION ALL替换UNION
使⽤UNION ALL替换UNION
UNION会对SQL结果集去重排序，增加CPU、内存等消耗
查询⼤量数据使⽤分页或TOP
合理限制记录返回数，避免IO、⽹络带宽出现瓶颈
递归查询层次限制
使⽤ MAXRECURSION 来防⽌不合理的递归 CTE 进⼊⽆限循环
临时表与表变量
使⽤本地变量选择中庸执⾏计划
在存储过程或查询中，访问了⼀张数据分布很不平均的表格，这样往往会让存储过程或查询使⽤了次优甚⾄于较差的执⾏计划上，造成High CPU及⼤量IO Read等问题，使⽤本地变量防⽌⾛错执⾏计划。
采⽤本地变量的⽅式，SQL在编译的时候是不知道这个本地变量的值，这时候SQL会根据表格⾥数据的⼀般分布，“猜测”⼀个返回值。不管⽤户在调⽤存储过程或语句的时候代⼊的变量值是多少，⽣成的计划都是⼀样的。这样的计划⼀般会⽐较中庸⼀些，不⼀定是最优的计划，但⼀般也不会是最差的计划
l如果查询中本地变量使⽤了不等式运算符，查询分析器使⽤了⼀个简单的 30% 的算式来预估
Estimated Rows =(Total Rows * 30)/100
l如果查询中本地变量使⽤了等式运算符，则查询分析器使⽤：精确度 * 表记录总数来预估
Estimated Rows = Density * Total Rows
尽量避免使⽤OR运算符
对于OR运算符，通常会使⽤全表扫描，考虑分解成多个查询⽤UNION/UNION ALL来实现，这⾥要确认查询能⾛到索引并返回较少的结果集
增加事务异常处理机制
应⽤程序做好意外处理，及时做Rollback。
设置连接属性 "set xact_abort on"
输出列使⽤⼆段式命名格式
⼆段式命名格式：表名.字段名
有JOIN关系的TSQL，字段必须指明字段是属于哪个表的，否则未来表结构变更后，有可能发⽣Ambiguous column name的程序兼容错误
架构设计
•读写分离
•schema解耦
•数据⽣命周期
读写分离
•设计之初就考虑读写分离，哪怕读写同⼀个库，有利于快速扩容
•按照读特征把读分为实时读和可延迟读分别对应到写库和读库
•读写分离应该考虑在读不可⽤情况下⾃动切换到写端
Schema解耦
禁⽌跨库JOIN
数据⽣命周期
根据数据的使⽤频繁度，对⼤表定期分库归档
主库/归档库物理分离
⽇志类型的表应分区或分表
对于⼤的表格要进⾏分区，分区操作将表和索引分在多个分区，通过分区切换能够快速实现新旧分区替换，加快数据清理速度，⼤幅减少IO 资源消耗
频繁写⼊的表，需要分区或分表
⾃增长与Latch Lock
闩锁是sql Server⾃⼰内部申请和控制，⽤户没有办法来⼲预，⽤来保证内存⾥⾯数据结构的⼀致性，锁级别是页级锁
（根据培训讲师的PPT整理的，希望对⼤家有⽤，⽂笔不好望见谅）
本⽂由远程⼀键发布