latin1_general_ci: Não há distinção entre letras maiúsculas e minúsculas. Buscando por “teste”, registros como “Teste” ou “TESTE” serão retornados. latin1_general_cs: Distingue letras maiúsculas e minúsculas. Buscando por “teste” somente retornará “teste”. Opções como “Teste” e “TESTE” não serão retornadas. latin1_swedish_ci: Não distingue letras minúsculas e maiúsculas e nem caracteres acentuados e com cedilha, ou seja, o registro que contém a palavra “Intuição” será retornado quando houver uma procura pela palavra “intúicao” Mesmo que você troque o collation do banco de dados os objetos previamente criados não terão o collation alterado, para isso você terá que recriar o objeto.
Em relação a performance, devido ao fato de que cada collation tem suas regras no tratamento de strings, pode-se ter variações grandes de performance dependendo do tamanho da tabela a ser consultada.
Esse artigo cita um exemplo onde a diferença de performance foi de 10x entre duas tabelas idênticas com collations diferentes.
Qual collation utilizar vai depender de quais idiomas você vai suportar, por exemplo se você for trabalhar com linguagens baseadas em latin (européias, portugues, etc) voce pode usar Latin1_General, que corresponde basicamente ao conjunto de caracteres ASCII. Para trabalhar com um charset maior, como o Unicode, o mais importante é usar tipos de dados que suportam esse charset, como nchar, nvarchar.