sqlserver数据规范二：数据库级代码规范

最新推荐文章于 2023-05-03 02:06:31 发布

cs9088

最新推荐文章于 2023-05-03 02:06:31 发布

阅读量331

点赞数

文章标签：数据库

需求：翻译Pinal Dave pinal@sqlauthortiy.com大师的代码规范，借鉴并规范公司数据库开发。

1：为sqlserver的数据库对象使用pascal 标准，表，视图应该使用字母s结尾。
原因：表存的数据都是复数的原因，开发很多框架例如 entity framework 生成的表也带复数
例：

点击(此处)折叠或打开

userdetails
emails

2：如果你的表设计项目很大，那么应该分组，分组方式加前缀。例：

点击(此处)折叠或打开

page_userdetails
page_emails

3：使用下面的语法规则使得存储过程更加清晰易懂：
其中 action_type 为 get,delete,update,write,archive, insert ，，等等例：

点击(此处)折叠或打开

spapplicationname_getuserdetails
spapplicationname_updateEmails

4：触发器的命名规则:TR_XXX

点击(此处)折叠或打开

TR_emails_logemailchanges
TR_userdetails_updateusername

5：索引命名规则：IX_XXX

点击(此处)折叠或打开

ix_userdetails_userid

6：主键命名规则 PK_XXX

点击(此处)折叠或打开

PK_userdetails
PK_emails

7：外键命名规则 FK__XXX

点击(此处)折叠或打开

FK_userdetails_emails

8: 默认列命名规则 DF__XXX

点击(此处)折叠或打开

DF_userdetails_username

9：使用第三方的数据库建模工具带来的规范格式

点击(此处)折叠或打开

Q. What is Third Normal Form and what is its advantage?
A. Third Normal Form (3NF) is most preferable normal form in RDBMS.
Normalization is the process of designing a data model to efficiently store data in a database.
The rules of 3NF are mentioned here
Make a separate table for each set of related attributes, and give each table a primary key.
If an attribute depends on only part of a multi-valued key, remove it to a separate table
If attributes do not contribute to a description of the key, remove them to a separate table.
Normalization is very close to concept of object oriented schema’s and it stores one data at
only one place by removing all the redundant data. It also helps to draw the schema easier.
Normalization comes at the cost of performance

10：避免 select *

点击(此处)折叠或打开

select * from user_details;
select username,password from userdetails;

11: 避免使用临时表，因为由此产生过多的磁盘I/O，通常使用普通表CTE（common table exression）替代
但是要注意查询范围。要避免过多的子查询嵌套，替代方案为 WITH AS 语句。

Following Image of Execution Plan displays that the performance for both of them is same
with regard to each other in one batch. This MAY NOT be true when there is a complex query in issue.
For most of the time, it is will be same.

点击(此处)折叠或打开

query1：Paging Method Using Derived Table
USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140
SELECT FirstName, LastName, EmailAddress
FROM (
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC) PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO
query2： Paging Method Using CTE
USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120;
SET @EndRow = 140;
WITH PersonContact AS
(
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC)
SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

12：使用缩进排版编写sql语句,突出关键字。
13：使用大写字母突出sql关键字

点击(此处)折叠或打开

SELECT ,UPDATE,INSERT,WHERE,INNER JOIN,AND,OR...

14：每个表必须有主键，没有主键列需使用identifiy 自动设定。

15：数据建模表关联设计

15：数据建模表关联设计
如果一个表（table1）和另一个表（table2）的关联，那么关联字段应该遵循：
table2 --->table1id
16：默认值字段不能为空
17：在做update，delete等dml 的时候，最好是要加上主键的where 条件，能避免失误。
18：写存储过程，尽量使用同一个库的表，这能减少网络消耗。
19：表之间数据拷贝，尽量少用loop 插入方式。使用select 插入方法
如： insert into ..select ..from
select into from where
20: 数据库对象命名不能有空格：
例子：[order details]
21: 切记不要使用保留字来命名数据库对象。这会导致一些无法预测的情况。
22：养成注释的习惯。不管是存储过程。触发器和sql，它不会降低你的性能
23：不要使用过长的like 字符查询一个走了索引执行计划的sql 语句
24： select * 和select 1 判定子查询性能对比：
测试结果，其实区别不大，推荐select 1

点击(此处)折叠或打开

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
use tpcc
go
SET STATISTICS TIME ON
SET STATISTICS IO ON
IF EXISTS(
SELECT *
FROM dbo.order_line
WHERE ol_i_id =151078 )
SELECT 'SELECT *'
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
SET STATISTICS IO ON
USE tpcc
GO
IF EXISTS(
SELECT 1
FROM dbo.order_line
WHERE ol_i_id =151078 )
SELECT 'SELECT 1'
GO

