Getting a SEEK rather than SCAN on an Aggregate This is doing my headin, but I'm stuck on trying to get an Index Seek on the MAX aggregate within this query.

Here is the execution plan - and I'm wondering what I'm missing here, as currently as the query takes 3/4 seconds to complete which is too slow. I've indexed on StartDate, ClientID with no luck.

CREATE TABLE #SearchWords
(
    ID int IDENTITY(1,1),
    Word NVARCHAR(50)
)

INSERT INTO #SearchWords
(
    Word
)
SELECT 
    value 
FROM 
    STRING_SPLIT(@SearchWords, ' ')  
WHERE 
    TRIM(value) &lt;&gt; '' 

SELECT
    C.ClientID, 
    C.FirstName,
    C.LastName,
    C.FullName, 
    C.DateOfBirth,
    G.GenderName, 
    G.GenderIcon, 
    C.VerificationCode,
    MAX(V.StartDate) as LastVisitDate
FROM 
    Client C
LEFT JOIN Visit V on 
    C.ClientID = V.ClientID
INNER JOIN LookUp.Gender G on
    C.GenderID = G.GenderID
WHERE
    (
        EXISTS( -- if we have words
                SELECT *
                FROM #SearchWords s
                WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
            )
        OR
        (SELECT COUNT(*) FROM #SearchWords)=0 -- if we haven't any words
    )
    AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)
GROUP BY
    C.ClientID, 
    C.FirstName,
    C.LastName,
    C.FullName, 
    C.DateOfBirth,
    G.GenderName, 
    G.GenderIcon, 
    C.VerificationCode
You want your index to have ClientID as the leading key.  E.g.:

CREATE INDEX IX_Visit_ClientID_StartDate ON Visit(ClientID, StartDate)

This is because you need to join on ClientID before finding the most recent StartDate. Therefore ClientID needs to appear first.

Along with that index change... Since you only want to find the most recent Visit.StartDate you can change your LEFT JOIN Visit to an OUTER APPLY that gets the most recent date.

Try this:

CREATE TABLE #SearchWords
(
    ID int IDENTITY(1,1),
    Word NVARCHAR(50)
)

DECLARE @SearchWordCount INTEGER;

INSERT INTO #SearchWords
(
    Word
)
SELECT 
    value 
FROM 
    STRING_SPLIT(@SearchWords, ' ')  
WHERE 
    TRIM(value) &lt;&gt; ''

SELECT @SearchWordCount = @@ROWCOUNT;

SELECT
    C.ClientID, 
    C.FirstName,
    C.LastName,
    C.FullName, 
    C.DateOfBirth,
    G.GenderName, 
    G.GenderIcon, 
    C.VerificationCode,
    V.LastVisitDate
FROM 
    Client C
OUTER APPLY (
    SELECT MAX(StartDate) AS LastVisitDate
    FROM Visit AS V
    WHERE C.ClientID = V.ClientID
) AS V
INNER JOIN LookUp.Gender G on
    C.GenderID = G.GenderID
WHERE
    (
        EXISTS( -- if we have words
                SELECT *
                FROM #SearchWords s
                WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
            )
        OR @SearchWordCount = 0
    )
    AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)
GROUP BY
    C.ClientID, 
    C.FirstName,
    C.LastName,
    C.FullName, 
    C.DateOfBirth,
    G.GenderName, 
    G.GenderIcon, 
    C.VerificationCode 



  Note:
  
  I've also made the @@ROWCOUNT improvement I suggested in my comment.  


The GROUP BY is almost certainly unnecessary now too.  Remove it if it is.

Off Topic:

One final last comment which is technically unrelated to your question. You seem to be doing something that Full Text Searching may be a better solution for.