SQL SERVER数据库脚本开发规范.docx

1、SQL SERVER数据库脚本开发规范SQL SERVER数据库脚本开发规范修订历史记录版本 改版日期 改版说明* 作者 备注 V0.9 2010-8-17 C 创建稿 Simon Cheng V1.02010-8-31A追加何根华V1.02010-8-31A追加王永庚 注：改版说明中填写C-创建, A-追加,M-修改,D-删除,R-评审一、命名规范1)命名标志法用Pascal标志法，给数据库对象命名，Tables，Views，Store Procedures。例如：Contracts；ContactPhones2)一组相关表的命名如果有一组相关的表，需要加前缀。以下划线“_”分割。例如：Pr

2、oducts_USAProducts_IndiaProducts_Mexico3)存储过程命名GroupName_。 这里的Action如：Get，Delete，Update，Write，Archive，Erase等动词。例如： ARP_GetStockProcess; GetOrgUsers注：GroupName见附件4)禁止使用触发器命名, 5)索引命名IX_。例如：IX_UserDetails_UserID。6)主键PK_。7)外键FK_。例如：FK_UserDetails_Emails。8)引用字段命名一个表如有引用字段，指有外键的列，该字段名格式为：ID;9)缺省值DF_。例如：DF

3、_UserDetails_UserName。 10)禁止使用空格在数据库对象命名时，禁止使用空格。11)数据库保留字不要使用数据库保留字，给数据对象命名；12)视图的命名用Pascal标志法，和表一致；13)字段的命名用Pascal标志法，单词中间不用下划线二、书写格式1)SELECT语句不要在SELECT语句后面用星号*，表名前需要加Owner（dbo）。在SELECT语句后面写明需要的列名。例如：SELECT MainDeptId ,PowerDesc FROM dbo.AppDatabases WITH(NOLOCK)2)数据库对象引用在多表关联时，列名前需要加上别名（或表名），表名前加

4、Owner（dbo）。如果涉及到跨数据库，就需要加上Database名称。例如：例如：AdventureWorks.dbo.Contact；存储过程也一样；3)不要拼SQL语句从安全角度出发，尽可能用参数化来实现；4)明确写出关联方式（不要用旧的书写方式）-错误的格式:SELECT *FROM Table1, Table2WHERE Table1.d = Table2.c-正确的格式:SELECT Tb1.Columns1 ,Tb2.Columns2FROM Table1 Tb1 WITH(NOLOCK)INNER JOIN Table2 Tb2 WITH(NOLOCK)ON Tb1.d =

5、Tb2.c5)SQL语句缩进方式SQL语句要按照缩进方式书写。提高可读性。例如：Example：Wrong FormatSELECT E.UserName,UD.PasswordEncode FROM DBO.UserDetails UD WITH(NOLOCK) INNER JOIN Employee E WITH(NOLOCK) ON E.EmployID = UD.UserIDExample：Correct FormatSELECT E.UserName,UD.PasswordEncodeFROM DBO.UserDetails UD WITH(NOLOCK) INNER JOIN Em

6、ployee E WITH(NOLOCK) ON UD.UserID = E.EmployID6)SQL保留字要大写对SQL的保留字，都需要大写。例如：SELECT，UPDATE，INSERT，WHERE，INNER JOIN，AND，OR等。7)不使用系统前缀不要用系统对象使用了的前缀来命名。例如“sp_”是数据库系统自身存储过程的前缀（请参存储过程的命名格式）；8)注释的重要性，注释是必须的不要忘记在存储过程，触发器，SQL Batches中添加注释。注释不会影响性能。9)注释部分修改的规范至少要包含，以下三个关键信息：/*修改日期：修改 人: 修改内容：*/10)注释的格式单行注释，用两

7、个减号(-)。多好注释，用(/* */)括起来；11)过多使用GOTO语句会使得代码可读性降低三、性能相关1)尽量用主键在Update和Delete的Where子句中，尽量用主键2)尽量避免使用游标Cursors建议使用SELECT语句，或用While Loop语句实现相同功能； 方法说明：每个表基本上都有一个自增的唯一标志字段（字符串也有一样），先取出符合条件的最大最小值，然后在这个范围内部循环，就可以达到和Cursor一样的功能。把查询结果放到带有自增列的表变量中去；下面是一个简单循环控制的例子：DECLARE TransactionID INTDECLARE TransactionTyp

8、e NCHAR(1)DECLARE Quantity INTSET TransactionID = ( SELECT MIN(TransactionID) FROM TransactionHistory)WHILE TransactionID IS NOT NULLBEGIN SET TransactionID = ( SELECT MIN(TransactionID) FROM Production.TransactionHistory WHERE TransactionID TransactionID ) -Do something hereEND3)SET NOCOUNT ON 语句把

9、SET NOCOUNT ON 语句放到存储过程和触发器中，作为第一句执行语句。例如：CREATE PROCEDURE dbo.UP_GetOrgChildrenASBEGIN SET NOCOUNT ON .关闭数据库提示输出。4)模糊查询Like语句在模糊查询Like语句中，禁止使用前置通配。即不要在单词的第一个字母使用百分号%。这样Index不再起作用；5)禁止在Where子句关联字段上使用函数这会使得Index不起作用；6)Filtered Index可以提高查询性能当特定集合经常被访问，使用Filtered Index可以提高查询性能，可以减少索引维护开销；7)尽量不用临时表建议使用派

10、生表或公用表表达式（CTE）-查找排在第二位的价格SELECT Min(UnitPrice)FROM Products With(Nolock)WHERE ProductID IN( SELECT TOP 2 ProductID FROM Products With(Nolock) ORDER BY UnitPrice DESC) 实现同样功能，可以改造成以下用派生表来表达。效率是上面的两倍：SELECT Min(UnitPrice)FROM( SELECT TOP 2 UnitPrice FROM Products With(Nolock) ORDER BY UnitPrice DESC)

11、As Pro8)WHERE子句中不用“不等于号”9)及时检查global变量ERROR在执行数据处理（INSERT/UPDATE/DELETE）后，及时检查global变量ERROR。以便在出错时，能Rollback。或者用Try Catch；10)Declare语句在存储过程的起始部分定义Declare语句。这会使查询优化器，Reuse查询计划；11)SET语句在存储过程前面（Declare后面），执行SET语句，对变量进行初始化；12)尽可能不使用Cross Join13)尽可能不使用集合返回如果在没有必要返回集合的情况下，不要使用集合返回。例如：IF EXISTS(SELECT Top

12、1 1 FROM dbo.UserDetails WITH(NOLOCK) WHERE UserID = 50)，这样比下面写法好：IF EXISTS(SELECT * FROM dbo.UserDetails WITH(NOLOCK) WHERE UserID = 50)14)数据用多少抓多少通常情况下，不要一下子返回很大的结果集。建议用Top N分页抓；15)视图使用把经常使用的，复杂的关联查询，以及计算，合并到一个视图中去。 16)尽可能不使用NText，Text，Image类型的字段可以用NVarChar(Max)，VarChar(Max)，Varbinary(Max)。不要在数据中保

13、存Binary或Image文件（二进制BLOB对象）。而是通过外部文件，数据库中保存这些外部文件的Path来实现；17)静态SQL语句尽量避免使用动态SQL语句。比如说查询条件中的字段都是变化的。动态SQL语句的执行效率比静态的要低。动态SQL语句在每次执行的时候，会重新生成执行计划；18)查询结果中尽可能减少Null值的出现因为会招致Query和Update的复杂性。可以用ISNULL或COALESCE来处理Null值； 19)Output Param参数与DataSet如果存储过程返回的结果集，始终只会是一条记录。建议使用Output Param参数的方式。因为ADO在处理Output P

