When you create a table-valued function (TVF) in SQL Server, you can either make it an inline table-valued function (ITVF) or a multi-statement table-valued function (MSTVF). There are differences between these function types, and they use a different syntax accordingly.
This article covers the difference between MSTVFs and ITVFs.
In SQL Server, the multi-statement table-valued function is one of two types of Transact-SQL table-valued functions (the other type being the inline table-valued function).
Table-valued functions (TVF) are a type of user-defined function that return their results as a table. They can therefore be queried just like a normal table.
In SQL Server, the inline table-valued function is one of two types of Transact-SQL table-valued functions (the other type being the multi-statement table-valued function).
Table-valued functions (TVF) are a type of user-defined function that return their results as a table. They can therefore be queried just like a normal table.
You can alter a table-valued function (TVF) in SQL Server using the ALTER FUNCTION T-SQL syntax.
You simply follow ALTER FUNCTION with the new definition. Basically it’s like using CREATE FUNCTION except you replace CREATE with ALTER.
You can create a table-valued function (TVF) in SQL Server using the CREATE FUNCTION T-SQL syntax.
The syntax is slightly different depending on whether you’re creating an inline table-valued function (ITVF) or a multi-statement table-valued function (MSTVF).
You can create a multi-statement table-valued function (MSTVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.
You can create an inline table-valued function (ITVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.
In SQL Server, it’s possible to use a table-valued function (TVF) to delete data from the underlying tables that the TVF queries.
To delete table data via a TVF, simply use the same T-SQL DELETE syntax that you’d use if deleting a row from the table directly.
In SQL Server, it’s possible to update data via a table-valued function.
What I mean is, you can update data in the underlying tables that the function queries.
For example, if your function returns someone’s first name from a table, you can update their first name by running an UPDATE statement against the function instead of the table.
Note that this only works on inline table-valued functions (ITVFs). As far as I’m aware, it won’t work on multi-statement table-valued functions (MSTVFs).
Also, the columns you update will need to be valid columns in the function’s query.
In SQL Server, it’s possible to insert data via a table-valued function (TVF).
By this, I mean insert rows in the underlying tables that the function queries.
To insert a new row via a TVF, simply use the same T-SQL INSERT syntax that you’d use if inserting data directly into the table.