Question: I'm having trouble joining tables in the same database, because, for some reason, some columns are of the collation Danish\_Norwegian\_CI\_AS and some are Danish\_Norwegian\_CI\_AI. Most are Danish\_Norwegian\_CI\_AS, and that's the specified collation for the database as well.

I want to alter the collation of these tables, so that they all are Danish\_Norwegian\_CI\_AS, but I can't understand where I should begin to understand the consequences of making this change. Will some characters be mistranslated for example?

I've tried to understand the differences between collations, but can't find much. I understand that the difference has something to do with ascent sensitivity.

I expect all tables, columns etc. to be of collation Danish\_Norwegian\_CI\_AS

Answer: 
The difference between AS and AI is that the sort order (or searches etc.) see or not a difference between letters with (or without) accents, e.g.

```
-- This query would result in 'different', as it uses an accent sensitive collation
SELECT CASE
   WHEN 'ö' COLLATE Latin1_General_CI_AS = 'o' COLLATE Latin1_General_CI_AS
   THEN 'same'
   ELSE 'different'
END

-- This query would result in 'same', as it uses an accent insensitive collation
SELECT CASE
   WHEN 'ö' COLLATE Latin1_General_CI_AI = 'o' COLLATE Latin1_General_CI_AI
   THEN 'same'
   ELSE 'different'
END

```

CS and CI are similar, it means case sensitive ore case insensitive.

Generally it is possible to use a different collation in a table or even in a column, but I never thought this a good idea, esp. when you link tables using a character field, like

```
SELECT
   p.Matchcode,
   p.Name,
   i.Ammount
FROM
   Persons p
   INNER JOIN Invoices i ON i.Matchcode = p.Matchcode

```

This could cause unexpected (and wrong) results when the two tables (or the column Matchcode in the two tables) are using a different collation.

If you need a different collation in a specific query, it would be better to add the collation in the query, something like

```
SELECT
   Lastname COLLATE Latin1_General_CS_AS AS Lastname,
   Firstname COLLATE Latin1_General_CS_AS AS Firstname
FROM
   Persons

```
[Understanding COLLATE DATABASE\_DEFAULT](https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/)