Sort order with numbers as strings

For demonstration purposes, I have a sample table with a varchar column called numberstring with the values:

-4
32
0
4
16
8
-8
1024

When selecting using ORDER BY numberstring, I find that different users get different results.

Some get:

-4, -8, 0, 1024, 16, 32, 4, 8

while some get:

0, 1024, 16, 32, -4, 4, -8, 8

Somehow, the second result appears to ignore the minus sign, even though the data is otherwise sorted as string data.

As far as I can tell, the underlying server is a standard install of Microsoft SQL Server express, without any special options. The version is within the last few years, but users with the same version still report different results.

Is there something in the setup which affects the results, and how can I view this from SSMS?

The database server and SSMS are set up in Australia with only the defaults, as far as I am aware. The data type is varchar(max).

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

String sort order is determined by the collation. Users in different locations expect data to be sorted differently and the collation codifies those expectations.

When not explicitly specified, collation for a column is inherited from the database/instance level. Installing SQL Server sets a different instance-level collation, depending what the user chose or the default inferred from Windows language settings. From the documentation:

The server collation is specified during SQL Server installation. The default server-level collation is based upon the locale of the operating system.

For example, the default collation for systems using US English (en-US) is SQL_Latin1_General_CP1_CI_AS. For more information, including the list of OS locale to default collation mappings, see the “Server-level collations” section of Collation and Unicode Support.

Likely some of your users are using the backward-compatible SQL_Latin1_General_CP1_CI_AS collation by default, whereas others elsewhere in the world might be using something like Latin1_General_100_CI_AS:

DECLARE @T table 
(
    numberstring varchar(11) COLLATE Latin1_General_100_CI_AS NOT NULL
);

INSERT @T (numberstring) VALUES
('-4'),
('32'),
('0'),
('4'),
('16'),
('8'),
('-8'),
('1024');

SELECT * FROM @T AS T ORDER BY T.numberstring;

DECLARE @T table
(
    numberstring varchar(11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
);

INSERT @T (numberstring) VALUES
('-4'),
('32'),
('0'),
('4'),
('16'),
('8'),
('-8'),
('1024');

SELECT * FROM @T AS T ORDER BY T.numberstring;

db<>fiddle

This may have come about because some of your users have their Windows locale set to English (United States) while others have English (Australia).

One way to address this issue in your scripts is to use the optional COLLATE clause on your CREATE DATABASE statement (documentation).

---

You can see the default collation for each database using:

SELECT D.[name], D.collation_name 
FROM sys.databases AS D
ORDER BY D.[name];

If that returns a NULL, chances are the database is not online or is closed due to AUTO_CLOSE (which defaults to ON for SQL Server Express). See the documentation for sys.databases.

The instance-level collation with:

SELECT SERVERPROPERTY('Collation');

---

The older SQL_* collations use different rules for Unicode and non-Unicode data:

SELECT FH.* 
FROM sys.fn_helpcollations() AS FH
WHERE FH.[name] IN (N'SQL_Latin1_General_CP1_CI_AS', N'Latin1_General_100_CI_AS');

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0