SQL Server 2022 order by bug?

Question

Post reply

SQL Server 2022 order by bug?

hugo-939487

SSC Enthusiast

Points: 150
More actions
November 25, 2025 at 10:16 am

#4690313
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 Id
```
Results:
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.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247201 More actions · Answer 1

Interestingly, using only VARCHAR produces consistent results. Not sure why Unicode characters are working differently.

hugo-939487 SSC Enthusiast Points: 150 More actions · Answer 2

I get the same (wrong) results, VARCHAR or NVARCHAR makes no difference.

Phil Parkin SSC Guru Points: 247201 More actions · Answer 3

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

hugo-939487 SSC Enthusiast Points: 150 More actions · Answer 4

hugo-939487

SSC Enthusiast

Points: 150

November 25, 2025 at 12:57 pm

#4690361

Query Results

I got exactly the same result as with using NVARCHAR.

Phil Parkin SSC Guru Points: 247201 More actions · Answer 5

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.

hugo-939487 SSC Enthusiast Points: 150 More actions · Answer 6

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_AI

My database has collation Latin1_General_CI_AI, which gives the wrong results when using VARCHAR. For NVARCHAR all results are the same and wrong.

hugo-939487 SSC Enthusiast Points: 150 More actions · Answer 7

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_AI

My database has collation Latin1_General_CI_AI, which gives the wrong results when using VARCHAR. For NVARCHAR all results are the same and wrong.

Steve Jones - SSC Editor SSC Guru Points: 740103 More actions · Answer 8

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

Ken McKelvey SSCoach Points: 18979 More actions · Answer 9

With a collation of Latin1_General_CI_AS, I get the same results with SQL2019, SQL2017 and my last remaining instance of SQL2016.

Steve Jones - SSC Editor SSC Guru Points: 740103 More actions · Answer 10

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 "-"

hugo-939487 SSC Enthusiast Points: 150 More actions · Answer 11

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.