SQL Server 2008之用户自定义函数

wangluo010

• 标量函数，当使用T-SQL实现时不能返回rowversion、cursor、table，当使用托管代码实现时，不能返回rowversion、cursor、table、text、ntext、image
  

• 创建标量函数时，CREATE FUNCTION必须是批处理中唯一语句；和存储过程不同，使用函数时，必须使用BEGIN...END抱住函数体，存储过程中BEGIN...END是可选的
  

• 创建用户自定以的标量函数的Guidelines：
  

• 函数使用两部分命名法，函数中引用的数据库对象也使用两部分命名法
  

• 在函数中，错误将导致整个函数停止执行，而存储过程或者触发器则是只取消产生错误的语句执行，然后继续执行接下来的语句
  

• 函数修改底层数据库被认为是有副作用的，在SQL Server中函数不允许有副作用，即不允许修改底层数据库，因此你可能无法修改数据库中的数据，无法执行存储过程，无法执行动态SQL（能不能执行主要看是否修改了底层数据库）
  

• 函数分为Deterministic，即在相同的数据库状态下提供同样的输入总是返回相同的值和Non-deterministic，即返回不同的值，可以使用OBJECTPROPERTY()函数确定函数是不是deterministic的
  

• 表值函数通常和参数化视图是等价的，但是视图是不允许传递用户自定义的参数的；对于内联函数来说，不用将函数体包含在BEGIN...END语句块中，返回类型为TABLE，返回的表结构从SELECT语句进行推导
  

• 多语句表值函数允许更加复杂的返回表构建逻辑，函数体必须包含在BEGIN...END语句中，必须提供返回表的定义
  

• 视图代码直接纳入到查询代码中（也就是将定义视图的代码和查询代码组合成一个查询的代码，而不是先执行视图查询，再对返回数据执行查询），而标量函数则不是这样的；在SELECT列表和WHERE语句中使用标量函数将带来严重的性能问题
  

• 内联表值函数将直接将代码纳入使用他们的查询中，而多语句表值函数则不会这么做，除非多语句表值函数在查询中只执行一次，否则其性能会很差；CROSS APPLY操作符用来为左边表中每一行执行表值函数，这会带来严重的性能问题，尽量避免
  

• 上述两点中指出的性能问题，都是逐行调用了自定义函数，这样需要为每行去提取自定义函数的定义，然后去执行这些定义，导致了性能问题；更深层次的原因是因为函数采用了过程式的处理方法，而SQL Server查询数据则是基于数据集合的，这样在采用过程式的逐行处理时，SQL Server性能就会显著降低
  

• 创建函数的指导原则：
  

• 决定使用的函数类型
  

• 为每个任务创建一个函数，避免创建大函数，完成多任务的函数
  

• 使用两部分命名法
  

• 考虑使用函数时的性能影响，通常来说，内联函数比多语句函数性能要好
  

• 考虑和索引组合使用时的影响，对索引列使用函数很可能移除索引列的应用
  

• 避免引发错误，在函数中不允许错误处理
  

• 表值函数和存储过程都能实现相似的结果，一些应用程序只能使用表值函数，另外一些只能使用存储过程
  
  
  • 函数
    
  
  
  
  
  • 返回结果更容易访问，存储过程得使用输出参数，使用起来更复杂
    
  
  
  
  
  • 可以将表数据返回给一个变量
    
  
  
  
  
  • 不能有数据相关的副作用，即不能修改数据，不能自行动态SQL语句
    
  
  
  
  
  • 多语句表值函数通常有性能问题
    
  
  
  • 存储过程
    
  
  
  
  
  • 可以修改数据
    
  
  
  
  
  • 可以执行动态语句
    
  
  
  
  
  • 可以包含详细的异常处理
    
  
  
  
  
  • 可以返回多结果集
    
  
  

• 表值函数和视图通常都可以实现相似的结果
  
  
  • 视图
    
  
  
  
  
  • 能够被几乎所有应用程序使用
    
  • 和表非常相似
    
  
  
  
  
  • 可以被更新
    
  • 可以使用INSTEAD OF触发器
    
  
  
  • 表值函数
    
  
  
  
  
  • 类似于参数化视图
    
  
  
  
  
  • 通常导致严重的性能问题（多语句表值函数）
    
  
  
  
  
  • 不能纳入到使用的查询中
    
  
  
  
  
  • 只有内联表值函数可以更新