A collation itself specifies the rules for how strings of character data are sorted and compared. The rules for sorting data vary depending on the language and locale. For example if you was to use a Lithuanian collation, the letter "Y" would appear between "I" and "J" if sorted. And if using the traditional Spanish collation "ch" would be sorted at the end of a list of words beginning with "c".
More details here
Even within a table you could have different collations. Collation is a column property, therefore you would need to change the collation for each column: http://www.sqlusa.com/bestpractices/changecollation/