none
Are User Defined Functions (UDF) pre-compiled? RRS feed

  • Question

  • I have seen several posts saying that a UDF is not pre-compiled like a stored procedure. But this page http://msdn.microsoft.com/en-us/library/6z6w48f5.aspx says both are . Could any one let me know which one is correct and what is meant by this?
    Wednesday, June 15, 2011 8:04 AM

Answers

  • Yes it is.. If you see performance problem that meas  your UDF access every row in the table and a workaround is to rewrite it

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[TsqlSplit]

    (@List As varchar(8000))

    RETURNS @Items table (Item varchar(8000) Not Null)

    AS

    BEGIN

     

      DECLARE @Item As varchar(8000), @Pos As int

      WHILE DATALENGTH(@List)>0

      BEGIN

        SET @Pos=CHARINDEX(',',@List)

        IF @Pos=0 SET @Pos=DATALENGTH(@List)+1

        SET @Item =  LTRIM(RTRIM(LEFT(@List,@Pos-1)))

        IF @Item<>'' INSERT INTO @Items SELECT @Item

        SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)

      END

      RETURN

    END

     

     

    SELECT * FROM [TsqlSplit] ('1,2,3')

     

     

    SELECT objtype, cacheobjtype, usecounts, text 

    FROM sys.dm_exec_cached_plans AS ECP

      CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST

    WHERE EST.text LIKE '%TsqlSplit%'

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 15, 2011 8:17 AM
    Answerer
  • Hi arun_arunarun,

    As to the difference between Stored Procedures and User Defined Functions, please take a look at threads below:
    Difference Between User Defined Function and Stored Procedure and Difference between procedure and function ?

    Best Regards,
    Stephanie Lv


    Saturday, June 18, 2011 5:09 AM

All replies

  • Yes it is.. If you see performance problem that meas  your UDF access every row in the table and a workaround is to rewrite it

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[TsqlSplit]

    (@List As varchar(8000))

    RETURNS @Items table (Item varchar(8000) Not Null)

    AS

    BEGIN

     

      DECLARE @Item As varchar(8000), @Pos As int

      WHILE DATALENGTH(@List)>0

      BEGIN

        SET @Pos=CHARINDEX(',',@List)

        IF @Pos=0 SET @Pos=DATALENGTH(@List)+1

        SET @Item =  LTRIM(RTRIM(LEFT(@List,@Pos-1)))

        IF @Item<>'' INSERT INTO @Items SELECT @Item

        SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)

      END

      RETURN

    END

     

     

    SELECT * FROM [TsqlSplit] ('1,2,3')

     

     

    SELECT objtype, cacheobjtype, usecounts, text 

    FROM sys.dm_exec_cached_plans AS ECP

      CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST

    WHERE EST.text LIKE '%TsqlSplit%'

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 15, 2011 8:17 AM
    Answerer
  • So why this common misconception. Is there any difference between them related to pre-compilation
    Wednesday, June 15, 2011 8:23 AM
  • Between whom? Stored procedures and UDF?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, June 15, 2011 8:34 AM
    Answerer
  • Hi arun_arunarun,

    As to the difference between Stored Procedures and User Defined Functions, please take a look at threads below:
    Difference Between User Defined Function and Stored Procedure and Difference between procedure and function ?

    Best Regards,
    Stephanie Lv


    Saturday, June 18, 2011 5:09 AM
  • UDF are not pre-compiled, UDFs similar to SPs are only compiled on their first execution. They are just parsed and created in SQL catalog.

    Try to execute the DMV query before the SQL query where the function TsqlSplit() is called. It won't give to you any cache'd record. But after you execute the SQL query and then execute the DMV query then you will see a cache'd record for Function execution.

    Check this:

    dbcc freeproccache
    go
    
    SET ANSI_NULLS ON 
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
    
    CREATE FUNCTION [dbo].[TsqlSplit] (@List As varchar(8000)) 
    RETURNS @Items table ( 
      Item varchar(8000) Not Null) 
    AS 
      BEGIN 
          DECLARE @Item As varchar(8000), 
                  @Pos  As int 
    
          WHILE DATALENGTH(@List) > 0 
            BEGIN 
                SET @Pos=CHARINDEX(',', @List) 
    
                IF @Pos = 0 
                  SET @Pos=DATALENGTH(@List) + 1 
    
                SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))) 
    
                IF @Item <> '' 
                  INSERT INTO @Items 
                  SELECT @Item 
    
                SET @List=SUBSTRING(@List, @Pos + DATALENGTH(','), 8000) 
            END 
    
          RETURN 
      END 
    GO 
    
    SELECT objtype, 
           cacheobjtype, 
           usecounts, 
           text 
    FROM   sys.dm_exec_cached_plans AS ECP 
           CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST 
    WHERE  EST.text LIKE '%TsqlSplit%' 
    
    GO 
    
    SELECT * 
    FROM   [TsqlSplit] ('1,2,3') 
    GO 
    
    SELECT objtype, 
           cacheobjtype, 
           usecounts, 
           text 
    FROM   sys.dm_exec_cached_plans AS ECP 
           CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST 
    WHERE  EST.text LIKE '%TsqlSplit%' 
    


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Wednesday, December 11, 2013 6:23 AM