?
Solved

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

Posted on 2013-10-29
9
Medium Priority
?
985 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 500 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 1000 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 500 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 1000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

801 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