Noted, thank you.

Came up with the below ITVF, but the performance is worse than the scalar function. Any suggestion on improving the ITVF or pointers where I'm doing wrong would be appreciated, there must be a better logic than below

IF OBJECT_ID('dbo.fnProperCase_ITVF') IS NOT NULL
 DROP FUNCTION dbo.fnProperCase_ITVF
GO
CREATE FUNCTION dbo.fnProperCase_ITVF
(
 @InputString VARCHAR(2000)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
 WITH Tens (N) AS
 (
  SELECT N FROM
  (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS A (N)
 ),
 Tally (Num) AS
 (
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM Tens AS A,Tens AS B,Tens AS C,Tens AS D
 ),
 Words (Num,Word) AS
 (
  SELECT
   Num,SUBSTRING(' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' ',Num + 1,CHARINDEX(' ',' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' ',Num + 1) - Num -1)
  FROM Tally
  WHERE Num < LEN(' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' ')
   AND SUBSTRING(' ' + LTRIM(RTRIM(LOWER(@InputString))) + ' ',Num,1) = ' '
 )
 SELECT
  PropperString = STUFF((
   SELECT ' ' + UPPER(SUBSTRING(Word,1,1)) + SUBSTRING(Word,2,LEN(Word))
   FROM Words
   ORDER BY Num
   FOR XML PATH('')),1,1,'')
)
GO

Attached is the test harness I used to compare the functions on a million row table and below are the timings.