Latin1_General explanation

[micang]

SQL 2000 - Query Analyser

Hi All,

I used enterprise manager to generate this code. Can anyone please explain why it generates "COLLATE Latin1_General_CI_AS" ? as I have no idea what it is.

When I do the same query on another database, it does not generate that.

This is the query below:

SELECT     VC_Definitions.Type, COUNT(tbDVLACensus_Anonymised_UsedTransaction.intDVLACensus_AnonymisedID)
FROM         tbDVLACensus_Anonymised_UsedTransaction LEFT OUTER JOIN
                      VC_Definitions ON tbDVLACensus_Anonymised_UsedTransaction.strTaxCode = VC_Definitions.Tax COLLATE Latin1_General_CI_AS AND 
                      tbDVLACensus_Anonymised_UsedTransaction.strBodyCode = VC_Definitions.Body COLLATE Latin1_General_CI_AS
GROUP BY VC_Definitions.Type

Many thanks.

Michael

 

[ptheriault]

Latin1_General_CI_AS is the collation used for that column. It's code page 1252

It means
Latin1_General = English
CI = case Insensitive

I believe sql server places the column collation when you scirpt out objects by default.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Here is a complete definition of each value of the collation.

CollationDesignator

Specifies the base collation rules used by the Windows collation. The base collation rules cover the following:

* The alphabet or language whose sorting rules are applied when dictionary sorting is specified
* The code page used to store non-Unicode character data.

Some examples are:

* Latin1_General or French: both use code page 1252.
* Turkish: uses code page 1254.

For a list of collation designators, see Collation Settings in Setup.

CaseSensitivity

CI specifies case-insensitive, CS specifies case-sensitive.

AccentSensitivity

AI specifies accent-insensitive, AS specifies accent-sensitive.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Thanks for the info Paul.

Would I be correct in saying that if that is ommited, it won't affect the query?

Michael

 

[ptheriault]

YES and NO. It depends on default collation of your database. If your db collation is Latin1_General_CI_AS then it won't matter if you omit. If for example your db collation is Latin1_General_CS_AS (Case Sensitive) it will matter.
To find out your db collation right click on the database in EM or SSMS and select properties.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Many thanks for the help and info Paul.

Michael