You can create a database with a different collation to the server collation. This is one issue you will face in case database collation is different from the server collation.
Then let us create a table with few data,
Let me use temp table to use this data.
So you are using temp table and actual table. (This is a hypothetical scenario just to present this idea).
Last query will return following error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Danish_Norwegian_CI_AS" in the equal to operation.
This is due to the temp database has the server collation, so the workaround will be at the time of creating temp table, you have to explicitly mention the collation.