How to order by last name on a full name column?

I would do something like:

SELECT FullName
FROM TABLE
ORDER BY REVERSE(SUBSTRING(REVERSE(FullName), 0, CHARINDEX(' ', REVERSE(FullName)))) 

Instead of calculating what the last name is each time you want to run the query, you can have a computed column that persists the actual value into a column that can be used as you would any other column.

ALTER TABLE Customer
    ADD LastName AS 
        RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) PERSISTED

This Adds the column LastName to your Customer table, uses the function specified to calculate the value of the last name, and stores it onto the physical table. The keyword PERSISTED at the end is required for the value to be saved to the disk, else it will be computed each time a query is run.

Edit: To deal with values with no spaces:

ALTER TABLE Customer
    ADD LastName AS 
    case when CHARINDEX(' ', REVERSE(FullName)) > 0
    then RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) 
    else
    FullName
    end
    PERSISTED

Though you can fiddle with this function as you will to determine what happens in this case. My point is to show that case statements can be used. You may also want to cast all output paths to the same type to avoid type mismatches.

try this, it uses the minimal number of functions to find the last space in the FullName string, which should help performance:

DECLARE @YourTable table (FullNamevarchar(30))

INSERT @YourTable VALUES ('Harry Smith');
INSERT @YourTable VALUES ('John Davis');
INSERT @YourTable VALUES ('Allision Thomas Williams');

SELECT
    FullName
        ,RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1) AS SortBy
    FROM @YourTable
    ORDER BY RIGHT(FullName, CHARINDEX(' ', REVERSE(FullName)) - 1)

OUTPUT:

FullName                       SortBy
------------------------------ ------------------------------
John Davis                     Davis
Harry Smith                    Smith
Allision Thomas Williams       Williams

(3 row(s) affected)

For the best performance and most accurate sort, you need to split out the name into Firstname, MiddleName, and LastName fields. Only the user entering the data can really understand what portion of FullName is the last name.