• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1021
  • Last Modified:

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

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
bkienzle
Asked:
bkienzle
  • 3
  • 2
  • 2
  • +1
4 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Show us the T-SQL in SQL Server that produced the first set.
0
 
SterlingMcClungCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
aadihCommented:
Don't know MS-SQL Servers. :-(

Some thing in me compels me to say to you: You are not stupid. :-)
0
 
virtuadeptCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
virtuadeptCommented:
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
 
aadihCommented:
Great job, jimhorn.  :-)

Regards.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now