What does the collation AI or AS affect?

[jsteph]

Hi,
I set up a new sql-2005 server, and it appears that the 'default' collation was Latin_General_CI_AI.

My old server had a collation of SQL_Latin1_General_CP1_CI_AS.

I'm of course finding this out after the fact, honestly I'd never given much thought to collation.

Anyway, after I imported some tables from the old server and it appears that it imported the old collation for that table. I'm now getting the "can't resolve the collation...blah" messages when I run queries involving these imported tables.

My main question is, what's the difference--specifically in the AI/AS (accent insensitive/sensitive) and what does that affect in my world, which is a basic business db in the USA, with no special characters, etc. (that I know of)?
Thanks,
--Jim

 

[ptheriault]

AI or AS stands for AccentSensitivity it can be either AI (accent insensitive) or AS (accent sensitive).

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

 

[jsteph]

Paul,
Yes, I knew that as I'd parenthetically said in the post, what I was wondering what it would affect, and why I get the error instead of just, say, a missed match in criteria if one of those accent characters was involved.

From what I've been able to see it has to do with tilde's, etc. I was just wondering if anyone had a general idea of how it would affect a (and I know this is vague) 'typical' usa business database.

I suppose for names, etc, with a caret or accent-grave character, if it were AI it would call an 'a' equal to an 'a' with an accent. But why the sql error?
--Jim

 

[ptheriault]

Jim,
It would error because the extra character is not in the current code page that your using. You can change the collation at the server, database, table and column level.

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

 

[SQLBill]

Even in a typical US business there are customers with names that use the tilde (accent).

if it were AI it would call an 'a' equal to an 'a' with an accent. But why the sql error?

Actually, no - it would not call an a with an accent the same as an a without an accent. It would throw an error that there is a collation issue. Look up the ASCII values. The character a is a different ASCII value than the value a with an accent.

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

To be clearer. In your AS system, the a with an accent is ASCII 133 (I believe). In an AI collation, ASCII 133 doesn't exist. The letter a is ASCII 97. Since it can't translate ASCII 133, you get the collation error.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jsteph]

Paul and Bill,
Thanks very much, I ended up changing the collation on the db, tables, and columns.

I have a follow up question: I recall very vaguely reading something about a 'gotcha' if the server collation doesn't match the db's collation. Something to do with the tempdb--when it's used for sorting--not being the same collation as the db for which it's sorting. Is this a possible issue?
Thanks,
--Jim

 

[gmmastros]

Yes. It's a possible issue.

This problem occurs when you use temp tables (and the TempDB collation does not match your db's collation). There is a way around this, but you may need to modify a lot of your code.

You can cause the collation of a string column in the TempDB to match the default collation of your user db.

create table #Temp (Data VarChar(20) [!]collate database_default[/!])

To be clear, you will only have this problem if you...

Use temp tables
And those temp tables have strings (char, nchar, varchar, nvarchar)
And you join this temp table to a real table on this 'string' column.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom