We help IT Professionals succeed at work.

SQL Server unexpected (and wrong) ORDER BY behavior

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
(
  OPER nchar(1),
  ORDINAL INT
)

INSERT INTO #ORDERTEST VALUES('+', 1)
INSERT INTO #ORDERTEST VALUES('-', 2)
INSERT INTO #ORDERTEST VALUES('+', 3)

SELECT * FROM #ORDERTEST ORDER BY OPER

DROP TABLE #ORDERTEST

Open in new window

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.
Comment
Watch Question

Russ SuterSenior Software Developer

Author

Commented:
Great. Thanks. I thought I was losing my mind. It turns out there's a reason why it behaves this way and we can address it accordingly. Thanks again.