14、aram的时候效率要高于DataSet； 20)有效的Indexes是提高数据性能的最好办法21)SQL语句的性能需要检查通过使用Query Analyzer，SHOWPLAN_TEXT，或者SHOWPLAN_ALL，来了解您的SQL语句的执行计划。您的查询应该是执行Index Seek，而不是Index Scan或Table Scan；四、索引相关1)原则上每个表必须建立一个主键和一个聚集索引注意主键和聚集索引之间的区别。聚集索引建在业务相关的字段上。建议建立一个业务不相关的自增字段代理主键；2)聚集索引建立在经常被作为查询条件，以及有表关联的字段上。3)索引不是越多越好索引是需要系统维护的

15、，数据更新频繁的表尤其如此。不需要的索引，一定要及时删除；4)索引经常建立的字段关联字段，查询条件，Order By，Group By5)不建议使用Distinct没有唯一性要求的地方，就不用Distinct。Union all不进行Duplicated检查的，Union是需要的；6)覆盖索引在2005/2008中提倡使用Included索引，把需要显示的字段加到索引中去，减少Page操作。添加的字段不宜多;五、死锁相关1)使用NOLOCK提示查询优化器在繁忙的系统中，对改善并发问题，是个不错的选择；2)访问相关的Tables循序宜相同在存储过程，触发器，以及SQL Batches中，尽可能按

16、照相同的循序来访问相关的Tables。这样可以减少Deadlock的机会； 3)Transaction Scope尽可能短4)在Transaction Scope中涉及到数据修改量，尽可能小5)尽可能低的设置锁，以及隔离的级别六、其他约束1)每个表都应该有主键字段通常该主键为IDENTITY类型。2)有Default Value限制的字段不允许设置为可以为空（Nullable）3)在相关表存在的DataBase下创建存储过程和函数4)SQL归档文件格式以存储过程为例：USE NorthwindGO/*执行位置：mliappdb.rdtry对象名称: dbo.CustOrderTotal对象类型

17、：STORE PROCEDURE入口参数： CustomerID 用户ID出口参数： TotalSales 总销售额对象说明：根据CustomerID，计算该用户总的销售额创建日期: 08/18/2010 09:39:13创建 人: Simon Cheng修改日期：修改 人: 修改内容：*/ALTER PROCEDURE dbo.CustOrderTotal CustomerID NCHAR(5), TotalSales MONEY OUTPUTASBEGIN SET NOCOUNT ON -关闭输出 /* 统计该客户的销售总额 其中需要扣除折扣 */ SELECT TotalSales =

18、SUM(OD.UNITPRICE*(1-OD.DISCOUNT) * OD.QUANTITY) FROM ORDERS O WITH(NOLOCK) , ORDERDETAILS OD WITH(NOLOCK) WHERE O.CUSTOMERID = CustomerID AND O.ORDERID = OD.ORDERIDEND5)Constraint创建级别Default Constraint必须建在Column级别上。其他类型的Constraint建立在Table级别上。例如：A.Column Level：CREATE TABLE ProductSales( SalesID INT C

19、ONSTRAINT PK_ProductSales_SID PRIMARY KEY, ProductID INT CONSTRAINT FK_ProductSales_PID FOREIGN KEY REFERENCES Products(ProductID), SalesPerson VARCHAR(25);B.Table LevelCREATE TABLE ProductSales( SalesID INT, ProductID INT, SalesPerson VARCHAR(25) CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),

20、 CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID);6)带INSERT的SELECT语句当从一个表向另外一个表Insert数据时（列出字段），尽量使用带INSERT的SELECT语句。而不是使用游标来做。例如：INSERT INTO DBO.TableA ( Column1 ,Column2)SELECT Column1 ,Column2FROM DBO.TableB WITH(NOLOCK)WHERE .七、附加说明1)GroupName，前缀缩写表GroupNameacp_arp_bep_csp_dtp_fup_等等.