Using Table Valued Functions in SSMS

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Using Table Valued Functions in SSMS

Post by robinwilson16 » Fri Mar 03, 2017 12:31 am

In case you have not come across table valued functions before, you can call them like a normal table.

Here is an example:
Table Valued Functions 1.PNG
Table Valued Functions 1.PNG (34.21 KiB) Viewed 2942 times
You can also join to table valued functions but must use CROSS APPLY and OUTER APPLY instead of INNER JOIN and LEFT JOIN.

This example shows how it works when cross applied:
Table Valued Functions 2.PNG
Table Valued Functions 2.PNG (121.38 KiB) Viewed 2942 times
Cross apply is required as the values passed into the function are not available using an INNER JOIN.
Aside from this CROSS APPLY and INNER JOIN are more or less identical and the query optimiser may switch an INNER JOIN to a CROSS APPLY anyway.

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 1 guest