Overview: Functions are powerful database objects in SQL allowing users to pass input parameters and receive a calculated output of some kind. There are many Scalar-Valued Functions (such as COUNT(), or Microsoft's t-SQL function GETDATE()), but for functions that output a table, there are two options:
- Table-Valued Function
- Function Table
Table-Valued Functions are passed parameters through parenthesis following the function name and are CROSS APPLY'ed to the initial table in order to output a table itself:
Function Tables are functions that appear to be normal tables. They are JOIN'ed to the query and use WHERE clauses to pass the necessary parameters:
You can see that both queries provide the same parameters, but in different ways. Function Tables allow the function to mask itself as a table if the application does not allow them.
Note: You can further prove that a function table is not a real table by trying to SELECT * from it:
The error message shows that it tried to take the parameters from the query and pass them to the Table-Valued Function behind the scenes, but was unable to since the WHERE clauses did not provide the necessary information.
When to Use Each One: The full scope of the pros and cons of each type are beyond the scope of this course, especially since this is more of a SQL question and less of a PI SQL question. Regardless of which option is chosen for a given query, the PI SQL query engines provide customers with the opportunity to use either one.
One scenario in specific, however, that leads many customers to use function tables over table-valued functions is when an application cannot execute table-valued functions. For example, Microsoft SQL Server Linked Servers do not allow table-valued functions and will throw this error message for any query that contains them:
Remote table-valued function calls are not allowed
The RTQP data model by default does not contain any function tables. For example, the Object Explorer for a default RTQP installation shows six table-valued functions but no function tables for the Master.Element schema:
Function Tables are very simple to create in an ad-hoc fashion, either through scripting or manually creating them.
- To manually create them, right click on the Table-Valued Function and select "Create Function Table"
Provide a name, and click OK.
Note: It's good practice to name function tables starting with "ft_" to signify to any query writers that this is a function table and not a normal table.
In the object explorer, we can expand the columns of the original Table-Valued Function as well as the newly created Function Table and confirm that they have the same inputs (blue) and outputs (green):
- The Function Tables can also be created in a scripted manner. Still in the Object Explorer, we can right click on the Function Tables folder and select Scripts > Create Function Table > New Query Editor Window:
This provides us with a template to create the function table from an existing table value function. Note that the command is "CREATE TABLE", since this is what a function table is presenting itself as:
We can return to the Object Explorer to see a list of the datatypes for each input parameter, and use that to fill in the template:
Then click execute when we're done to create the function. We can confirm once again in Object Explorer that both methods of creating the Function Table produced the same output:
PI OLEDB Enterprise:
The PI OLEDB Enterprise data model, on the other hand, comes with a corresponding Function Table for every Table-Valued Function, as seen in the Object Explorer:
Expanding out the columns confirms that the associated function and table have the same inputs (blue) and outputs (green):
For any new user-created transpose function, a table-valued function will be created, but the option to also create a function table is present in the wizard's "Options" menu:
Once created, for the NuGreen boiler snapshot, for example, we can see both database objects in the Object Explorer:
And expanding the columns of each will prove the same inputs (blue) and outputs (green) exist for both: