国内SqlServer最牛人编写的MS SqlServer Sql 脚本优化规范及范例

/*==========================代码格式规范=====================*/

 

/*

1.T-SQL脚本有必要的缩进和换行，代码层次结构清晰，

一行的最大长度一般不要87个字符；代码使用统一的风格，

例如：如果使用空格作为缩进，则不能再使用TAB做缩进处理

*/

USE Test

GO

--错误的格式

DECLARE@TransactionNumber INT

,@purnoCHAR(8)

SET@purno='105336'

IFISNULL(@purno,'')<>''

BEGIN

SELECTTOP 1 @TransactionNumber=TransactionNumber FROM [SCM].[dbo].[potran01] WITH(NOLOCK) WHERE purno=@purno

END

 

--正确的格式:

DECLARE@TransactionNumber INT

,@purnoCHAR(8)

SET@purno='105336'

IFISNULL(@purno,'')<>''

BEGIN

SELECTTOP 1 @TransactionNumber=TransactionNumber 

FROM[SCM].[dbo].[potran01] WITH (NOLOCK)

WHEREpurno=@purno

END

 

/*

2.脚本中的所有关键字、系统变量名、系统函数名全部大写

（可以参考一个SQL联机帮助中对于该关键字描述时使用的是大写还是小写）

*/

 

--错误写法

selectcase @@servicename

when'mssqlserver' then @@servername

else@@servicename

end asInstanceName

 

--正确写法

SELECTCASE @@SERVICENAME

WHEN'MSSQLSERVER' THEN @@SERVERNAME

ELSE@@SERVICENAME

END ASInstanceName

 

 

/*创建对象请添加必要的注释

3.存储过程、视图、用户定义函数有合理的注释，至少包括：创建人、

创建日期、修改人、修改日期、功能描述、参数说明。

*/

 

USE Test

GO

 

/*===========================CreateSP==========================

**DB:Test

**Type:Procedure

**ObjectName:dbo.Up_Test_Print

**Creater:Cherish

**Createdate: 2008-11-7

**Modifyby: Cherish

**Modifydate: 2008-11-8

**Function:Testing print in SSB

**Variable:N/A

=====================================================================*/

CREATEPROCEDURE dbo.Up_Test_Print

AS

SETNOCOUNT ON

BEGIN

DECLARE@do INT

,@loopINT

SET@do=0

SET@loop=100

WHILE@do<@loop

BEGIN

PRINT'Test Print in sp.'

SET@do=@do+1

END

END

GO

 

 

 

/*==========================Secondpart:代码规范=====================*/

 

/*

--BYDBA1.请在代码的开始处添加USE GO指令

*/

USE DB

GO

 

/*

--BYDBA3.请指定对象的架构者(SQL2K中称为所有者)（一般都是dbo）

*/

SELECTC1,C2 

FROMdbo.Test WITH (NOLOCK)

 

 

/*

--BYDBA1.已经在数据库中，不用加库名。

*/

USE Test

GO

CREATEVIEW dbo.V_TestView

AS

SELECTC1,C2

FROM[Test].[dbo].[Test] WITH (NOLOCK)--BYDBA 1.已经在数据库中，不用加库名。

 

/*

--BYDBA1.请显示罗列表字段。

*/

USE TEST

GO

IFOBJECT_ID('dbo.Test_SELECT') IS NOT NULL

DROPTABLE dbo.Test_SELECT

CREATETABLE dbo.Test_SELECT

(

ID INTIDENTITY(1,1) NOT NULL

,SONumberINT

,CustomerNumberINT

,ShippingCodeCHAR(15)

,CONSTRAINTPK_Test_SELECT PRIMARY KEY

(

ID ASC

)

)

 

--不规范的写法

SELECT* 

FROMdbo.Test_SELECT WITH (NOLOCK)

 

--推荐的写法

SELECTID

,SONumber

,CustomerNumber

,ShippingCode 

FROMdbo.Test_SELECT WITH (NOLOCK)

 

/*

如果一个T-SQL语句涉及到多个表，则引用的每个列必须指定该列所属的对象

*/

 

--不规范的写法

SELECTedate --BYDBA 1.请指明字段的表别名。

,ISNULL(vendno,'')--BYDBA 1.请指明字段的表别名。

FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)

INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)

ONA.Item=B.Item

WHEREpurno='105336'--BYDBA 1.请指明字段的表别名。

 

--规范的写法

SELECTA.edate

,ISNULL(B.vendno,'')

FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)

INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)

ONA.Item=B.Item

WHEREA.purno='105336'

 

/*

6.在INSERT语句中，必须指定插入列的列表, 否则的话, 

表结构略有差异就会导致插入失败或者插入到不正确的列中

*/

