June 1, 2006 at 12:06 am
Hello,
I'm in a dilemma I don't know how to solve.
In my database, I will have the names of people from all over europe, so I've used the collecation
Danish_Norwegian_CI_AI.
This makes the sorting correct, but not the select statements.
I also tried using the collation Latin1_General_CI_AI. This makes the select statement ok, but not the sorting.
Lets say I have these names in my table:
name:
-------------
ôyvind
õyvind
ôyvind
õyvind
øyvind
øyvind
öyvind
öyvind
And use this select Statement:
SELECT *
FROM table
WHERE (name= 'øyvind')
order by name
With the collaction :Latin1_General_CI_AI , the select statement will give me all of the names in the table.
With the collation Danish_Norwegian_CI_AI, the selectstatement will only give me these:
øyvind
öyvind
øyvind
öyvind
Now, I can't really understand how I can get both correct sorting, and correct results from select statements.
Is there some kind of trick I can use when either sorting or selecting , or is there some other collation I can use, that will give me both correct sorting, and correct select-statement results ?
June 1, 2006 at 12:58 am
A possible resolution to your problem is as follows:
declare @tbl table (lname sysname collate database_default)
insert into @tbl values ('ôyvind')
insert into @tbl values ('õyvind')
insert into @tbl values ('ôyvind')
insert into @tbl values ('õyvind')
insert into @tbl values ('øyvind')
insert into @tbl values ('øyvind')
insert into @tbl values ('öyvind')
insert into @tbl values ('öyvind')
SELECT *
FROM @tbl
WHERE lname= 'øyvind' collate Latin1_General_CI_AI
order by lname collate Latin1_General_CI_AS
Note the last collate statement where [AS] stands for Accent Sensitive.
You can read more about collation in this article:
http://www.databasejournal.com/features/mssql/article.php/3302341
June 1, 2006 at 9:26 pm
Wow, thanks a lot for showing me that.
I never knew I could do it that way.
I have a few more questions for you:
-Is there a performance loss when specifying different collations for select and sort statements manually , like you showed me ?
-Is there a way to set up 2 different collations for Select and Sort (like you showed me), on either a databaselevel, or tablelevel, so that I do not have to use the 'collate name_of_collation' on each statement ?
June 2, 2006 at 6:41 am
You need to understand that there are two types of collation - one has to do with Windows and the other is SQL Server.
When you first install SQL Server, and you have chosen the default settings, SQL Server will inherit the Windows collation; however, if you had chosen the 'advanced' option, then you will be given the opportunity to choose the collation yourself - either to use Windows collation (default) or to use SQL collation (for backward compatibility). Note that there are a number of collation options to choose from - you will need to do some reading by searching the web!
If you wish to change the collation of a particular data in a table, then you can create a duplicate column specifying the desired collation and then copy the data to it, then drop the old column.
I don't think there is much of a performance issue with specifying a different collation, but you might let us know by doing some testing yourself. As to the collation that is specific to your requirement, it does look like a 'dilemma' as the two collate statements are only different in their Accent (Sensitive vs Insensitive).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply