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.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Russ Suter

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy