Just another day I received following question and I find it very interesting. I decided to try it out on SQL Server 2016’s new WideWorldImporters database. The question was – “How to Join a Table Valued Function with a Database Table?” This is indeed very interesting as this particular feature was introduced in SQL Server 2008, so what you will see in this blog post applies to every version of SQL Server after SQL Server 2008.
In database WideWorldImporters we have a table valued function – [Application].[DetermineCustomerAccess], which accepts city id as an input and returns us result if that particular city have accessed enabled or not. We will join this to Sales.Customers table which also have a column city. Now we want to know if the customer city has access enabled or not. To find out we have to join a table valued function with our customer table. Here is the syntax to join table valued function to a table. We will apply CROSS APPLY to connect function and table.
USE WideWorldImporters GO SELECT c.CustomerName, a.AccessResult FROM Sales.Customers c CROSS APPLY [Application].[DetermineCustomerAccess] (DeliveryCityID) a GO
Let me know if you have any question in the comment area.
Reference: Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
We can do normal inner join table value function. CROSS APPLY is where we don’t have any join condition for example, we have table1 and table2. table1 has a column called rowcount. For each row from table1 we need to select first rowcount rows from table2, ordered by table2.id
Hi Pinal. I suscribed your blog from long time ago and I’m curious about this post because this is the first time I see a function like this CROSS APPLY [Application].[DetermineCustomerAccess] (DeliveryCityID) on a SQL query. Is this a property of sql server databases or can be found in another databases like mysql/oracle?
Regards and thanks for your article. Sorry if my question is silly.
Hi,
You can’t use the normal inner join if you had to pass the parameter to the function from the first table.
Hi Pinal, Can you please confirm this?
I am not able to understand this question.
Hi Pinal,
I’ve been playing around with CROSS APPLY and experienced performance issues. When I rewrote my code using a SP with temporary tables, it got much quicker – like 90 seconds with CROSS APPLY to 3 seconds with the SP. Are functions generally slower than procedures?
Regards
Nils
Yes my experience is that functions are generally slower than procedures.
For some reason scaler functions are a lot slower than table functions, at least in SQL Server 2012
That’s because the CROSS APPLY runs once for each row
Cross and Outer apply used with table valued function. Its behave like Inner and outer join respectively. Below is query example to make it clear.
IF(OBJECT_ID(‘dbo.emp’)) IS NOT NULL
DROP TABLE emp
create table emp
(
EmpID int,
EmpName varchar(20),
DepID int
)
INSERT INTO emp
SELECT 1,’Rikhil’,1
UNION ALL
SELECT 2,’Mehul’,2
UNION ALL
SELECT 3,’Neeraj’,NULL
GO
IF(OBJECT_ID(‘dbo.Dep’)) IS NOT NULL
DROP TABLE Dep
create table Dep
(
DepID int,
DepName varchar(20)
)
INSERT INTO Dep
SELECT 1,’IT’
UNION ALL
SELECT 2,’Mechanical’
UNION ALL
SELECT 3,’Electrical’
GO
IF(OBJECT_ID(‘dbo.GetEmpDep’)) IS NOT NULL
DROP FUNCTION GetEmpDep
GO
CREATE FUNCTION GetEmpDep(@DepID int)
RETURNS TABLE
AS
RETURN
select DepID ,
DepName
from Dep
WHERE DepID=@DepID
GO
select ‘CROSS APPLY behave Like Inner join’ AS Title,*
from emp
CROSS APPLY GetEmpDep(DepID)
select ‘OUTER APPLY behave Like Outer join’ AS Title,*
from emp
OUTER APPLY GetEmpDep(DepID)
Great example.
Thank you. :)
thank you
Hi , Pinal,
i want to first value , middle value and last value in datetime column, im getting first and last by using min n max function, but im not getting middle value ie in between 12-1 pm
im using it for attendance project if middle punch is not there then it should be null but
pleaes do needful