Archive
Are User Defined Functions (UDF) pre-compiled – MSDN TSQL forum
–> 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.
SQL Basics – UDF | User Defined Functions – Scalar, Table Valued (TVF), MultiStatement (MTVF)
UDF or User Defined Functions are a set or batch of code where one can apply any SQL logic and return a single scalar value or a record set.
According to MS BOL UDFs are the subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. These reusable subroutines can be used as:
– In TSQL SELECT statements at column level.
– To create parametrized view or improve the functionality of in indexed view.
– To define a column and CHECK constraints while creating a table.
– To replace a stored procedures and views.
– Join complex logic with a table where a stored procedure fails.
– Faster execution like Stored procedures, reduce compliation cost by caching the execution query plans.
Apart from the benefits UDF’s has certain limitations:
– Can not modify any database objects, limited to update table variables only.
– Can not contain the new OUTPUT clause.
– Can only call extended stored procedures, no other procedures.
– Can not define TRY-CATCH block.
– Some built-in functions are not allowed here, like:GETDATE(), because GETDATE is non-deterministic as its value changes every time it is called. On the other hand DATEADD() is allowed as it is deterministic, because it will return same result when called with same argument values.
A UDF can take 0 or upto 1024 parameters and returns either a scalar value or a table record set depending on its type.
SQL Server supports mainly 3 types of UDFs:
1. Scalar function
2. Inline table-valued function
3. Multistatement table-valued function
1. Scalar function: Returns a single value of any datatype except text, ntext, image, cursor & timestamp.
-- Example: --// Create Scalar UDF [dbo].[ufn_GetContactOrders] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_GetContactOrders](@ContactID int) RETURNS varchar(500) AS BEGIN DECLARE @Orders varchar(500) SELECT @Orders = COALESCE(@Orders + ', ', '') + CAST(SalesOrderID as varchar(10)) FROM Sales.SalesOrderHeader WHERE ContactID = @ContactID RETURN (@Orders) END --// Usage: -- Used at COLUMN level with SELECT SELECT ContactID, dbo.ufn_GetContactOrders(ContactID) FROM Person.Contact WHERE ContactID between 100 and 105 -- Output below -- Used while defining a computed column while creating a table. CREATE TABLE tempCustOrders (CustID int, Orders as (dbo.ufn_GetContactOrders(CustID))) INSERT INTO tempCustOrders (CustID) SELECT ContactID FROM Person.Contact WHERE ContactID between 100 and 105 SELECT * FROM tempCustOrders -- Output below DROP TABLE tempCustOrders
Output of both the selects above:
ContactID OrdersCSV
100 51702, 57021, 63139, 69398
101 47431, 48369, 49528, 50744, 53589, 59017, 65279, 71899
102 43874, 44519, 46989, 48013, 49130, 50274, 51807, 57113, 63162, 69495
103 43691, 44315, 45072, 45811, 46663, 47715, 48787, 49887, 51144, 55310, 61247, 67318
104 43866, 44511, 45295, 46052, 46973, 47998, 49112, 50215, 51723, 57109, 63158, 69420
105 NULL
Note: If this was a temp(#) table then the function also needs to be created in tempdb, cause the temp table belongs to tempdb. The tables in function should also have the database name prefixed, i.e. [AdventureWorks].[Sales].[SalesOrderHeader]
2. Inline table-valued function: Returns a table i.e. a record-set. The function body contains just a single TSQL statement, which results to a record-set and is returned from here.
-- Example: --// Create Inline table-valued UDF [dbo].[ufn_itv_GetContactSales] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_itv_GetContactSales](@ContactID int) RETURNS TABLE AS RETURN ( SELECT h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate], h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID] FROM Sales.SalesOrderHeader AS h JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID JOIN Production.Product AS p ON p.ProductID = d.ProductID WHERE ContactID = @ContactID ) --// Usage: SELECT * FROM ufn_itv_GetContactSales(100)
3. Multistatement table-valued function: Also returns a table (record-set) but can contain multiple TSQL statements or scripts and is defined in BEGIN END block. The final set of rows are then returned from here.
-- Example: --// Create Multistatement table-valued UDF [dbo].[ufn_mtv_GetContactSales] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufn_mtv_GetContactSales](@ContactID int) RETURNS @retSalesInfo TABLE ( [ContactID] INT NOT NULL, [SalesOrderID] INT NULL, [ProductID] INT NULL, [Name] NVARCHAR(50) NULL, [OrderDate] DATETIME NULL, [DueDate] DATETIME NULL, [ShipDate] DATETIME NULL, [TotalDue] MONEY NULL, [Status] TINYINT NULL, [SalesPersonID] INT NULL) AS BEGIN IF @ContactID IS NOT NULL BEGIN INSERT @retSalesInfo SELECT h.[ContactID], h.[SalesOrderID], p.[ProductID], p.[Name], h.[OrderDate], h.[DueDate], h.[ShipDate], h.[TotalDue], h.[Status], h.[SalesPersonID] FROM Sales.SalesOrderHeader AS h JOIN Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID JOIN Production.Product AS p ON p.ProductID = d.ProductID WHERE ContactID = @ContactID END -- Return the recordsets RETURN END --// Usage: SELECT * FROM ufn_mtv_GetContactSales(100)
— Output:
More MSDN & MS BOL links:
http://msdn.microsoft.com/en-us/library/aa175085(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/aa214363(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/ms186755.aspx
http://msdn.microsoft.com/en-us/library/ms191007.aspx
http://msdn.microsoft.com/en-us/magazine/cc164062.aspx