Solved

Difference in sort order between Oracle/varchar2 and SQL Server/nvarchar

Posted on 2013-10-29
9
965 Views
Last Modified: 2013-10-30
Have a table with 5 rows.

If I order by MODULENAME in SQLServer I get this: (Sewer seems out of order)
DID      MODULENAME
3      S-Drainage
3      Sewer
3      S-Signs / Markings
3      S-Streets
3      S-Traffic

If I order by MODULENAME in Oracle I get this: (as I would expect)
DID      MODULENAME
3      S-Drainage
3      S-Signs / Markings
3      S-Streets
3      S-Traffic
3      Sewer

This seems like database 101 stuff. What am I missing?
Additional info, but this shouldn't matter:
MODULENAME datatype in SQL is nvarchar(20), Oracle is varchar2(20)
Part of Primary key in SQL, unindexed in Oracle.
0
Comment
Question by:bkienzle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39609327
Show us the T-SQL in SQL Server that produced the first set.
0
 
LVL 7

Assisted Solution

by:SterlingMcClung
SterlingMcClung earned 125 total points
ID: 39609331
I suspect that this is due to differences in how the hyphen is treated in the various platforms.  Looks like depending on the collation that you have chosen in MS SQL, the hyphen may be ignored, like what you are seeing:
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39609345
Couple of possibilities
*  The ORDER BY clause was commented out, or everything except the ORDER BY clause was highlighted, when it was executed.
*  The ORDER BY clause is sorting by another column before MODULENAME which is producting the set.  If that column is not in the SELECT clause this may not be obvious.
*  There's a UNION involved.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 24

Expert Comment

by:aadih
ID: 39609348
Don't know MS-SQL Servers. :-(

Some thing in me compels me to say to you: You are not stupid. :-)
0
 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 250 total points
ID: 39609359
I am not sure why this happens but it does it for me too, and no indexing doesn't matter. I suspect it has to do with how NVARCHAR (multiple bytes) is stored versus VARCHAR (1 byte per character).

declare @t table (did int, modulename nvarchar(20))

INSERT @t
SELECT 3,      N'S-Drainage' UNION ALL SELECT
3,      N'Sewer' UNION ALL SELECT
3,      N'S-Signs / Markings' UNION ALL SELECT
3,      N'S-Streets' UNION ALL SELECT
3,      N'S-Traffic'

SELECT did, modulename
from @t
order by modulename

go

declare @t table (did int, modulename varchar(20))

INSERT @t
SELECT 3,      'S-Drainage' UNION ALL SELECT
3,      'Sewer' UNION ALL SELECT
3,      'S-Signs / Markings' UNION ALL SELECT
3,      'S-Streets' UNION ALL SELECT
3,      'S-Traffic'

SELECT did, modulename
from @t
order by modulename

Open in new window


Output from that:

(5 row(s) affected)
did         modulename
----------- --------------------
3           S-Drainage
3           Sewer
3           S-Signs / Markings
3           S-Streets
3           S-Traffic

(5 row(s) affected)


(5 row(s) affected)
did         modulename
----------- --------------------
3           S-Drainage
3           S-Signs / Markings
3           S-Streets
3           S-Traffic
3           Sewer

(5 row(s) affected)

Open in new window


This has me curious so I will see if I can figure out why.
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
ID: 39609383
Building off of the links provided by SterlingMcClung, this is fascinating stuff
DECLARE @test TABLE
(id int, string varchar(50))

INSERT INTO @test 
VALUES (1, 'co-op'), (2, 'co op'), (3, 'co_op'), (4, 'coop'), (5, 'co-a'), (6, 'co-z')

/*
Unicode       Word Sort     Hyphen and apostrophe are special cased, e.g. "coop" and "co-op" will sort together in a list
Non-Unicode   String sort   Hyphen and apostrophe will sort with all other symbols
*/

-- Unicode
SELECT * FROM @test ORDER BY CAST(string AS NVARCHAR(50)) 

-- Non-Unicode
SELECT * FROM @test ORDER BY string		--  COLLATE SQL_Latin1_General_Cp1_CI_AS

Open in new window

0
 
LVL 8

Accepted Solution

by:
virtuadept earned 250 total points
ID: 39609392
I found this that explains the why.

http://support.microsoft.com/kb/322112

As to how to get around it, you could do this:

ORDER BY CONVERT(VARCHAR(20),modulename)
0
 
LVL 24

Expert Comment

by:aadih
ID: 39609911
Great job, jimhorn.  :-)

Regards.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question