-1

1/ Sometime you write

     ALTER DATABASE [MyDb] COLLATE SQL_Latin1_General_CP1_CI_AS

after a moment, if you try 

     ALTER DATABASE [MyDb] COLLATE FRENCH_CI_AS

it may not succeed. why this?

2/ if table1 is SQL_Latin1_General_CP1_CI_AS and if table2 is FRENCH_CI_AS one cannot write ...where table1.field1= table2.field2

What is the consequence of COLLATE in a database or table ?

Bellash
  • 7,560
  • 6
  • 53
  • 86
  • 1
    1: The statemnt could fail because of the fact that the database is in use by someone else, you can't issue an `ALTER DATABASE` statement then. Try setting it to single user mode first. 2: For SQL Server begin here: http://msdn.microsoft.com/en-us/library/ms143726.aspx – NickyvV May 22 '14 at 08:37

3 Answers3

1

You can compare item with different collations. For SQL Server you use :

table1.field1 = table2.field2 COLLATE FRENCH_CI_AS

or

 table1.field1 = table2.field2 COLLATE SQL_Latin1_General_CP1_CI_AS

(or whatever the field1 collation is).

Note: You should try and leave the collation as the server default, unless you have a specific reason to do otherwise.

Bellash
  • 7,560
  • 6
  • 53
  • 86
Anthony Horne
  • 2,522
  • 2
  • 29
  • 51
1

you can compare your query with collate name

table1.field1 = table2.field2 COLLATE FRENCH_CI_AS

otherwise Alter Column.(If you want to change field COLLATE)

ALTER TABLE [dbo].[TableName]
        ALTER COLUMN [FieldName] [DataType] COLLATE [Collate Name] NOT NULL
Harshil
  • 403
  • 2
  • 7
1

This answer is based on experience from working with Ms Sql Server. I have only a little experience from working with other databases, but I imagine much of this would apply for them as well.

From Msdn (here):

Collations let users sort and compare strings according to their own conventions

Collations are used to let sql server know which characters are considered the same. If a case insensitive collation is used, X and x are considered the same. If a case sensitive collation is used, they are different. In an accent insensitive collation e and é might be considered the same, while in an accent sensitive collation they are different.

At least with Ms Sql Server, if you are comparing two strings that are stored in different collations, you must tell the server which collation to use in the comparison by using the COLLATE clause already mentioned in other answers. Not sure about how this is done in other databases.

If your table contains a row with a column that contains the text ÖBc and you select from this table like so:

select COL from TBL where COL = 'obc'

Then the row will be found using a case and accent insensitive collation like latin1_general_ci_ai. Accent insensitivity means that O and Ö are the same, and case insensitivity takes care of the case mismatch.

With the same select and collation latin1_general_cs_ai the row will not be found because of case sensitivity.

Similarly with collation latin1_general_ci_as, the row will not be found because of accent sensitivity.

Collation is also used to determine alphabetical sort order when sorting results. This determines if upper and lower case charachers are sorted differently and also where accented charaters are sorted.

user1429080
  • 9,086
  • 4
  • 31
  • 54