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.
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.
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.
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.
In SQL Server, you can use the sys.columns system catalog view to return a list of non-computed columns from a table.
By “non-computed”, I simply mean columns that are not computed columns.
When you create a computed column in SQL Server, the expression you use for the column will either be deterministic or nondeterministic. This can have implications, such as whether or not you can use it in an index or flag it as “persisted”.
A deterministic column is one that will return the same value for a specific set of input values and given the same state of the database. A nondeterministic column can return a different value even when given the same input even if the database state remains the same. For example, a function that returns the current date is nondeterministic, because it will return a different value each day.
You can use the COLUMNPROPERTY() function with the IsDeterministic argument to find out whether or not a computed column is deterministic.
When you create a computed column in SQL Server, you have the option of flagging it as “persisted”. A persisted computed column is one that is physically stored in the table. If you don’t specify that it’s persisted, then the column’s value will be calculated each time you run a query against it.
You can query the sys.computed_columns system catalog view to find out whether a computed column is marked as persisted.
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.
Continue readingIn SQL Server, you can use T-SQL to get the definition for a computed column by querying the sys.computed_columns system catalog view.
In SQL Server, there are a couple of system catalog views that enable you to get a list of computed columns in a database.
One of these views is called sys.computed_columns. The other is sys.columns.