25: 默认约束须在字段级定义，其他约束须在表级定义
比如：
字段级定义约束：

点击(此处)折叠或打开

USE AdventureWorks2008
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
GO
USE AdventureWorks2008
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
);
GO

表级定义约束

点击(此处)折叠或打开

CREATE TABLE Products
(
ProductID INT,
ProductName VARCHAR(25)
CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
);
ALTER TABLE Products
ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
go
ALTER TABLE Products
DROP CONSTRAINT pk_products_pid;
GO
CREATE TABLE ProductSales
(
SalesID INT,
ProductID INT,
SalesPerson VARCHAR(25)
CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),
CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
);
GO

26: 不要希望于数据库规范能替代约束。
27：尽量不要使用recompile命令对存储过程重新编译。这样会降低性能
原因是Compile Blocking的问题
解释：
由于编译存储过程导致被锁定，在master.sys.sysprocesses视图中或sp_lock存储过程中观察到的等待资源字段中的内容是“COMPILE”，
或者使用SQL PROFILER 录制过程中出现大量的“SP:REComplie”事件。由于重新编译需要耗费CPU资源，所以，此种锁定是在一长串的被锁定连接中，
单一锁定者锁定时间不长，但整个链接各点都有一点耗时，所以在链接尾端的被锁定者需要等待较长时间。同时会出现CPU的使用率比较高。
当存储过程中使用了缓存数据表，而该缓存数据表还需要设置结构，如需要要设置主键或者利用缓存数据表开打开游标，则每次调用该存储过程进，
都会要求重新编译。或这个存储过程是当应用程序执行时，常常会被调用的热门存储过程，就会出现Compile Blocking的状况出现。
但存储过程第一次使用时，也会需要编译，所以不要一看到是在等待编译，就以为是COMPILE Blocking现象。

28：存储过程declare 必须开头，这样使得执行计划能重用

29: set 关键字在存储过程中的位置？

点击(此处)折叠或打开

CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String,@Count,1) >= '0'
AND SUBSTRING(@String,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
END
SET @Count = @Count + 1
END
RETURN @IntNumbers
END
GO

30：begin...end 是存储过程的标志

点击(此处)折叠或打开

CREATE PROCEDURE usp_SelectRecord
AS
BEGIN
SELECT *
FROM TABLE
END
GO

31：使用括号增强sql的可读性

点击(此处)折叠或打开

if((select 1 from tablename where 1=2)
isnull)

32: 注释规范单行注释使用 -- 程序块级别注释用 /* */
33: 尽可能少使用cross join
34：不需要反馈结果的，那就别返回

点击(此处)折叠或打开

比如：
if exists( select 1 from userdetails where userid=50)
比下面代码好：
if exists (select count(userid) from userdetails where userid=50)

35: index seek 比 index scan,table scan 好：原因
索引是一颗B树，
index seek是查找从B树的根节点开始，一级一级找到目标行。
index scan则是从左到右，把整个B树遍历一遍。
假设唯一的目标行位于索引树最右的叶节点上（假设是非聚集索引，树深度2，叶节点占用k页物理存储）。
index seek引起的IO是4，而index scan引起的IO是K，性能差别巨大。
seek:从B树根到叶节点的过程，
scan：当SEEK完成后，在叶节点执行范围或全部扫描（按查询的选择性会有不同)
36: 使用 nvarchar,varchar,varbinary 替代 ntext,text, image 数据类型。
37：不要使用二进制，图片数据类型存入数据库，替代的是外部文件的链接存储到数据库。
38：避免使用动态sql，而应该使用静态sql，每次只需的时候，动态sql都需要重新生成执行计划？
39：尽可能减少null值字段的使用，因为这很可能增加查询复杂度，lsnull和coales 函数对处理null值
有很大帮助。
40：unicode 字符类型会占用更多的空间，没必要的话尽量使用不带n的字符类型
41：使用except，not exist 替代 left join 或 not in 能等到更好的性能

点击(此处)折叠或打开

select empno,empname from employeerecord
where sarary >1000 and salary not in
(select salary from emplpyeerecord
where salary >2000);
改成：
select empno,empname from employeerecord where salary >1000
execpt
select empno,empname from employeerecord where salary >2000
order by empname;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30182853/viewspace-2137680/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30182853/viewspace-2137680/

cs9088

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sqlserver数据规范二：数据库级代码规范

需求：翻译Pinal Dave pinal@sqlauthortiy.com大师的代码规范，借鉴并规范公司数据库开发。1：为sqlserver的数据库对象使用pascal 标准，表，视图应该使用字母s结尾。...
复制链接

扫一扫