The Fastest way to select recent inserted record

Run the following script to create tables.

• Employee
• Payroll

create table Employee
(
EmployeeID int primary key identity,
Name varchar(150),
NationalIDNo varchar(50),
PhoneNo varchar(20)
)


CREATE TABLE [dbo].[Payroll](
 [PayrollID] [int] IDENTITY(1,1) NOT NULL,
 [EmployeeID] [int] NULL,
 [PayYear] [int] NULL,
 [PayMonth] [int] NULL,
 [BasicSalary] [decimal](18, 2) NULL,
 [HomeAllownce] [decimal](18, 2) NULL,
 [MedicalAllownce] [decimal](18, 2) NULL,
 [NetAmount]  AS (([BasicSalary]+[HomeAllownce])+[MedicalAllownce]),
 CONSTRAINT [PK_Payroll] PRIMARY KEY CLUSTERED 
(
 [PayrollID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Payroll]  WITH CHECK ADD  CONSTRAINT [FK_Payroll_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[Payroll] CHECK CONSTRAINT [FK_Payroll_Employee]

• Run the following script to insert records in tables

INSERT INTO [Employee]([Name],[NationalIDNo],[PhoneNo]) VALUES('Hilaly','NID1234567','+966897667')
INSERT INTO [Employee]([Name],[NationalIDNo],[PhoneNo]) VALUES('Mirwaise','6786876','+966897767786')
INSERT INTO [Employee]([Name],[NationalIDNo],[PhoneNo]) VALUES('Zahir Jan','Abc-67687767','+96689766789')
INSERT INTO [Employee]([Name],[NationalIDNo],[PhoneNo]) VALUES('Waseem','zvw-67687767','+9668978676')

     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(1,2014,3,30000,10000,5000)     
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(1,2014,4,30000,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(1,2014,5,30000,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(1,2015,3,30500,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(1,2016,4,31000,10000,5000)
     
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(2,2014,9,32000,10000,5000)     
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(2,2014,10,32000,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(2,2014,12,32500,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(2,2015,6,33000,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(2,2016,7,33000,10000,5000)
     
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(3,2014,6,40000,10000,5000)     
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(3,2014,7,40000,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(3,2014,11,40500,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(3,2015,6,41000,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(3,2016,7,41500,10000,5000)
     
      INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(4,2014,6,50000,10000,5000)     
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(4,2014,7,50000,10000,5000)
     INSERT INTO [dbo].[Payroll]([EmployeeID],[PayYear],[PayMonth],[BasicSalary],[HomeAllownce],[MedicalAllownce])
     VALUES(4,2014,11,50500,10000,5000)

• And Finally the query which gives you the latest salary issued to employee

;with tbl as(
SELECT rn = ROW_NUMBER() OVER (PARTITION BY EmployeeID 
      ORDER BY PayrollID DESC, EmployeeID DESC), *
   
  FROM dbo.Payroll
  )
  select e.EmployeeID, e.Name,e.NationalIDNo,t.PayrollID,t.PayYear,
  t.PayMonth,t.BasicSalary,t.HomeAllownce,
  t.MedicalAllownce,t.NetAmount  from tbl as t,Employee as e  where rn=1 and
  t.EmployeeID=e.EmployeeID order by e.EmployeeID

• The required output