SQL Server 数据库规范

一、 命名规范

常用对象命名规范，使用帕斯卡命名法（Pascal，单词首字母大写），统一使用英文。

1. 表。英文单数名词，尽量写完整单词名称一般不超过3个英文单词都可表达出表的意思。使用帕斯卡命名法。

如：User，UserRole，Role，Group，Family，SalesOrderDetail

错误例子：Users，UserTable

注意：特殊意义的表

自定义的元数据表，使用前缀Sys开头：SysDictionary，SysParameter，SysModel，SysRegion

业务同类表：WeChatCompany，WeChatUser，WeChatMember，SMSSend，SMSReceive

缩写表，普遍认知的缩写： US_User，EN_User，CN_User，WTO_Member，WTO_ Country

单词过多的表：User_ ZGYH（中国银行中文缩写），User_ GSYH（工商银行中文缩写）

2. 字段。与表命名雷同。

对于主键字段，统一为：ID

外键引用的字段，统一为：外键表名+ID

如 UserID，UserRoleID，SalesOrderDetailID，SysDictionaryID

3. 视图（不要使用）。

与表命名雷同。大写“V_”作为前缀，格式：V_视图名称

如：V_User，V_SysDictionary

4. 存储过程（业务处理不要使用，报表可用）。获取或执行数据的过程。

要求动名词组合。大写“USP_”作为前缀，格式：USP_存储过程名称

如：P_GetUser，P_UpdateUserByUsername，P_CleanDeletedUser，P_GetMyRecord

5. 标量函数（查询到表的不要使用）。返回单个值。

要求动名词组合，大写“FN_”作为前缀，格式：FN_函数名称

如： FN_GetUserName，FN_GetUserNameByUserID

6. 表值函数（查询到表的不要使用）。返回表。

要求动名词组合，写“TF_”作为前缀，格式：TF_函数名称

如：TF_ SplitChar，TF_ SplitCharByComma，TF_GetUserByCity

7. 触发器（不要使用）。

大写“TR_”作为前缀，触发操作为后缀，格式：TR_表/视图名称_Insert/Delete/Update

如：TR_User_Insert，TR_User_Update，TR_User_Delete

8. 索引。尽量写完涉及的字段

大写“IX_”作为前缀，格式：IX_表名称_字段名称_字段名称_……

如：IX_User_ID，IX_User_UserName_Mobile

唯一索引： UIX_表名称_字段名称

包含列索引：IX_表名称_字段名称_Include

筛选索引：IX_表名称_字段名称_Where

9. 约束。

主键约束：PK_表名称_字段名称

外键约束：FK_表名称_字段名_主表名称_主表字段（禁用外键约束！）

唯一约束：UIX_表名称_字段名称（唯一约束默认是创建唯一索引来约束）

默认值约束：DF_表名称_字段名称

Check约束：CK_表名称_字段名称

10. 临时对象。大小写不要求，其他与表名称规范相同。声明使用会话级别。

临时表：#user （不要使用 ##user）

(表)变量：@name （不要使用@@name）

二、 设计规范

对象级规范：

Ø 数据库表设计必须满足第三范式（特殊情况再讨论）

Ø 业务表都设置自增主键ID！（主键不一定是聚集索引）

Ø 禁止使用外键约束！（操作数据慢；维护数据困难）

Ø 禁止使用触发器！（程序中控制操作）

Ø 禁止使用视图！（维护不便，多表关联可能有字段没用。报表可用）

Ø 禁止使用函数访问表、视图等数据（函数只作为辅助计算工具，不参与访问数据！）

Ø 禁止使用存储过程处理业务逻辑（先在程序计算好再去读写数据库！）

Ø 禁止使用游标！（少用遍历，用集合概念来操作）

Ø 禁止使用临时表！（业务操作频繁会不可控）

Ø 禁止使用同义词！（维护易被忽略）

Ø （报表可用存储过程、视图、临时表，视图中的结果集不要有排序）

Ø （若必要使用，视图、存储过程、函数、触发器、游标等不要多层调用2 次以上！）

Ø 不要使用数据库关键字作为表名、字段名等（尤其系统对象名称，如：getdate、sum 等）

Ø 创建约束、索引等，手动设置名称，不用系统自动生成！

Ø 索引尽量不超过5个，尤其那些频繁更改和插入的表！

Ø 表和字段在数据库中须添加详细注释！（参考：sp_addextendedproperty）

