Limitation of UDF

Introduction

All the developers are very well-known about the UDF in MS SQL server. The UDF is first introducing at Microsoft SQL Server 2000.

Here in this article we are trying to discuss about some limitation of UDF.

No Side-Effects

By definition, a UDF is supposed to have no side-effects on the database. 

In MS SQL Server user defined functions (UDFs) you cannot take any actions (update, delete, insert) that modify data on any object outside the scope of the UDF.  A table variable inside the UDF is, of course, allowed. 

Let’s take an Example:

IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL

   BEGIN

      DROP FUNCTION [dbo].[fn_MYUDF];

   END

GO

CREATE FUNCTION [dbo].[fn_MYUDF]

       (

         @p_EMPID   INT

       ) 

RETURNS INT

AS

BEGIN

   DECLARE @intRETURN INT = 1;

  

   UPDATE dbo.tbl_Employee

     SET EMPNAME='Joydeep Das'

   WHERE EMPID=@p_EMPID;

  

   RETURN @intRETURN; 

END  

When we compile this we get an error output.

Msg 443, Level 16, State 15, Procedure fn_MYUDF, Line 10

Invalid use of a side-effecting operator 'UPDATE' within a function.

Can NOT use Non-deterministic Built-in function

Within UDF we cannot use any non-deterministic built in function. Non deterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. We cannot influence the determinism of any built-in function. Each built-in function is deterministic or non deterministic based on how the function is implemented by SQL Server.

SQL Server 2005 has allowed some of the non-deterministic functions that were not allowed in 2000. In 2005 we can now use CURRENT_TIMESTAMP, @@MAX_CONNECTIONS, GET_TRANSMISSION_STATUS, @@PACK_RECEIVED, GETDATE, @@PACK_SENT,
GETUTCDATE, @@PACKET_ERRORS, @@CONNECTIONS, @@TIMETICKS, @@CPU_BUSY, @@TOTAL_ERRORS, @@DBTS, @@TOTAL_READ, @@IDLE,
@@TOTAL_WRITE and,  @@IO_BUSY

Limitation of Parameters

UDF can have up to 1023 input parameters; Stored Procedure can have up to 21000 input parameters. This is a relatively minor limitation because most routines require a much smaller number of parameters.

Limitation of CURSOR Declaration

IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL

   BEGIN

      DROP FUNCTION [dbo].[fn_MYUDF];

   END

GO

CREATE FUNCTION [dbo].[fn_MYUDF]

       (

         @p_OPMODE   INT

       ) 

RETURNS INT

AS

BEGIN

   DECLARE @intRETURN INT;

  

   DECLARE cur_test SCROLL CURSOR

        FOR SELECT o.id FROM sysobjects as o;

       

   RETURN @intRETURN; 

END 

Msg 1049, Level 15, State 1, Procedure fn_MYUDF, Line 0

Mixing old and new syntax to specify cursor options is not allowed.

In UDF we need ANSI SQL style to declare cursor.

In TSQL, user-defined functions cannot modify state of the database or persist information between executions. This is one of the reasons to disallow insert/update/delete on permanent tables or dynamic SQL.

This also means that UDFs will disallow cursors that are global in nature which happens to be the case with those declared using the ANSI SQL syntax. Note that there is no way to specify cursor as local using the ANSI SQL syntax. So we default all cursors declared within UDFs to be local implicitly. This creates a conflict in case of the DECLARE CURSOR statement using ANSI SQL syntax since that doesn't support the local option. Hence the error message about mixing old and new syntax for cursor options. The workaround is to use TSQL syntax for the DECLARE CURSOR statement to specify LOCAL or leave it out in which case we default to LOCAL anyway.

References: http://blogs.msdn.com/b/sqltips/archive/2005/06/21/431358.aspx

IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL

   BEGIN

      DROP FUNCTION [dbo].[fn_MYUDF];

   END

GO

CREATE FUNCTION [dbo].[fn_MYUDF]

       (

         @p_OPMODE   INT

       ) 

RETURNS INT

AS

BEGIN

   DECLARE @intRETURN INT;

  

   DECLARE cur_test CURSOR SCROLL

        FOR SELECT o.id FROM sysobjects as o;

       

   RETURN @intRETURN; 

END 

OR

IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL

   BEGIN

      DROP FUNCTION [dbo].[fn_MYUDF];

   END

GO

CREATE FUNCTION [dbo].[fn_MYUDF]

       (

         @p_OPMODE   INT

       ) 

RETURNS INT

AS

BEGIN

   DECLARE @intRETURN INT;

  

   DECLARE cur_test  CURSOR LOCAL SCROLL

        FOR SELECT o.id FROM sysobjects as o;

       

   RETURN @intRETURN; 

END   

Return Only One Result Set

UDFs can return only one row set to the user, whereas stored procedures can return multiple row sets.

UDF Can NOT Call Regular Stored Procedure

We can't call regular stored Procedure from functions - only other functions or some extended stored procedures.

-- Procedure

IF OBJECT_ID(N'dbo.proc_MYPROC', N'P')IS NOT NULL

   BEGIN

      DROP PROCEDURE [dbo].[proc_MYPROC];

   END

GO

CREATE PROCEDURE [dbo].[proc_MYPROC]

     (

        @p_EMPID   INT = 0

     )

 AS

 BEGIN

    SELECT *

    FROM   tbl_Employee

    WHERE  EMPID = @p_EMPID;

 END      

GO

--Function

IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL

   BEGIN

      DROP FUNCTION [dbo].[fn_MYUDF];

   END

GO

CREATE FUNCTION [dbo].[fn_MYUDF]

       (

         @p_ID   INT

       ) 

RETURNS INT

AS

BEGIN

   DECLARE @intRETURN INT;

  

   EXEC [dbo].[proc_MYPROC]

      @p_EMPID = @p_ID

       

   RETURN @intRETURN; 

END   

--Function Execution

SELECT  dbo.fn_MYUDF (1)

Msg 557, Level 16, State 2, Line 1

Only functions and some extended stored procedures

can be executed from within a function.

Can NOT Execute Dynamic SQL

UDFs also cannot execute dynamically constructed SQL statements. If we need to construct a statement dynamically based on the parameter values, we must resort to using stored procedures.

Can NOT Support SET Operation

SET options can affect not only the performance of the queries, but their output as well.

SET options cannot be altered within UDFs.

IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL

   BEGIN

      DROP FUNCTION [dbo].[fn_MYUDF];

   END

GO

CREATE FUNCTION [dbo].[fn_MYUDF]

       (

         @p_ID   INT

       ) 

RETURNS INT

AS

BEGIN

   DECLARE @intRETURN INT;

  

   SET NOCOUNT ON;

       

   RETURN @intRETURN; 

END 

Msg 443, Level 16, State 15, Procedure fn_MYUDF, Line 10

Invalid use of a side-effecting operator 'SET OPTION ON' within a function.

Error Checking Limitation

RAISERROR, PRINT statement is not allowed by UDF. Even @@ERROR global variable is not supported by UDF. If you encounter an error, UDF execution simply stops, and the calling routine fails. We are allowed to write a message to the Windows error log with xp_logevent if you have permission to use this extended procedure.

Hope you like it

Posted by: MR. JOYDEEP DAS