Total Page Preview: 000000004350
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
There is three type of user define function in sql server:
1. Scalar
Deterministic UDF: UDF in which particular input results in particular output. In other words, the output depends on the input.
Non-deterministic UDF: UDF in which the output does not directly depend on the input.
2. In-line UDF:
UDFs that do not have any function body(BEGIN...END) and has only a RETURN statement. In-line UDF must return 2D data.
3. Multi-line or Table Valued Functions:
It is an UDF that has its own function body (BEGIN ... END) and can have multiple SQL
statements that return a single output. Also must return 2D data in the form of table variable.
Difference between a nested UDF and recursive UDF:
-
Nested UDF: calling an UDF within an UDF
-
Recursive UDF: calling an UDF within itself
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com