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/