Total Page Preview:   000000004342

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 :





Facebook User: