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)
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.
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.
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?
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
INSERT INTO emp
IF(OBJECT_ID(‘dbo.Dep’)) IS NOT NULL
DROP TABLE Dep
create table Dep
INSERT INTO Dep
IF(OBJECT_ID(‘dbo.GetEmpDep’)) IS NOT NULL
DROP FUNCTION GetEmpDep
CREATE FUNCTION GetEmpDep(@DepID int)
select DepID ,
select ‘CROSS APPLY behave Like Inner join’ AS Title,*
CROSS APPLY GetEmpDep(DepID)
select ‘OUTER APPLY behave Like Outer join’ AS Title,*
OUTER APPLY GetEmpDep(DepID)
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