Sometimes it is important to choose right collation for the server

The title looks like a joke, am I right? Sometimes we just take care of collation for specific database. Well that may lead you to unpleasant situation and moreover getting wrong or no data at all. Recently I helped my friend to solve the problem for his new software. He created a database with "right" collation let say Hebrew_CI_AS and the program searches for the data that contains hebrew characters. Suddenly the program does not return the data at all. He showed me that the data he is looking for are presented in the table and point me to the stored procedure that performs the searching. You have already guessed what was the problem, right? The problem was because withing a stored procedure he used a temporary table where the programm stores the data for final result (VARCHAR(n) for search string) for some logic. As you know tempdb is a system database which inherits a server's collation (SQL_Latin1_General_CP1_CI_AS) and hence no data were returned. In that specific case we changed the data type to NVARCHAR(n) and there is no need to reinstall the entire server, but think about the program(I have seen many of such) that such technique (store unicode character in temporary tables) are used in hundred of stored procedures and change the datatype for all of them is very big headache.