2

We have a SQL Server 2014 instance, which houses many related databases. New ones are added via our script regularly. When our app is used to create the database - the databases are created with collation SQL_Latin1_General_CP1_CI_AS as expected.

But there are now databases with Latin1_General_CI_AS collation. They were not created by our app (we looked at the database creator). For example, "Pete" is the db owner and some DBs he created would have Latin1_General_CI_AS collation and others SQL_Latin1_General_CP1_CI_AS.

Can anyone point out what I could investigate please - should the default collation not always be the same as the SQL instance? What if I created a database remotely - from PC1 with Collation1 and PC2 with Collation2 - would the database be created with different collations?

Any ideas how this could happen - multiple collations for one user? Thanks

Improve this question

1 Answer 1

Reset to default
3

The COLLATE clause of CREATE DATABASE can be used to specify a collation different from the Default Server collation. Also, if the databases were backed up from another server, they would be restored with the collation they had on the source server.

If they are created using CREATE DATABASE and a collation is not specified via the COLLATE keyword, then the default collation would be used.

Improve this answer
1
  • Good. I would also mention that it is also possible, even if unlikely to be the issue here, that the DB default Collation can be changed via ALTER DATABASE.
    – Solomon Rutzky
    May 31, 2016 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.