A collation has two effects:
- For non-Unicode data types it determines the code page of the data, i.e. it determines which characters you can store in the column/variable or not
- For all data types, it affects how data is sorted and compared, i.e. ORDER BY and equality
To avoid problems with the first issue, always store and manipulate Unicode data using the nchar/nvarchar data types, because then you don't have to worry about the collation anyway. It requires more disk space, but it avoids some really awkward issues, so for most people it's probably a good tradeoff.
For the second issue, use the collation that makes the most sense for your database, i.e. which collation sorts and compares the data in the way that you want to do it most of the time? For example, if you know that case-sensitive comparisons will be important then Latin1_General_CS_AS might be a better choice.
And you can always use COLLATE to specify the collation explicitly if you need more control over specific queries:
create table #t (name nvarchar(100))
insert into #t select N'Che'
insert into #t select N'Carlos'
insert into #t select N'Cruz'
select name from #t order by name collate Modern_Spanish_CI_AS
select name from #t order by name collate Traditional_Spanish_CI_AS
drop table #t
If you don't know how text data will be sorted or compared and if your users don't know either, then I would just stay with your default collation (and use Unicode!); in the worst case, you can always move the data to a new table with the correct collation. And there's a lot of documentation on collations in Books Online that you should have a look into.