USE TEST

GO

IFOBJECT_ID('dbo.Test_INSERT') IS NOT NULL

DROPTABLE dbo.Test_INSERT

CREATETABLE dbo.Test_INSERT

(

ID INT NOT NULL

,SONumberINT

,CustomerNumberINT

,CONSTRAINTPK_Test_INSERT PRIMARY KEY

(

ID ASC

)

)

 

IFOBJECT_ID('dbo.Test_INSERT1') IS NOT NULL

DROPTABLE dbo.Test_INSERT1

CREATETABLE dbo.Test_INSERT1

(

ID INTNOT NULL

,SONumberINT

,CustomerNumberINT

,CONSTRAINTPK_Test_INSERT1 PRIMARY KEY

(

ID ASC

)

)

 

INSERTINTO dbo.Test_INSERT1(ID,SONumber,CustomerNumber)

SELECT1,1,3

 

--错误的写法

INSERTINTO dbo.Test_INSERT--BYDBA 1.请显示指定为INNER JOIN。

SELECT *

FROMdbo.Test_INSERT1 AS A WITH (NOLOCK)

WHERENOT EXISTS(SELECT TOP 1 1

FROMdbo.Test_INSERT AS B WITH (NOLOCK)

WHEREA.ID=B.ID)

GO

 

--修改表

ALTERTABLE Test_INSERT

ADDShippingCode CHAR(15)

GO

 

--再次插入数据报错。

INSERTINTO dbo.Test_INSERT

SELECT *

FROMdbo.Test_INSERT1 AS A WITH (NOLOCK)

WHERENOT EXISTS(SELECT TOP 1 1

FROMdbo.Test_INSERT AS B WITH (NOLOCK)

WHEREA.ID=B.ID)

GO

 

 

--正确的写法为:

INSERTINTO dbo.Test_INSERT(ID

,SONumber

,CustomerNumber)

SELECTID

,SONumber

,CustomerNumber

FROMdbo.Test_INSERT1 AS A WITH (NOLOCK)

WHERENOT EXISTS(SELECT TOP 1 1

FROMdbo.Test_INSERT AS B WITH (NOLOCK)

WHEREA.ID=B.ID)

/*

7.对于SELECT中涉及的表和视图，在非事务和特别的完整性要求的上下文中，

使用TABLE Hints—WITH(NOLOCK)

*/

--Demo:

USE TEST

GO

IFOBJECT_ID('dbo.Test_NOLOCK') IS NOT NULL

DROPTABLE dbo.Test_NOLOCK

CREATETABLE dbo.Test_NOLOCK

(

ID INTIDENTITY(1,1)

,NAMECHAR(36)

)

 

DECLARE@i INT

SET @i=0

BEGINTRY

TRUNCATETABLE dbo.Test_NOLOCK

BEGINTRAN

WHILE@i<10

BEGIN

 

INSERTINTO Test_NOLOCK(NAME)

SELECTNEWID()

SET@i=@i+1

END

--COMMIT

END TRY

BEGINCATCH

ROLLBACK 

ENDCATCH

 

--不允许读脏，只能读取已经提交的数据

SELECTID,NAME

FROMdbo.Test_NOLOCK

 

--可以读取没有提交的数据

SELECTID,NAME

FROMdbo.Test_NOLOCK WITH (NOLOCK)

 

/*

为了简化控制, 所有的锁提示都需要加WITH, 即FROM tb (NOLOCK) 需要修改为 FROM tb WITH(NOLOCK) 

*/

--BYDBA1.请使用WITH(NOLOCK)，而非NOLOCK。

--IND2WHP01

SELECTVendorNumber,

WareHouseNumber,

SoNumber

FROMS7EDIDB01.EDI.DBO.EDIInvoiceMatchTransaction /*WITH*/ (NOLOCK)

WHEREsonumber = '86022942'

 

/*

8.子查询中，只查询出必须的列，不要包含与处理需求无关的列

*/

--错误的写法

SELECTID,NAME

FROMdbo.Test_NOLOCK WITH (NOLOCK)

WHEREEXISTS (

SELECT 1AS ID

, 'Name'AS Name

, 'Test'AS C3

UNIONALL

SELECT 2

, 'ABCE'AS Name

,'Test_NOLOCK' AS C3

)

 

--正确的写法

SELECTID,NAME

FROMdbo.Test_NOLOCK WITH (NOLOCK)

WHEREEXISTS( SELECT TOP 1 1 

FROM

(

SELECT 1AS ID

UNIONALL

SELECT 2

) AS A

)

/*

--BYDBA1.变量赋值,请修改为SELECT TOP 1...

*/

USE TEST

GO

IFOBJECT_ID('dbo.Test_TOP1') IS NOT NULL

