How to query SQL Server 2008 database for first and last name and order by relevance?

Question

Basically I have a table like this:

CREATE TABLE Person(
    PersonID int IDENTITY(1,1) NOT NULL,
    FirstName nvarchar(512) NOT NULL,
    LastName nvarchar(512) NULL
)

And I need to find the top n results based on a user-query like this:

"Joh Smi"

The following query returns the results I need (I think). Just not in the relevant order.

SELECT
    PersonID, FirstName, LastName
FROM
    Person
WHERE
    FirstName LIKE 'Joh%' OR
    LastName LIKE 'Joh%' OR
    FirstName LIKE 'Smi%' OR
    LastName LIKE 'Smi%'

If the following names were in the database and our user-query was "Joh Smi" the names should appear in the following order (or similar)

1. John Smith
2. Johnny Smith
3. John Jacob
4. David Smithsonian
5. Daniel Johnson

I'm hoping to get it to work similar to facebook's autocomplete friend-search.

So, how do I return the top n most relevant rows in SQL Server 2008?

Answer 1

I recommend implementing Full Text Search (FTS) for two reasons:

1. Simplify the query (shouldn't need the ORs, which'll perform poorly)
2. Utilize the FTS ranking functionality - see this article