TVF full name is User-defined functions that return a table data. It is also alternative of the view. While views are limited to a single SELECT statement,TVF can contain additional statements that allow more powerful logic than is possible in views.
Create View sample Code
Table Value Function
Lets query the TVF
TVF return result is just like a table. We can use APPLY to join it. APPLY is similar to join. There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSS APPLY is more like the inner join. OUTER APPLY is more like the outer join.
To simplify the testing, I change the TVF as below
Then run the below query
When Query engine execute the query, it take the ProductID from Product table and retrieve the data from TVF , then combine with the Product table.
If TVF does not return data, then CROSS APPLY wont show the dataset from the Product table. When we replace the CROSS APPLY with OUTER APPLY, even the TVF does not return data, It will still show the result set.
Confirm there is no sales for the Product ID 1
Lets change it to the OUTER APPLY
As you can see the Product ID 1 does not show in the result set when we use CROSS APPLY but shows in the result set when we use OUTER APPLY.
How to resolve CREATE FUNCTION failed because a column name is not specified for column 2.
If any of the column in the User defined function does not have explicit name, you will get the below error message
Msg 4514, Level 16, State 1, Procedure ufnGetSalesCount2, Line 5
CREATE FUNCTION failed because a column name is not specified for column 2.
In the example above, If we do not provide the alias for the COUNT(ProductID), then we will get that error.