I may have found a bug in Microsoft SQL Server and I'm hoping someone can shed some light on this. I've created a very simple example that fully illustrates the problem. If you run the following SQL statements in SQL Server Management Studio you'll see the problem.
CREATE TABLE #ORDERTEST
INSERT INTO #ORDERTEST VALUES('+', 1)
INSERT INTO #ORDERTEST VALUES('-', 2)
INSERT INTO #ORDERTEST VALUES('+', 3)
SELECT * FROM #ORDERTEST ORDER BY OPER
DROP TABLE #ORDERTEST
This creates a super simple temp table, inserts some values, and selects them. The oddity here is that it orders the '-' first. This despite fact that the '+' character has an ASCII value of 43 and '-' has an ASCII value of 45. Even weirder than this is if you replace the nchar(1) with a char(1) declaration it returns the result set ordered in reverse, correctly prioritizing the '+' character over the '-'.
What's going on here? We had to write additional code to catch this condition and order it correctly. I'm wondering where else a similar gotcha might be lurking.