SQLServer编写规则
1、 存储过程
a) 在程序应用中,对于数据库“写”操作的功能通过存储过程来实现。
b) 存储过程命名:
SP_+表名(+功能名)
对于一个表有两个以上的存储过程时使用+功能名,功能名必须能清晰显示该功能的作用。如果是针对同一张表的添加、修改、删除操作,可以在同一个存储过程中,通过传递一个Flag参数(Int型)来区分,Flag参数定义如下:
Flag=1:添加
Flag=2:修改
Flag=3:删除
c) 如果表中的字段过多,可以将添加、修改、删除、或者其它操作分开来写
d) 存储过程必须在开始处加上注释:
/*********************************************************
** 功能:
** 输入参数:(如果通过该输入参数进行判断必须在此注明)
** 输出参数:(如果有输入参数必须在此注明)
**********************************************************/
e) 存储过程中传入参数:使用@P_开头
f) 存储过程中涉及到返回值(不是数据库中的值的时候)的地方需要返回为常量@P_ReCode,参数定义如下(针对普遍情况,不包含特殊情况处理):
ReCode = 0:操作执行正确
ReCode = -1:操作执行失败
ReCode = -2:记录已经存在
g) 存储过程中涉及到常量的地方,应该在declare中赋值给一个变量,便于修改。
h) 变量定义时必须添加注释。
i) 有较为复杂的逻辑结构时必须添加注释。
j) 在存储过程参数定义完成后AS之前,需要加入以下代码
--WITH ENCRYPTION
2、 函数(Function)
在数据库中需要用到函数(Function),命名方式为:
FN_****,其中****的规则同存储过程
3、 查询操作
所有“读”操作(查询)通过SQL语句来实现。
对于写查询语句需要注意遵守以下规则:
a) 在.NET中需要拼接字符串时,使用值类型的ToString方法和运用StringBuilder类(其.NET 命名空间是System.Text),通过Append,Remove,Insert等方法直接对字符串进行操作,通过ToString方法返回操作结果。示例如下:
StringBuilder sb = new StringBuilder();
Database.TParams[] t = new Database.TParams[2];
sb.Append("SELECT * FROM RMP_CRQuestion_Info");
sb.Append(" WHERE RMP_CRQuestion_ActiveFlag = @RMP_CRQuestion_ActiveFlag");
sb.Append(" AND RMP_CRQuestion_DeletedFlag = @RMP_CRQuestion_DeletedFlag");
sb.Append(" ORDER BY RMP_CRQuestion_Order");
t[0] = new Database.TParams("@RMP_CRQuestion_ActiveFlag", 1, SqlDbType.Char, Constant.STR_ACTIVE, 1);
t[1] = new Database.TParams("@RMP_CRQuestion_DeletedFlag", 1, SqlDbType.Char, Constant.STR_UNDELETED, 1);
try
{
ds = db.RunSQLReturnRS(sb.ToString(), ref t);
}
catch
{
throw new Exception("DB08040:获取全部变更分析问题产生错误!");
}
b) 避免使用不兼容的数据类型, 例如float和int、char和varchar、binary和varbinary是不兼容的。示例如下:
Select SYS_Role_Name from SYS_Role_Info
Where SYS_Role_DeletedFlag = ‘1’
应改为:
Select SYS_Role_Name from SYS_Role_Info
Where SYS_Role_DeletedFlag = 1
c) 尽量避免在WHERE子句中对字段进行函数或表达式操作,如:
SELECT A1 FROM T1 WHERE F1/2=100
应改为:
SELECT A1 FROM T1 WHERE F1=100*2
SELECT A1 FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
应改为:
SELECT A1 FROM RECORD WHERE CARD_NO LIKE ‘5378%’
SELECT member_number, first_name, last_name FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
应改为:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
即:查询时要尽可能将操作移至等号右边
d) 尽量避免使用!=或<>、IS NULL或IS NOT NULL、IN ,NOT IN等这样的操作符
e) 合理使用EXISTS,NOT EXISTS子句。如下所示:
SELECT SUM(T1.C1)FROM T1 WHERE(
(SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)
应改为:
SELECT SUM(T1.C1) FROM T1WHERE EXISTS(
SELECT * FROM T2 WHERE T2.C2=T1.C2)
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')
可以写成:
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')
SELECT hdr_key FROM hdr_tbl
WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
可以写成:
SELECT a.hdr_key FROM hdr_tbl a---- tbl a 表示tbl用别名a代替
WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)
SELECT hdr_key FROM hdr_tbl
WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)
f) 能够用BETWEEN的就不要用IN
SELECT * FROM T1 WHERE ID IN (10,11,12,13,14)
应改为:
SELECT * FROM T1 WHERE ID BETWEEN 10 AND 14
g) 不允许Select * 这样的写法,要将需要的字段全部写出来
h) 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。
i) 在使用UNION的时候,如果单一的查询语句返回唯一的一组数据时,用UNION ALL代替UNION
j) SQL语句中的表连接数尽量少。在连接合表时,首先连接主键,然后是外键,如果没有主键,那么首先联合外键。在某种情况下,两个表之间可能不只一个的连接条件,这时在WHERE 子句中将连接条件完整的写上
k) 在Where语句中检查表的主键和他们的位置(如果是联合主键的话),按顺序连接表,并把所有的主键放在同一边(左边或右边)
l) 能用DISTINCT的就不用GROUP BY
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
可改为:
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
当一些查询语句中没有建立在主键和外键上时,通知DBA,为这些查询建立特殊索引