T-SQL使用经验汇总

表字段

1)     字段值在允许为空的情况下，尽量采用默认值

 

2)     日期类型的数据字段，尽量采用整型类型（unix timespan），其次采用datetime类型，不要使用字符串类型来存放，如：nvarchar，varchar

 

3)     数值类型的数据字段，应使用sqlserver中对应的数值类型来存放，不要使用字符串类型来存放，如：nvarchar，varchar

 

4)     字段索引不要建太多，应根据数据表承载业务数据的实际情况进行创建索引

 

Select单表或多表查询

1)     内联表位置的摆放遵循“左大右小”原则

 

2)     Select 后字段名称尽可能显式地指定，尽可能少用*号代替

 

3)     Select 字段中不要使用子查询，可寻求表关联的方式代替

 

Select或Update条件

1)     尽量少用函数进行条件筛选，如：substring、charindex、isdate等

 

2)     Where条件中的字段尽可能是索引（但不是索引越多越好）

 

3)     Where条件中各条件的位置优先级排列标准：

       函数表达式条件-> 字段比较条件 -> 字符串常量条件-> 数值常量条件

 

4)     少用in操作，采用exists操作代替

 

5)     多表关联情况下Where中条件可放在On 关联中

 

6)     Where如果有多个or操作可考虑用union all 代替

 

7)     Like条件尽量使用半模糊，如： like ‘xxx%’

 

8)     Order by 排序字段中少用函数表达式或字段

 

9)     需要在Where 条件中进行动态判断操作时，可以采类似如下写法：(‘’= @id orid=@id) 

复杂t-sql

1)     多个查询结果合并union all 优于union

2)     如果有多重子查询结果行关联查询，采用with 公用表达式

3)     Groupby 优于Distinct 关键字

4)     多重子查询嵌套，尽量将筛选条件放在里内层的子查询中

5)     Groupby 分组查询条件尽量在where中，少放在having中进行筛选，数据量超大或查询复杂度很高的情况下少用sqlserver2005版自带的partitionby等语法

6)     Groupby 分组合并统计的字段不要存在null值，对于sum求和要先isnull转换过滤

7)     在store procedure 或function中少用字符串拼装方式生成sql脚本，sql脚本条件多采用参数传入的方式，如：where id=@id ，外部应用通过数据连接请求的方式提交到sqlserver内部执行的sql脚本也应多采用参数传入的方式，如：where id=@id

8)     在包含有频繁（或并发）业务逻辑的store procedure 或 function中少用cursor操作，少用#临时表操作。对于cursor代替方案可寻求update的多表内联更新方式，对于#临时表代替方案可寻求视图或with公用表达式的方式

9)     要编写动态order by 字段时，可采用casewhen进行判断，减少拼装字符串操作

10) 表值函数内联查询优于表之间的关联查询，如

      Select a.id, b.name from table1 as a OUTER APPLY fun(a.id) b

Sqlserver优化与管理

 

执行计划

1)     系统表sys.dm_exec_cached_plans存放sqlserver执行计划缓存

 

2)     系统表函数sys.dm_exec_sql_text 通过对执行计划句柄的参数可以得到具体是哪个执行计划的名称

 

通常结合的使用示例:

 

SELECT usecounts , p.size_in_bytes , [sql].[text]

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql

 

各字段意义和使用规则可参考sqlserver 自定的BookOnline帮助

 

DBCC命令

3)     DBCC FREEPROCCACHE 清除当前执行计划缓存

 

4)     DBCC FREESESSIONCACHE  清除当前会话状态缓存

 

5)     DBCC FREESYSTEMCACHE('All')  清除所有缓存

 

6)     DBCC DROPCLEANBUFFERS  从缓冲池中删除所有缓冲区

具体使用参考microsoft 官网的msdn