TSQL - Is it possible to define the sort order?

It's incredibly clunky, but you can use a CASE statement for ordering:

SELECT * FROM Blah ORDER BY CASE MyColumn     WHEN 'orange' THEN 1     WHEN 'apple' THEN 2     WHEN 'strawberry' THEN 3     END 

Alternately, you can create a secondary table which contains the sort field and a sort order.

TargetValue  SortOrderorange       1apple        2strawberry   3

And join your table onto this new table.

Use a CASE statement:

ORDER BY CASE your_col           WHEN 'orange' THEN 1           WHEN 'apple' THEN 2           WHEN 'strawberry' THEN 3         END 

Alternate syntax, with an ELSE:

ORDER BY CASE            WHEN your_col = 'orange' THEN 1           WHEN your_col = 'apple' THEN 2           WHEN your_col = 'strawberry' THEN 3           ELSE 4         END 

If this is going to be a short-lived requirement, use a case statement. However, if you think it may be around for a while, and it's always going to be orange/apple/strawberry order (or even if not - see below), you may want to think about sacrificing some disk space to gain some speed.

Create a new column in your table called or_ap_st and use an insert/update trigger to populate it with the number 1, 2 or 3, depending on the the value of your fruit column. Then index on it.

Since the only time the data in that column will change is when the row changes, that's the best time to do it. The cost will then be incurred on a small number of writes rather than a large number of reads, hence amortised over the select statements.

Your query will then be a blindingly fast:

select field1, field2 from table1order by or_ap_st;

with no per-row functions killing the performance.

And, if you want other sort orders as well, well, that's why I called the column or_ap_st. You can add as many other sorting columns as you need.