Collations define the order that string data will be stored and how they will be compared on join/where conditions. If you try to compare two columns with different collations you may received the below error message. This may happen when using temporary tables or different databases in the comparison.
Msg 468, Level 16, State 9, Line 14 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Collations that start with “SQL_” are the older ones and behave in a “SQL2000 way”, this means that it does not follow Windows rules for Unicode strings. Collations without the “SQL_” in the name are newer, compatible with windows, and follows the new rules of Unicode strings.
Because of this difference, the newer collations will be able to do an Index Seek on some specific conditions that SQL collations would cause an Index Scan. If your system uses string comparisons heavily, then this should be taken in consideration when creating the database.
See this great article on the differences and examples with deep analysis (Yeah it is really good!).
I’m out of time today, so go ahead and read the article. Research the web. Read more about this topic. Don’t take my word for granted, I’m researching and learning all the time, this blog is just to put together all the stuff I learn in a simpler and meaningful way. Do your homework too! =D