Solved

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

Posted on 2013-10-29
9
927 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
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 65

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 65

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

815 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

8 Experts available now in Live!

Get 1:1 Help Now