There is no inherent danger in having different collations across databases. You can even have different collations across various columns in the same table. And in fact, if you look at any of the system catalog views (`sys.tables`, `sys.objects`, `sys.columns`, and so on) you will find that they have several collations across the columns, and for various reasons.
The issues you may potentially run into depend on what you are doing in your queries, and if the database is a contained database.
SET @LocalVar NVARCHAR(50); SELECT @LocalVar = TOP (1) something FROM some table; IF (@LocalVar = N'string literal') BEGIN some statements; END;
could potential behave differently when run in both DBs, even if the same initial value of the same collation was used to set the value of `@LocalVar`. This is not a huge concern, just something to be aware of. Please see the documentation page for Collation Precedence for more details.
The question about where to physical store the data file(s) for the new DB is entirely irrelevant to this Question about collations.
21 People are following this question.