DROPTABLE dbo.Test_TOP1

CREATETABLE dbo.Test_TOP1

(

IDINT 

,TransactionNumberCHAR(25)

,purnoCHAR(8)

)

INSERTINTO dbo.Test_TOP1(ID,TransactionNumber,purno)

SELECT234434,'1111111111','105336'

UNIONALL

SELECT234445,'2222222222','105336'

UNIONALL

SELECT234345,'fdfdrynkjs','1053334'

 

SELECTTransactionNumber,purno 

FROMdbo.Test_TOP1 WITH (NOLOCK)

 

--错误的写法一

DECLARE@TransactionNumber CHAR(25)

SELECT@TransactionNumber=ISNULL(TransactionNumber,'')--BYDBA 1.变量赋值,请修改为SELECT TOP 1...

FROM[dbo].[Test_TOP1] WITH (NOLOCK)

WHEREpurno='105336'  

 

SELECT@TransactionNumber

 

----错误的写法二

SET@TransactionNumber= ISNULL(

(SELECTTOP 1 TransactionNumber

FROM[dbo].[Test_TOP1] WITH (NOLOCK)

WHEREpurno='105336' )

,'')

 

 

SELECT@TransactionNumber

 

--正确的写法

SELECTTOP 1 @TransactionNumber=ISNULL(TransactionNumber,'')

FROM[dbo].[Test_TOP1] WITH (NOLOCK)

WHEREpurno='105336'  

 

SELECT@TransactionNumber

 

/*

创建索引时,显示定义索引的类型(CLUSTERED OR NONCLUSTERED)、FILLFACTOR   

*/

 

CREATENONCLUSTERED INDEX ix_TableName_Column1_Column2 ON dbo.TableName

(

Column1ASC

,Column2DESC

)

WITHFILLFACTOR = 80

 

/*

禁止在使用了事务的情况下，不编写防止造成未提交或者未回滚事务的情况的处理代码 

*/

USE TEST

GO

IFOBJECT_ID('dbo.Test_TRAN') IS NOT NULL

DROPTABLE dbo.Test_TRAN

CREATETABLE dbo.Test_TRAN

(

ID INTIDENTITY(1,1)

,NumberINT

)

 

BEGIN

BEGINTRAN

INSERTINTO dbo.Test_TRAN(Number)

SELECT12

UNIONALL

SELECT1/0

COMMIT

END

 

/*

对于类型不相同的两个数据进行逻辑处理（例如：比较或者赋值）时，使用显式的数据类型转换(CAST或者CONVERT)，特别是在WHERE语句中

*/

/*

--BYDBA3.请保证WHERE语句中=两边的数据类型一致，否则SQLServer走不到Index

使用显式的数据类型转换（CAST或者CONVERT）

*/

 

--D2WHP01

 

--不正确的写法

SELECTA.edate

,B.vendno

FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)

INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)

ONA.Item=B.Item

WHEREB.purno=105336

 

--正确的写法

SELECTA.edate

,B.vendno

FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)

INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)

ONA.Item=B.Item

WHEREB.purno=CONVERT(CHAR(8),105336)--B.purno的数据类型为char(8)

--或者是

SELECTA.edate

,B.vendno

FROM[SCM].[dbo].[arinvt01] AS A WITH (NOLOCK)

INNERJOIN [SCM].[dbo].[potran01] AS B WITH (NOLOCK)

ONA.Item=B.Item

WHEREB.purno='105336'

 

/*

SELECTTOP 1 ... ORDER BY与MAX，MIN

*/

SELECTTOP 1 item

FROM[SCM].[dbo].[potran01] WITH (NOLOCK)

ORDER BYitem DESC

 

 

--建议使用MAX函数。

SELECTMAX(item)

FROM[SCM].[dbo].[potran01] WITH (NOLOCK)

 

/*----------------------BYDBA3.去掉RTRIM函数。--------------------

1.去掉Isnull里面的RTRIM函数

2.使用函数 LEN()的时候，建议去掉 RTRIM。

3.做字符串比较，SQLServer会忽略掉尾部的空格

*/

DECLARE@string CHAR(50)

SET@string='Test isnull  '

 

--Isnull

SELECTLEN(ISNULL(@string,''))

,LEN(ISNULL(RTRIM(@string),''))

 

--使用Len函数

SELECTLEN(RTRIM(@string))

,LEN(@string)

 

--字符串比较

IF@string<>'Test isnull'

SELECT'Not Equal'

ELSE IF@string='Test isnull'

SELECT'Equal'

ELSE

SELECT'Unknow'

 

 

