Solved

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

Posted on 2013-10-29
9
910 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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
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
 
LVL 24

Expert Comment

by:aadih
Comment Utility
Don't know MS-SQL Servers. :-(

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

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 8

Assisted Solution

by:virtuadept
virtuadept earned 250 total points
Comment Utility
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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Great job, jimhorn.  :-)

Regards.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now