CODEWITHSUNDEEP
sql-serverstringsorting
Sunil
Sunil

Reputation: 21406

Why is string length not being considered when SQL Server sorts a string column

I have a simple SQL fiddle at SQL Fiddle for this question.

Question

I am not understanding something very basic i.e. why does SQL Server place abcx before abd when sorting string values in an ascending order?

If someone could please explain the behind-the-scene logic followed by database engine, then this would be very clear.

I thought smaller length string would always come before a larger length string. So, first all strings of length 1 would be sorted, followed by strings of length 2 , followed by strings of length 3 and so on.

Script for this question is as below.

create table someStrings ( myname varchar(100) );

insert into someStrings (myname) 
values ('abc'), ('abd'), ('abcx');

select myname 
from someStrings 
order by myname;

Upvotes: 1

Views: 207

Answers (2)

Thom A
Thom A

Reputation: 95913

When sorting a (n)varchar in SQL Server, each character in its position is sorted against those in the same position. If a (n)varchar is shorter than another, but its first character is later in the alphabet that another value, then that value will appear later in the sort.

So, for example, the simple value A is very likely be appear at the beginning of your sort order. Likewise zAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA is going to appear near the end, as its first character is z.

In your example of abd and abcx, firstly the (n)varchar is sorted by its first character, a, then by its second, b. These are both the same. The third characters, however, are c and d. c in the alphabet, is prior to the letter d, thus in an ascending sort order abcx will be sorted before abd; because the 3rd character is earlier in the alphabet and is the first character that differs.

Edit: Also, to add a little more. If a (n)varchar is shorter than another (n)varchar but shares the same initial characters, it will also be sorted earlier. So, for example, the value add would be sorted prior to the word addition.

Upvotes: 2

Barry Kaye
Barry Kaye

Reputation: 7759

I believe what you are really discussing here is SQL Server Collation - have a look at this MS article - it's for SQL 2008 but as good as any for this point:

Selecting a SQL Server Collation

Upvotes: 2

Related Questions