/*创建Index需要注意的地方。

--BYDBA1.请按照规范对INDEX命名，IX_表名_字段名。

--BYDBA1.创建索引时请显式申明聚集类型 NONCLUSTERED

--BYDBA1.添加索引的时候，请指定填充因子 WITH FILLFACTOR = 90

*/

CREATENONCLUSTERED INDEX IX_DBMOVE_Training_Trn_Date

ONdbo.DBMOVE_Training

(

Trn_DateASC

)WITHFILLFACTOR=80

 

/*

12.禁止在使用了事务的情况下，不编写防止造成未提交或者未回滚事务的情况的处理代码

--BYDBA1.SP中禁止使用PRINT语句。

*/

BEGINTRANSACTION;  

BEGINTRY  

INSERTINTO dbo.users(id

,name

,Age)

SELECTUID,U_Name,U_Age 

FROM#Temp AS a

WHERENOT EXISTS(SELECT TOP 1 1

FROMdbo.users AS b WITH (NOLOCK)

WHEREb.id=a.UID)

PRINT'Insert successfully'--BYDBA 1.禁止在存储过程中输出不需要的信息

COMMIT TRANSACTION

END TRY

BEGINCATCH--BYDBA 1.请编写防止造成未提交或者未回滚事务的情况的处理代码。

ENDCATCH

 

/*

14.定义游标时，如果不是特别需要，使用LOCAL关键显式的将游标定义为局部游标，

尽量避免使用全局（GLOBAL，这是数据库的默认行为

 

）游标；

没有特殊需要的话，尽量使用FORWARD_ONLY READONLY 游标,同时尽量使用静态游标  STATIC；

避免大量数据的处理避免使用游标"

*/

DECLAREcur_MyCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

FOR

SELECTTOP 10 QUOTENAME(SCHEMA_NAME(schema_id),'[]')+'.'+QUOTENAME(name,'[]')

FROMsys.tables WITH (NOLOCK)

 

/*---------------BYDBA3.尽量不使用游标,请使用其他的方法。----------------

1.拼接字符串

2.update/delete/insert记录

*/

 

/*

15.缓存临时数据时，使用真正的临时表（#或者##开头）的表，避免将临时表缓存到正式表中

--这个会在稍后的专题Demo中有演示

*/

 

/*

16.动态T-SQL处理语句中，如果涉及到变量，尽量使用sp_executesql，通过参数传递进行处理，

避免使用EXEC硬拼SQL语句存储过程或函数中不允许包含未使用的参数或变量

--这个会在稍后的专题Demo中有演示

*/

 

/*

17.脚本中，禁止出现对正式对象的DROP

*/

DROPPROC dbo.up_test

DROPTABlE dbo.Test

DROPVIEW dbo.V_Test

 

--MarkDelete

 

/*

18.能使用INNERJOIN实现的处理，不要使用外连接

*/

 

DECLARE@t1 TABLE

(

id INT

,nameVARCHAR(10)

)

 

DECLARE@t2 TABLE

(

id INT

,nameVARCHAR(10)

)

 

INSERTINTO @t1

SELECT1,'a'

UNIONALL

SELECT2,'b'

UNIONALL

SELECT3,'c'

 

INSERTINTO @t2

SELECT4,'a'

UNIONALL

SELECT2,'b'

UNIONALL

SELECT5,'c'

 

---不推荐的写法

SELECTA.id

FROM @t1AS A

LEFTJOIN @t2 AS B

ONA.id=B.id AND B.name='B'

WHEREB.id IS NOT NULL

 

--推荐写法一

SELECTA.id

FROM @t1AS A

INNERJOIN @t2 AS B

ONA.id=B.id 

WHEREB.name='B'

 

--推荐写法二

SELECTA.id

FROM @t1AS A

WHEREEXISTS(

SELECTTOP 1 1

FROM @t2AS B

WHEREA.id = B.id

ANDB.name='B'

)

--推荐写法三

SELECTA.id

FROM @t1AS A

WHERE idIN(

SELECTid

FROM @t2AS B

WHEREB.name='B'

)

 

--BYDBA3.请将Left join连接修改为Inner Join连接。（Left连接表中在Where子句中有筛选条件）。

SELECTA.id,B.name

FROM @t1AS A

LEFTJOIN @t2 AS B

ONA.id=B.id 

WHEREB.name='B'

 

SELECTA.id,B.name

FROM @t1AS A

INNERJOIN @t2 AS B

ONA.id=B.id 

WHEREB.name='B'

 

 

/*

19.数据本身不会重复，或者不需要防止重复的UNION，改用UNION ALL

*/

DECLARE@t1 TABLE

(

id INT

,nameVARCHAR(10)

)

 

DECLARE@t2 TABLE

(

id INT

,nameVARCHAR(10)

)

 

INSERTINTO @t1

SELECT1,'a'