SQL Server 2022 order by bug?
-
Hi,
I was using order by on a column with characters and saw something I couldn't explain. Seems a bug to me, or am I missing something.
SELECT @@VERSION
SELECT N'1.' Id
UNION ALL
SELECT N'1-' Id
ORDER BY Id
SELECT N'1.w' Id
UNION ALL
SELECT N'1-w' Id
ORDER BY Id
DECLARE @s TABLE (Id NVARCHAR(3))
INSERT @s (Id)
SELECT N'1.' Id
UNION ALL
SELECT N'1-' Id
UNION ALL
SELECT N'1.w' Id
UNION ALL
SELECT N'1-w' Id
SELECT Id
FROM @s
ORDER BY IdResults:
Microsoft SQL Server 2022 (RTM-CU22) (KB5068450) - 16.0.4225.2 (X64)
Oct 22 2025 23:44:36
Copyright (C) 2022 Microsoft Corporation
Web Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
Id
----
1-
1.
Id
----
1.w
1-w
Id
----
1-
1.
1.w
1-w
I would expect that the ids with '-' always be listed before the ids with '.'.
Any ideas on this?
Best regards, Hugo.
-
Interestingly, using only VARCHAR produces consistent results. Not sure why Unicode characters are working differently.
-
I get the same (wrong) results, VARCHAR or NVARCHAR makes no difference.
-
This is the code I ran. Can you confirm that it produces inconsistent results on your system?
SELECT @@VERSION
SELECT '1.' Id
UNION ALL
SELECT '1-' Id
ORDER BY Id
SELECT '1.w' Id
UNION ALL
SELECT '1-w' Id
ORDER BY Id
DECLARE @s TABLE (Id VARCHAR(3))
INSERT @s (Id)
SELECT '1.' Id
UNION ALL
SELECT '1-' Id
UNION ALL
SELECT '1.w' Id
UNION ALL
SELECT '1-w' Id
SELECT Id
FROM @s
ORDER BY Id -
I got exactly the same result as with using NVARCHAR.
-
As I am running exactly the same version of SQL Server as you, I can only conclude that your query settings must somehow be affecting the result. Not sure I can help any further.
-
Yes, you pointed me to the right direction. It is a collation thing.
SELECT Id
FROM @s
ORDER BY Id COLLATE Latin1_General_CI_AI
SELECT Id
FROM @s
ORDER BY Id COLLATE SQL_Latin1_General_CP1_CI_AIMy database has collation Latin1_General_CI_AI, which gives the wrong results when using VARCHAR. For NVARCHAR all results are the same and wrong.
-
Yes, you pointed me to the right direction. It is a collation thing.
SELECT Id
FROM @s
ORDER BY Id COLLATE Latin1_General_CI_AI
SELECT Id
FROM @s
ORDER BY Id COLLATE SQL_Latin1_General_CP1_CI_AIMy database has collation Latin1_General_CI_AI, which gives the wrong results when using VARCHAR. For NVARCHAR all results are the same and wrong.
-
I thought it was a collation thing. That usually explains differences across systems like this.
There must be something in the underlying representation that causes this. Interesting issue. It's likely something simple, but I don't see it at first glance
-
With a collation of Latin1_General_CI_AS, I get the same results with SQL2019, SQL2017 and my last remaining instance of SQL2016.
-
IIRC, the collation for the SQL ones has different "weights" for the characters, so the sorting is different. I haven't found a source for this specifically, but it's definitely the way the computer views "." and "-"
-
I would understand the difference between the collations and the various sorting orders if the order were '1.', '1.w', '1-', '1-w' or '1-', '1-w', '1.', '1.w'. But then I still don't understand how the order could become '1-', '1.', '1.w', '1-w'.
Anyway, I've reported this as a bug to Microsoft, we'll see what happens.
-
I know, I am late to this topic.
But collation is the point here:
DECLARE @s TABLE (Id VARCHAR(3) COLLATE LATIN1_GENERAL_BIN)
INSERT @s (Id)
SELECT N'1.' Id
UNION ALL
SELECT N'1-' Id
UNION ALL
SELECT N'1.w' Id
UNION ALL
SELECT N'1-w' Id
SELECT Id
FROM @s
ORDER BY IdMicrosoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!) -
I have just asked copilot and got:
1. Why "1-" sorts before "1."
In most SQL Server collations (e.g., SQL_Latin1_General_CP1_CI_AS), sorting is based on Unicode code point order with collation-specific rules.
The hyphen - (U+002D) has a lower sort weight than the period . (U+002E).
Therefore, "1-" comes before "1." in ascending order.
2. Why "1.w" sorts before "1-w"
This is where collation punctuation rules kick in:
In word sort collations (most default collations), certain punctuation marks are ignored or treated as separators when sorting.
The period . is often treated as insignificant in word sort mode, so "1.w" is compared as if it were "1w".
The hyphen - is treated as a significant character with its own sort weight, so "1-w" is compared as "1<hyphen>w".
Since "1w" (from "1.w") comes before "1<hyphen>w", "1.w" sorts first.
3. How to change this behavior
If you want strict binary ordering (pure Unicode code point order, no punctuation ignoring), you can use a binary collation:
Sql
Copy code
SELECT value
FROM (VALUES ('1-'), ('1.'), ('1.w'), ('1-w')) AS t(value)
ORDER BY value COLLATE Latin1_General_BIN;
Result with binary collation:
Copy code
1-
1-w
1.
1.w
Here, sorting is purely by byte/Unicode value — no special punctuation rules.
? Key takeaway:
Default collations often use linguistic sorting, which can ignore or de-prioritize certain punctuation (like .) but not others (like -).
Binary collations give you predictable, code-point-based ordering.
If you want, I can give you a side-by-side comparison table of how "1-", "1.", "1.w", and "1-w" sort under different SQL Server collations so you can pick the right one for your case. Would you like me to prepare that?
-
Well, I learned something new today. Apparently, there's more to it than meets the eye.
Thanks, guys.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply