This blog will explain one of the most important question in interview?

Is How to Join Table Valued Function with Table?

For Ex

  1. Create table Emp as
    1. CREATE TABLE [dbo].[Emp](
    2. [ID] [int] IDENTITY(1,1) NOT NULL,
    3. [Name] [varchar](50) NULL,
    4. [City] [varchar](50) NULL,
    5. CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED
    6. (
    7. [ID] ASC
    8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    9. ) ON [PRIMARY]
    10. GO
    And Insert Record into Emp table as



  2. Create Table value function as
    1. GO
    2. -- =============================================
    3. -- Author: <Author:- Vishvajeet>
    4. -- Create date: <Create:- 23/11/2014>
    5. -- Description: <Description:- Fn_Select Employee Salary>
    6. -- =============================================
    7. Create FUNCTION [dbo].[fn_Salary]
    8. (
    9. @id Int
    10. )
    11. RETURNS TABLE
    12. AS
    13. RETURN
    14. (
    15. SELECT Salary from Salary where ID=@id
    16. )
    Then we can join these table value function and table with in the help of "Cross Apply" as
    1. select e.ID,e.Name,e.City,x.Salary from Emp e left join
    2. (
    3. select ID,Name,Salary from Emp cross apply [dbo].fn_Salary(ID)
    4. )x on x.ID=e.ID

And show Result as