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.
Category: SQL Server
Create a Table-Valued Function in SQL Server
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).
Create a Multi-Statement Table-Valued Function (MSTVF) in SQL Server
You can create a multi-statement table-valued function (MSTVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.
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.