You can create a multi-statement table-valued function (MSTVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.
functions
Create an Inline Table-Valued Function (ITVF) in SQL Server
You can create an inline table-valued function (ITVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.
Delete Data via a Table-Valued Function in SQL Server
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.
Update Data via a Table-Valued Function in SQL Server
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.
Insert Data via a Table-Valued Function in SQL Server
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.
Select Data via a Table-Valued Function in SQL Server
The SELECT statement is probably the most commonly used statement in SQL Server. Most of the time this statement is run against a view or directly against a table to retrieve rows of tabular data.
But views and tables aren’t the only objects you can run a SELECT statement on. The SELECT statement can also be used on other objects such as rowset functions, OPENXML, and user-defined functions.
This article provides an example of selecting data via a table-valued function.
What is a Table-Valued Function in SQL Server?
In SQL Server, a table-valued function (TVF) is a user-defined function that returns a table. This is in contrast to a scalar function, which returns a single value.
You can invoke a table-valued function in the same way that you can query a table. For example, you can use it in a SELECT statement. In some cases, table-valued functions can also be used to update, delete, and insert data.
Return the Local Server Name in SQL Server with @@SERVERNAME
In SQL Server, the @@SERVERNAME configuration function returns the name of the local server that is running SQL Server.
No argument is required. You can simply use it in a SELECT statement to return the server name.
Create a Computed Column that Uses Data from Another Table in SQL Server
A widely known limitation of computed columns in SQL Server is that they can’t access data from other tables. That is, your expression can use columns in the same table, but not from other tables.
But this is only half-true. While you can’t reference another table’s column directly within your expression, you can invoke a user-defined function. And therefore, you could create a user-defined function that performs the calculation you need, then simply call that function as your computed column’s expression.
Here’s an example to demonstrate.
How @@MAX_CONNECTIONS Works in SQL Server
In SQL Server, the @@MAX_CONNECTIONS configuration function returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.
No argument is required. You can simply use it in a SELECT statement to return the maximum number of simultaneous user connections allowed on the current server.