Solved

Determining details of sort order in a collation

Posted on 2006-11-10
7
862 Views
Last Modified: 2012-08-13
I'm using collation Finnish_Swedish_CI_AS in a database.

I note that (for instance) V and W are considered equal in this sorting, which surprises me (and possibly all other swedish people, at least the computer-firendly ones:-)
Also, I note that the sorting of danish Ö (o-umlaut) is not sorted together with swedish Ö -- etc etc. Also, there is (of course) a specific sort order of all (as an example) accented "A" characters, and I'd like to know exactly which order it is.

I've tested a little by creating a table with all possible character values, and then checked the sorted output.
However, is there any way to exactly determine the specific details of a collation sort order?
Something like "help_collation 'collation_name'" or "SELECT * FROM ::fn_helpcollations_details()" or something similar?
What I'm looking for is a way to explain to people exactly how each character is sorted.

Also, SQL Server is using Windows collation, right? Perhaps one could get this kind of list somewhere from Windows instead?

Thanks,
/Stefan Lennerbrant
0
Comment
Question by:stefanlennerbrant
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:regbes
ID: 17913470
Hi stefanlennerbrant,

It appears that SQL gets the order from Windows

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

HTH

R.
0
 

Author Comment

by:stefanlennerbrant
ID: 17913645
Thanks.
This page seems to describe "SQL Collations" in general (compared to "Windows collations"). The SQL Collation is depreciated, aren't they, and kept for backwards compatibility reasons?

The sort order used by me is (I think?) a Windows collation. It's named "Finnish_Swedish_CI_AS" and described is by sp_helpsort as:
Finnish-Swedish, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 60 on Code Page 850 for non-Unicode Data

However, the question still remains: How to get a detail description of the actual sort order used?
Something like a list where it is specified that A is listed before B, I before Í, V and W treated as equal, hyphen "-" is ignored, etc etc

Possibly the hint "SQL Server Sort Order 60 on CP850" above is something to investigate? I'll check into it.
Hasn't anybody ever been curious regarding the actial detail sorting order in a collation setup? :-)

/Stefan Lennerbrant
0
 
LVL 11

Expert Comment

by:regbes
ID: 17913670
stefanlennerbrant,


this should di it then

http://msdn2.microsoft.com/en-US/library/ms143515.aspx
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:stefanlennerbrant
ID: 17913686
Well, that just says things like:
  Distinguishes between accented and unaccented characters. For example, 'a' is not equal to 'ấ'.

I'm interested in _exactly_ which order a, á, à, â are sorted, and whether v and w are sorted in different order or are considered equal, etc etc

Nothing is said about that, or did I miss something?

/Stefan Lennerbrant
0
 
LVL 11

Accepted Solution

by:
regbes earned 125 total points
ID: 17913865
stefanlennerbrant,

yes i did not see which accent is sorted before another, its an intresting question but does not realy affect me as we do not user accents here
you could try something like this to get an indirect answer to you question


declare @i int
set @i = 0
create table #ChrOrd (num int,Chr char(1)COLLATE SQL_Latin1_General_CP1_CS_AS ,Chr2 char(1)COLLATE Finnish_Swedish_CI_AI)
set @i = 10
while @i<255

begin
insert into #ChrOrd select @i, char(@i),char(@i)

set @i = @i+1
end

select * from #ChrOrd order by 1
select * from #ChrOrd order by 2

drop table #ChrOrd



 
0
 

Author Comment

by:stefanlennerbrant
ID: 17934462
I don't find any theoretical explanation (or specification) on why the sort order is as it is, but your example does show it empirically. It's the same type of test that I already did, but built in a "nicer way".
However, I'd rather define the fields as char(2) instead and then insert two copies of each:
  insert into #ChrOrd select @i, char(@i)+'a',char(@i)+'a'
  insert into #ChrOrd select @i, char(@i)+'z',char(@i)+'z'

and then display them as (which I suppose you meant from the beginning)
  select * from #ChrOrd order by 2
  select * from #ChrOrd order by 3

Then you clearly see that v+w are considered equal, etc etc.

Well - now if I don't like the defined sort order? Is there anything I can do about it? (apart from trying to find a collation that sorts things in a "better way" according to my needs)
I'll create a new question for this:-)
0
 
LVL 11

Expert Comment

by:regbes
ID: 17936196
stefanlennerbrant,

I think you are lucky MS even admits that accents are diffrent to each other and gives them diffrent sort order, however the cahnce of them getting it right was slim :)
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 46
Passing Parameter to Stored Procedure 4 24
Connecting to multiple databases to create a Dashboard 5 26
SQL - Update field defined as Text 6 17
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

831 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