SQL开发效率注意事项

1．所有的 select建议加nolock,更新语句加rowlock

select columeName from tableName with(nolock) join  tablename2 T2 with(nolock)

2．应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null ,可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

3．应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

    select id from t where num=10 or num=20

    可以这样查询：

    select id from t where num=10

    union all

select id from t where num=20

   

4．in 和 not in 也要慎用，否则会导致全表扫描，如：

    select id from t where num in(1,2,3)

    对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

 

5．下面的查询也将导致全表扫描：

    select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

 

6．应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

       6.1. Select * FROM RECORD WhereSUBSTRING(CARD_NO,1,4)=’5378’

           应改为:Select * FROM RECORD Where CARD_NO LIKE ‘5378%’

       6.2. Select member_number, first_name,last_name FROM members Where     

           DATEDIFF(yy,datofbirth,GETDATE())> 21

           应改为:Select member_number, first_name, last_name FROMmembers 

       Where dateofbirth <DATEADD(yy,-21,GETDATE())

     即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

 

7．应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

   select id from t wheresubstring(name,1,3)='abc'--name以abc开头的id

   select id from t wheredatediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id

   应改为:

   select id from t where name like'abc%'

   select id from t wherecreatedate>='2005-11-30' and createdate<'2005-12-1'

 

8．很多时候用 exists 代替 in 是一个好的选择：

   select num from a where numin(select num from b)

       用下面的语句替换：

   select num from a whereexists(select 1 from b where num=a.num)

 

9．任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

10．尽量使用表变量来代替临时表,但是当数据量比较大的时候，表变量的效率没有临时变量高。

11．避免频繁创建和删除临时表，以减少系统表资源的消耗。

12．临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

13．在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，以提高速度；如果数据量不大，为了缓和系统资源，应先create table，然后insert, 但是在存储过程中，如果中间数据集比较大，尽量少用临时变量

14．如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

15．尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

16．充分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在 Where 子句中将连接条件完整的写上，有可能大大提高查询速度。

   1). SelectSUM(A.AMOUNT) FROM ACCOUNT A,CARD B Where A.CARD_NO = B.CARD_NO 

   2). SelectSUM(A.AMOUNT) FROM ACCOUNT A,CARD B Where A.CARD_NO = B.CARD_NO ANDA.ACCOUNT_NO=B.ACCOUNT_NO

第二句将比第一句执行快得多。

 

17．能用DISTINCT的就不用GROUP BY

18．能用UNION ALL就不要用UNION

19．尽量全部用存储过程