Link to home
Start Free TrialLog in
Avatar of Russ Suter
Russ Suter

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Russ Suter
Russ Suter

ASKER

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.