Ø 视图、存储过程、函数、触发器等，须填写创建信息及每个操作的描述！

Ø 禁止在数据库中存储文件。

Ø 插入字符数据时，须去掉左右空格！

Ø 插入表时所有字段必须全部列出！（如： insert

into tab(name,phone)

select name, mobile

from user）

Ø 存储过程、函数等，模块语句加上 BEGIN……END；去掉多余的空行和空格。

Ø 程序操作表的权限只允许select、insert、update、delete！

Ø 对于可能删除的业务数据，数据库不实际删除数据，增加字段 IsDeleted 来判断是否删除！

Ø 操作日志、系统日志等表，只允许插入，不能再修改！

Ø 对约束较强的枚举选项，程序应设置选项而不要用户手动随意填写！

Ø 业务表都添加一个时间字段：addTime

datetime not null

constraint DF_表名称_datetime

default(getdate())

字段规范：

Ø 尽量设置使用 “not null”约束，数值默认0，字符默认为空’’。否则查询时 null 是不参与比较的！

Ø 禁用 ntext、text 和 image，用nvarchar(max)、varchar(max)和 varbinary(max)替代，尽量不用max！

Ø 非英文重要数据使用unicode类型保存，如nvarchar ，nchar，其他可以varchar；

Ø 日期时间字段统一用 datetime，精确到毫秒；

Ø 金额、小数类型使用 decimal，不用int、float、double（int可存储21亿，float、double精度不准）

Ø 状态字段统一用State（可枚举的），不用status

Ø 不允许明文存储密码！

三、 查询规范

u 定义的参数、变量类型要参考与字段类型和长度相同。

Declare @name

varchar(50)

--类型要与 User(Name)相同

Select *

from User

where Name = @name

u 少用游标遍历，用集合概念来处理数据。

u 操作使用 IN 内的常量不要过百，尽量少！应用其他方式改为表连接。

错误示例：select

* from [User] whereName

in('AA','BB','CC','DD','EE','FF',………)

u 若须用“IN”，嵌套子查询不要超过3个。

Select *

from User

where Name

in(

select Name

from Manager

where Sex

in(Select Sex from

User where Name='kk')

)

u 子查询“IN”都改为“innerjoin”方式，注意是否有一对多或多对多情况，有则先连接键分组再连接。

上面的 IN 查询可改为如下：

Select distinct t1.*

from User t1

inner join Manager t2

on t1.Name=t2.Name

inner join

User t3 on t2.Sex=t3.Sex

where t3.Name='kk'

u 能用“EXISTS ”就不用“IN”，exists 可以避免可能的错误。

--表 TestRole

不存在字段 Mobile，但是查询不会报错！

select *

from User

where name in(select Mobile

fromTestRole)

select *

from User

where name not in(select Mobile

from TestRole)

改为:

select *

from User a

where exists(select 1

fromTestRole b where a.name=b.RoleName)

--表 TestRole

字段 RoleName

只要存在一个 Null ，查询则没有结果

select *

from User

where name not in(select RoleName

from TestRole)

改为:

select *

from User

where name not in(select RoleName

from TestRole whereRoleName

is notnull)

select *

from User a

where not exists(select 1

from TestRole b wherea.name=b.name)

u 存储过程中首行添加：set nocount on ，主要是将执行结果显示的打印信息不返回客户端，减少网络IO。

CREATE PROC dbo.TestPro

AS

BEGIN

SET NOCOUNT

ON --此处添加

…………

END

GO

u 存储过程内不要打印（print）信息，理由同上。

u 查询不用星号，哪怕字段再多也写完整，并非所有字段都用的上的。且星号不能建立合适的索引。

u Where筛选中，不要在字段上使用函数，否则整表都扫描来进行函数处理。

Select *

from User

where left(Name)

= '黄'

改为：

Select *

from User

where Name like '黄%'

Select *

from User

where convert(varchar(10),AddTime,120)='2018-01-01'

改为：

Select *

from User

where AddTime>='2018-01-01'

andAddTime<'2018-01-02'

u 模糊匹配like不用讲 % 放在首位。（同上）

u 时间比较注意，不必手写那么详细

Select *

from User where addTime<='2017-12-31 23:59:59'

改为：

Select *

from User

where addTime<'2018-01-01'

u 一致性不强或需要粗略统计的大表数据或报表，可以加上Nolock 允许脏读。

Select *

from User

with(nolock)

