Return to Revisions

A potentially faster method that works with your existing structure would be

DECLARE @Query VARCHAR(8000) = 'car BIKE RACK';

WITH QSplit([1], [2], [3], [4]) As
(
SELECT ' ' + [1], 
       ' ' + [2], 
       ' ' + [3], 
       ' ' + [4]
FROM string_split(UPPER(@Query), ' ', 1)
PIVOT (MAX(value) FOR ordinal IN ([1], [2], [3], [4])) AS P
)
SELECT m.*, Rank
FROM MyTable m
CROSS JOIN QSplit
CROSS APPLY (VALUES (CONCAT(' ', UPPER(Data) COLLATE Latin1_General_100_BIN2))) V(SpacePadded)
CROSS APPLY (VALUES (IIF(CHARINDEX([1],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([2],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([3],SpacePadded) > 0,1,0) + 
                     IIF(CHARINDEX([4],SpacePadded) > 0,1,0))               
                ) V2(Rank)
WHERE Rank > 0 
ORDER BY Rank DESC;

This

• Defines @Query as a non max datatype as doesn't sound like you need max and that can be slower.
• Only splits @Query once rather than redoing the splitting for each row in MyTable
• Removes the (non inlined) scalar UDF
• Simplifies the calculation of RANK to remove some execution plan operators
• Uses a binary collation in the hope that this should speed up the CHARINDEX search (NB: If you are able to ensure that all the Data will be stored in a canonical upper case form then do so and remove that UPPER(Data) call to avoid spending CPU time doing that at run time)

With this dummy data I found the revised method executes in around 100ms on my machine (original method was around 3 seconds)

INSERT INTO MyTable VALUES ('A CAR'), 
                           ('A BIKE'), 
                           ('CAR WASH'), 
                           ('CAR BIKE RACK'), 
                           ('A HOUSE');

INSERT INTO MyTable
SELECT TOP 75000 REPLACE(NEWID(), '-', ' ' )
FROM sys.all_columns c1, sys.all_columns c2