SQL with Manoj

–> Question:

I have seen several posts saying that a UDF is not pre-compiled like a stored procedure.

But this MSDN Page it says both are.

Could any one let me know which one is correct and what is meant by this?
 

–> My Answer:

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%' 

Ref Link.