Select *

from User

as U with(nolock)

u Count 是不统计null 的。计数使用 count(*)，MSSQL中count(*) 默认走索引长度最小的来统计。

COUNT(*)

= COUNT(1)

= COUNT('A')

Select

COUNT(*) from

User --统计表总行数

Select

COUNT(Mobile)

from User

--统计手机不为NULL的总行数

u SQL Server 中，默认 null 加上任何字符都为null，所以注意！

SELECT 'A'+NULL --结果为 NULL

SELECT 100+NULL --结果为 NULL

—正常写法，如果字段有 NULL

值，使用 ISNULL

判断更改。

Select Name

+ ISNULL(Mobile,'')

from User

注意：集合函数 sum、avg、max、min

是忽略 NULL 的。

u 除了数据需要大量导出，任何查询一定要使用分页查询。

u 一个事务中不用频繁重复读取表数据或操作数据

u 查询尽量参数化，即先声明参数，后赋值，再把参数带入执行脚本（如： where name = @name）

Select Name,Mobile

from User

where Name ='AA'

Select Name,Mobile

from User

where Name ='BB'

改为：

Declare @name

varchar(50)

Set @name

= 'AA'

Select Name,Mobile

from User

where Name =@name

go

Declare @name

varchar(50)

Set @name

= 'BB'

Select Name,Mobile

from User

where Name =@name

go

u 多表关联情况且结果集在满足情况下，尽量使用左连接(left join）而不使用内连接( inner join)

Select t1.Name

from User t1

inner join Manager t2

on t1.ManagerID=t2.ID

where t1.Mobile='13000000000'

改为：

Select t1.Name

from User t1

left join Manager t2

on t1.ManagerID=t2.ID

where t1.Mobile='13000000000'

因为 Mobile

唯一，第二种情况只查询 User

表。所以当条件不确定的时候、且不影响结果，使用第二种。

u 按添加时间排序时，性能不好可以用主键聚集索引ID排序，因为ID也是递增的。

Select top(10) Name,Mobile

from User

order by AddTime

desc

改为：

Select top(10) Name,Mobile

from User

order by ID desc

u 多表连接查询，使用简短别名，字段都加上别名且别名要统一！

Select t1.Name

from User t1

inner join Manager t2on t1.ManagerID=t2.ID

Select a.Name

from User a

inner join Manager b

on a.ManagerID=b.ID

Select u.Name

from User u

inner join Manager m

on u.ManagerID=m.ID

u 编写脚本不要写在一行上面，注意缩进，每行长度尽量不超过120个字符。

u 脚本中的系统关键字保持统一，可全部大写或全部小写或首字母大写。

u 不在数据库中使用链接服务器来跨服务器查询，程序中应从一节点读取数据后再传递到另一节点操作。

u 多表连接中，“on”后面若有 “or”，则改为 union 取连接：

Select a.name,b.name

From User a

Inner Join Manager b

On a.ManagerID=b.ID

or a.Mobile=b.Mobile

改为：

Select a.name,b.name

From User a

Inner Join Manager b

On a.ManagerID=b.ID

Union

Select a.name,b.name

From User a

Inner Join Manager b

On a.Mobile=b.Mobile

u 不要在生产库查询大量数据，占用有效数据的IO和内存。若须查询则加 nolock。

Select *

from User

with(nolock)

Select *

from User

as U with(nolock)

u 对于复杂分页查询，可以先按照条件找出主表ID（在非聚集索引中找出ID），通过ID再关联找出分页全部数据（通过聚集索引找出所有数据）

四、 维护规范

生产库使用完整模式，测试、开发库可用简单模式

操作日志表，ID通常只做主键，没什么用。可将字段“AddTime”作为聚集索引字段和分区字段。

每次修改函数、存储过程等，备份旧的副本，记录修改信息。

数据库级别的变动，先备份数据库

大量更新、删除数据时，先备份事务日志

删除、更新数据时，把需要操作的数据存储到另一个专门的数据库中作临时备份。

删除、更新数据时，小批量操作。建议每次不超过5000行，减少了事务的粒度，也防止锁升级为表锁。

定期分批重建索引，不要一次性全部重建。

其他： Disk IO、CPU、Memery、NetWork IO、迁移、扩展等性能问题，找DBA！~

SQL Server 的最大容量规范：

---------------------

本文来自 Huang-ZC 的CSDN 博客 ，全文地址请点击：