SQLServer数据库开发规范
数据库版本的选择
SQL Server ⽬前常见版本有： 2005、2008、2012、2014、2016、2017……
后⾯谈及 SQL Server 的版本前⾯不再加 “SQL Server” 字样，仅⽤ 4 位数字代替。
根据服务器上的操作系统及公司常⽤的数据库版本，结合项⽬的需要，选择较新的版本为佳。
注：
1. 常规维护（⽐如：2008的新功能压缩备份，只有常规备份的⼏分之⼀，可以节约服务器的空间）
2. DBA维护（⽐如：2005 就没有影响较⼩查慢SQL、死锁的⽅法， 2014 就⽐较⽅便⽽且对系统性能影响较⼩; 2014上的作业⽤Power Shell 脚本可安全地实现更多系统功能）、
3. 审计（2008新加功能，如果数据库数据经常被篡改或怀疑有⿊客侵⼊，就⽤得着了）
sqlserver备份表语句4. ⾼可⽤及读写分离（对于访问量⼤，稳定性要求⾼⽽且有报表需求的项⽬，2014 的 alwayson 是⾮常实⽤的）
5. 其它还有内置的加密（2016开始⽀持, 普通⽤户即使能查也⽆法看到实际数据）、对 Linux 的⽀持等，也是⾮常实⽤的。具体的可以查看 msdn 上相关介绍。
同⼀版本还有开发版、标准版、企业版等区别，⼀律选⽤企业版，不再赘述。
数据库的创建
1        2        3        4        5        6        7        8        9        10--⽂件的增长量设置为 64MB
CREATE
DATABASE
[test]
ON
PRIMARY
(
NAME
= N
'test'
, FILENAME = N
'D:\database\test.mdf'
,
SIZE
= 5MB , FILEGROWTH = 64MB )
LOG
ON
(
NAME
= N
'test_log'
, FILENAME = N
'D:\database\test_log.ldf'
,
SIZE
= 1MB , FILEGROWTH = 64MB )
GO
--恢复模式改为简单，如果希望为完整，不要下⾯这句即可ALTER
DATABASE
[test]
SET
RECOVERY SIMPLE
WITH
NO_WAIT
GO
主要是以下⼏个⽅⾯( 3、4两点可以DBA来完成 )：
1. 增加量要设置为固定值⽽不是百分⽐，避免数据库变⼤后⼀次性增涨过⼤导致空间浪费及性能问题;
2. ⼀般不太重要（不涉及到钱和重要业务）的库建议设置为简单，这样性能⾼⽽且免维护，缺点：如果要回以前更改的数据⽐较⿇烦。如果⽣产环境设置为完整模式，必须请DBA帮忙维护( 主要是做⽇志备份 );
3. 对于要做表分区的，可以增加⽂件组和⽂件，但⼀般情况下只保留⼀个⽂件组和⽂件，因为多⽂件只有在多磁盘下才有性能提升的可能;
4. 对于某些不太重要的⼤表（如⽇志），也可以单独增加⼀个专门的⽂件组和⽂件，⽅便⽇后单独清理并释放空间.
命名规范
⼏种常见的命名⽅法：
Pascal ：
将标识符的⾸字母和后⾯连接的每个单词的⾸字母都⼤写。可以对三字符或更多字符的标识符使⽤ Pascal ⼤⼩写。例如：BackColor
Camel ：
标识符的⾸字母⼩写，⽽每个后⾯连接的单词的⾸字母都⼤写。例如：backColor
⼤写：
标识符中的所有字母都⼤写。仅对于由两个或者更少字母组成的标识符使⽤该约定。例如：
System.IO
System.Web.UI
以下是常见的数据库对象的命名规范：
序
号
对象名命名规范⽰例备注
1数据库项⽬名，全⼩写csp归档库为原数据库+'_年份', 如：'csp_2018'
2表模块名_表名_后缀sys_user_data全⼩写。模块名为可选, 但尽量加上
sys_user_role_mid后缀：
data 为基本数据表
mid 为中间表
3字段Camel 命名法insertTime
4存储过程Proc_模块名_表对应类名
_操作Proc_BaseData_Area_Get Pacal命名法。开发类的存储过程绝对不要⽤ 'sp_xxx' , 因为这样会先查系统存储过程，没有再去查⽤户
存储过程，影响性能。
5函数Fun_功能名Fun_Split Pacal命名法。
6触发器trig_表名_操作类型trig_user_data_I (插⼊)
trig_user_data_I_D_U ( 增、
删、改 )
7主键PK_表名PK_user_data
8外键FK_表名_字段名FK_user_data_departmentId外键约束会导致性能下降、系统迁移⿇烦。
尽量不要建⽴外键，外键在⼼⾥。
9默认约束DF_表名_字段名DF_user_data_insertTime
10检查约束CK_表名_字段名CK_user_data_birthday
11视图view_表名_功能view_user类似表名
12函数Fun_操作Fun_Split
13索引IX_表名_字段名  1. IX_user_data_name_addTime
2.
IX_DBA_user_data_account
DBA优化时创建的索引以 “IX_DBA_” 开头
14其它对象（序列、⾃定义类
型等）
seq_表名或功能名seq_order_data命名规则类似表名
15其它可编程对象命名规则类似存储过程
另外：所有数据库对象名称不允许⽤中⽂，不允许包含空格，不要使⽤数据库保留字。
建表及相关对象规范
1. SQL Server 的未来版本中将删除 ntext、text 和 image 数据类型。请避免在新开发⼯作中使⽤这些数据类型，并考虑修改当前使⽤这些数据类型的应⽤程序。请改⽤
nvarchar(max)、 varchar(max)和 varbinary(max) 。
2. 在满⾜需要的同时尽量选⽤占⽤空间较⼩的数据类型，⽐如时间类型可选类型主要有 date, smalldatetime, datetime, datetime2 ，如果只需要记载年份，则 date 就够⽤了，可以节省硬盘和内存空间，⽽且效
率更⾼;
3. 字符串统⼀采⽤ nvarchar 。对于较⼤的表( 百万级以上 )，只⽤到了纯字母和数字的字段，⽽且性能要求较⾼的情况下可以⽤ varchar ;
4. 尽量减少不必要的约束，这些虽然带来⽅便，但会影响增、删、改的速度;
5. 尽量少⽤触发器，触发器的逻辑应尽可能简单，且不允许出现错误（必须有判断机制），不允许远程操作（远程操作可能有⽹络错误导致原有的增删改都⽆法完成）;
6. 对于可以⽤其它信息得到的列（如根据⽣⽇列可以得到年龄；根据当前时间判断当前记录是否已过期的），请使⽤计算列，⽽不是⽤定时任务反复去更新导致表频繁更新⽽影响数据库性能。
7. 对于增、删、改⾮常频繁的表，不应有太多的索引，⽽且记录数不宜过多。
8. 每个表都必须建⽴主键。⽆主键的表会带来阻塞，数据重复等不必要的问题。bigint ⽐ guid 建⽴的主键效率更⾼，如果⼀定要⽤ guid 作为主键，看是否能⽣成有序的 guid ，如果不能，主键不要使⽤聚集索
引，可以⽤其它常⽤列（如 insertTime）作为聚集索引。
9. 作为与其它表连接的字段，必须建⽴索引。
数据库设计规范
1. 三范式
数据库设计中应尽可能遵守三范式。所谓三范式即：
2.适当的冗余
但是完全按照规范化设计的系统⼏乎是不可能的，除⾮系统特别的⼩，在规范化设计后，有计划地加⼊冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作⽤。冗余可以是为了编程⽅便⽽增加，也可以是为了性能的提⾼⽽增加。从性能⾓度来说，冗余数据库可以分散数据库压⼒，冗余表可以分散数据量⼤的表的并发压⼒，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提⾼效率。
⽐如⼀些⽇志表的历史统计信息，我们可以通过作业定期在数据库负载较⼩的凌晨8点对数据⽇志数据进⾏统计，并建⽴冗余的统计表记录下来。
3.主键
主键是必要的，SQL SERVER的主键同时是⼀个唯⼀索引，⽽且在实际应⽤中，我们往往选择最⼩的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是⽐较⼤的，这个在下⾯索引的叙述。在有多个键的表，主键的选择也⽐较重要，⼀般选择总的长度⼩的键，⼩的键的⽐
较速度快，同时⼩的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很⼤，⼀般应该选择重复率低、单独或者组合查询可能性⼤的字段放在前⾯。
4.索引
索引分为聚集索引和⾮聚集索引。
每个数据表只能建⽴⼀个聚集索引，聚集索引决定了数据在表中的物理顺序，同时⾮聚集索引依赖聚集索引存在。每⼀个⾮聚集索引B树的页节点都存有对应的聚集索引键。因此聚集索引和⾮聚集索引的选择应该遵守如下规范：
1)应尽量选择符合唯⼀约束的字段建⽴聚集索引
2)尽量选择占⽤空间较⼩的字段建⽴聚集索引，⼀般要求聚集索引⼩于900字节
3)根据数据量决定哪些表需要增加索引，数据量⼩的可以只有主键。同时对数据量⽐较⼤的表(>1000⾏)应结合数据表的使⽤情况建⽴⾮聚集索引以提⾼数据库查询的反应效率。但是过多的⾮聚集索引也会影响数据表记录的插⼊及更新速度，⼀般要求⾮聚集索引的个数不超过两位数。因此应该针对各数据表的实际情况设计索引。
4)若某列的值⼤部分是a，少数是别的值（如b,c,d…），且经常以该列的其它值(如b,c,d…)为查询条件，可以考虑对(如b,c,d…)建⽴筛选索引。
5)把经常⼀起出现的字段组合在⼀起，组成组合索引，组合索引的字段顺序与主键⼀样，也需要把最常⽤的字段放在前⾯，把重复率低的字段放在前⾯，同⼀索引中的组成列最好不要超过3列。
6)根据使⽤频率决定哪些字段需要建⽴索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
7)若表主要⽤来查询，则可按需要建⽴索引，若对表操作主要是UPDATE，则尽可能少建索引。
5.主键与聚集索引的关系
在数据库设计中，我们经常容易混淆主键和聚集索引的关系。因为如果我们建⽴主键的时候没有特别说明，SQL SERVER会默认在主键上建⽴聚集索引。同时由于聚集索引同时也是唯⼀索引，⽽且主键⼀般为较⼩的键。所以我们经常将主键作为聚集索引。但是这并不表⽰主键和聚集索引等同。
存储过程（SQL）编写规范
⼀、注释
在 SSMS 界⾯下右键 “存储过程” -> “新建存储过程” ⽣成的脚本即可。在后期修改后加上修改的注释，便于后期跟进。
1        2        3        4        5        6        7        8        9        10        11        12        13        14        15-- ============================================= -- Author:      yenange
-- Create date: 2018-04-01
-- Description: 测试
-- Modify date: 2018-04-29
-- Modify desc: 增加参数
-- ============================================= CREATE
PROCEDURE
dbo.Proc_Test
@p1
BIGINT
AS
BEGIN
SET
NOCOUNT
ON
;
SELECT
@p1
AS
r;
END
GO
⼆、前后有 BEGIN  ... END GO ，便于与其它脚本区分开。
三、保留字⼀律⼤写，表名和函数或等对象⼀律加上架构名 dbo. ( 或其它 );
四、不要使⽤SELECT * 需要哪些字段，查询哪些字段, 尽可能少的返回结果集⾏的数量。
五、尽量避免 GOTO ，这会使可读性降低。
六、对于多表连接及查询，可以使⽤别名⽤于简化。
七、性能相关：
1) Where⼦句尽量避免使⽤函数；
2) 避免在ORDER BY⼦句中使⽤表达式；
3) 限制在GROUP BY⼦句中使⽤表达式；
4) 慎⽤游标；
5) 避免隐式类型转换，例如字符型⼀定要⽤’’，数字型⼀定不要使⽤’’；
6) 查询语句⼀定要有范围的限定，避免全表扫描操作；
7) 慎⽤DISTINCT关键字；
8) 慎⽤OR关键字，可以⽤UNION ALL替代；
9) 除⾮必要，尽量⽤UNION ALL⽽⾮UNION
10) 使⽤EXISTS(SELECT 1)替count(*)来判断是否存在记录；
11) SET NOCOUNT ON 语句
把 SET NOCOUNT ON 语句放到存储过程和触发器中，作为第⼀句执⾏语句。
⼋、尽量使⽤索引
1. SARG (Searchable Arguments)操作，因为它通常是指⼀个特定的匹配，⼀个值得内的匹配或者两个以上条件的AND连接。
如果⼀个阶段可以被⽤作⼀个扫描参数（SARG），那么就称之为可优化的，并且可以利⽤索引快速获得所需数据。
SARG包含以下操作符=、>、<、>=、<=、BETWEEN及部分情况下的LIKE。LIKE是否符合SARG，要看通配符%所在的位置。属于 SARG 的情况：
属于 SARG不属于 SARG
LIKE 'abc%'LIKE '%abc%', LIKE '%abc'
type = 1, type>1, type<5type <> 1
2. 尽可能不要包装字段：
正确：⽇期 >= '2018-04-28' AND ⽇期< '2018-04-29'
错误：DATEDIFF( DAY, ⽇期, '2018-04-28') =0
3. 不要在SQL中判断参数
1        2        3        4--前提：参数情况未知DECLARE
@sql NVARCHAR(
MAX
),@account NVARCHAR(50) SET
@account =
'test'
--正确: 拼接SQL
5        6        7        8        9        10        11        12SET
@sql=
'SELECT [name] FROM user_data WHERE 1=1 '
IF @account
IS
NOT
NULL
BEGIN
SET
@sql=@sql+
' AND account=@account'
END
EXEC
sp_executesql @sql,N
'@account NVARCHAR(50)'
,@account
-
-错误：不拼接SQL，在 SQL 中判断SELECT
[
name
]
FROM
user_data
WHERE
@account
IS
NULL
OR
account=@account
九. 事务和锁
事务是数据库应⽤中和重要的⼯具，它有原⼦性、⼀致性、隔离性、持久性这四个属性，很多操作我们都需要利⽤事务来保证数据的正确性。在使⽤事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下⽅⾯需要特别注意：
1) 凡是不怕脏读的地⽅，使⽤NOLOCK提⽰查询优化器
在繁忙的系统中，对改善并发问题，是个不错的选择；
2) 在存储过程，触发器，以及SQL 簇中，尽可能按照相同的循序来访问相关的表。这样可以减少死锁的机会；
3) 事务尽可能短
4) 在事务中涉及到数据修改量，尽可能⼩，提⾼事务中每个语句的效率，利⽤索引和其他⽅法提⾼每个语句的效率可以有效地减少整个事务的执⾏时间。
5) 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。
6) 尽可能低的设置锁，以及隔离的级别。
7) 尽量不要指定锁类型和索引，SQL SERVER允许我们⾃⼰指定语句使⽤的锁类型和索引，但是⼀般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在⽬前情况下更有，但是数据量和数据分布在将来是会变化的。
⼗、注意临时表和表变量的⽤法
在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的⽤法，需要注意：
1) 如果语句很复杂，连接太多，可以考虑⽤临时表和表变量分步完成。
2) 如果需要多次⽤到⼀个⼤表的同⼀部分数据，考虑⽤临时表和表变量暂存这部分数据。
3) 如果需要综合多个表的数据，形成⼀个结果，可以考虑⽤临时表和表变量分步汇总这多个表的数据。
4) 表变量尽量加上主键，以提⾼查询和连接性能;
5) ⼀般五千条数据或以下可以⽤表变量，数据较多或需要复杂连接（需要其它索引）的情况下，应该⽤临时表。
⼗⼀、⼦查询的优化：
1        2        3        4        5        6        7        8        9        10        11        12        13        14        15        16        17        18        19--1) NOT IN、NOT EXISTS的相关⼦查询可以改⽤LEFT JOIN代替写法。SELECT
BEA.[AddressID]
,BEA.[AddressTypeID]
FROM
[AdventureWorks2012].[Person].[BusinessEntityAddress] BEA
WITH
(NOLOCK)
WHERE
BusinessEntityID
NOT
IN
(
SELECT
BusinessEntityID
FROM
[AdventureWorks2012].[Person].[BusinessEntity]
WITH
(NOLOCK))
--可以改写成
SELECT
BEA.[AddressID]
,BEA.[AddressTypeID]
FROM
[AdventureWorks2012].[Person].[BusinessEntityAddress] BEA
WITH
(NOLOCK)
LEFT
JOIN
[AdventureWorks2012].[Person].[BusinessEntity] BE
WITH
(NOLOCK)
ON
BEA.BusinessEntityID = BE.BusinessEntityID
WHERE
BE.BusinessEntityID
IS
20        21        22        23        24        25        26        27        28        29        30        31        32        33NULL
--2)如果保证⼦查询没有重复，IN、EXISTS的相关⼦查询可以⽤INNER JOIN 代替。SELECT
BEA.[AddressID]
,BEA.[AddressTypeID]
FROM
[AdventureWorks2012].[Person].[BusinessEntityAddress] BEA
WITH
(NOLOCK)
WHERE
BusinessEntityID
IN
(
SELECT
BusinessEntityID
FROM
[AdventureWorks2012].[Person].[BusinessEntity]
WITH
(NOLOCK))
--可以改写成：
SELECT
BEA.[AddressID]
,BEA.[AddressTypeID]
FROM
[AdventureWorks2012].[Person].[BusinessEntityAddress] BEA
WITH
(NOLOCK)
INNER
JOIN
[AdventureWorks2012].[Person].[BusinessEntity] BE
WITH
(NOLOCK)
ON
BEA.BusinessEntityID = BE.BusinessEntityID
--3)不要⽤COUNT(*)的⼦查询判断是否存在记录，最好⽤LEFT JOIN或者EXISTS SELECT
BEA.[AddressID]
,BEA.[AddressTypeID]
FROM
[AdventureWorks2012].[Person].[BusinessEntityAddress] BEA
WITH
(NOLOCK)
WHERE
(
SELECT
COUNT
(*)
FROM
[AdventureWorks2012].[Person].[BusinessEntity]
WITH
(NOLOCK))=0
--可以改写成：
SELECT
BEA.[AddressID]
,BEA.[AddressTypeID]
FROM
[AdventureWorks2012].[Person].[BusinessEntityAddress] BEA
WITH
(NOLOCK)
LEFT
JOIN
[AdventureWorks2012].[Person].[BusinessEntity] BE
WITH
(NOLOCK)
当然，对于数据库优化来说，没有⼀定之规，数据量的⼤⼩、统计信息、服务器硬件等都会影响最终的效果，最好的⽅式还是实际看执⾏计划。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。