Monday, 24 September 2012

User Defined Functions (UDF) Limitations – Sql Server



UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF cannot do and why Stored Procedure is considered as more flexible then UDFs. Stored Procedure is more flexibility then User Defined Functions (UDF).
  1. UDF has No Access to Structural and Permanent Tables.
    • UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure).

  1. UDF Accepts Lesser Numbers of Input Parameters.
    • UDF can have upto 1023 input parameters; Stored Procedure can have upto 21000 input parameters.

  1. UDF does not Support Error Handling.
    • RAISEERROR or @@ERROR are not allowed in UDFs.

  1. UDF Returns Only One Result Set or Output Parameter.
    • Due to this it can be used in SELECT statement but cannot return multiple result set like Stored Procedure.

  1. UDF cannot Call Stored Procedure.
    • Only access to Extended Stored Procedure.

  1. UDF cannot Execute Dynamic SQL or Temporary Tables.
    • UDF cannot run dynamic SQL which are dynamically built in UDF. Temporary Tables cannot be used in UDF as well.

  1. UDF cannot Return XML.
    • FOR XML is not allowed in UDF.

  1. UDF does not support SET options.
    • SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc.).

    UDF is used for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

    2 comments:

    1. I can able to use @@ERROR, GETDATE() features within the UDF. I have declared 2 variables and assigned the output of @@ERROR and DATEPART(mm,getdate()) to them. For testing purpose i have used them and returned the addition of them for function return. It is working fine. Any thoughts/ comments?

      --Jayaprakash

      ReplyDelete
    2. Pl see the testing i have done for @@ERROR & GETDATE()-fyi

      --UDF Limitations
      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UDFTest]')
      AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
      DROP FUNCTION [dbo].[UDFTest]
      GO
      Create Function UDFTest () RETURNS INT
      AS
      BEGIN
      DECLARE @A INT
      DECLARE @B INT
      DECLARE @NAME VARCHAR(100)

      SELECT TOP 1 @NAME=NAME FROM Employee

      SELECT @A=@@ERROR --ALLOWED , gives 0 here


      SET @B=DATEPART(YY,getdate()) --ALLOWED, gives 2013


      RETURN @A+@B --RESULT 2013
      END
      GO
      --Verify
      SELECT dbo.UDFTest() as ValueFromCallSP --RESULT 2013
      GO

      ReplyDelete