what is it the difference between TVF/UDF

What is it the开发者_如何学Go difference between TVF/UDF, in a DBMS context?

A UDF is any function that was not supplied by the DBMS. It stands for User-Defined Function.

A TVF is a specific type of UDF, namely a Table-Valued Function. It returns a multi-row result (a derived table). In SQL Server for example, a TVF can be in one of two forms, a multi-statement or an inline TVF.

UDFs can be implemented in the procedural language, such as PL/SQL or TSQL, but some DBMS also support compiled/extension UDF, such as Java for Oracle and CLR for SQL Server.

A table-valued function is a type of UDF. In SQL Server, there are two kinds of table-valued functions (inline and multi-statement) and a scalar udf.

Inline TVFs are really closer to parametrized views in terms of their restrictions and their manipulations by the engine and optimizer.

There are two types in UDF: SVF(Scalar Valued Function) and TVF(Table Valued Function). The former returns UDF of value type and the latter returns table. It is common to compare UDF and Store Producer. In fact, they are both useful. UDF can only return one RowSet, while Store Producer can return more. UDF cannot support CUD operation while, Store Producer can. UDF supports inline query in SQL, while Sprocs not.