SQL Server 2022 order by bug?

Question

Post reply

SQL Server 2022 order by bug?

hugo-939487

SSC Enthusiast

Points: 149
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 8 posts - 1 through 8 (of 8 total)

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

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

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

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

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

Phil Parkin SSC Guru Points: 247186 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: 149 More actions · Answer 4

hugo-939487

SSC Enthusiast

Points: 149

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: 247186 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: 149 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: 149 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.