SQL Server: server collation

Question

What is the impact of Server collation on databases? We have dev and production environments on different server collations, is there a risk having these different server collations?

Answer 1

Well sure there is. Your sorting and all comparisions may not be the same (For example, one may be case sensative, one may not). The collation handles how character comparisions are done. See here for more details.

You should make sure they are the same. There is no reason for it to be otherwise.

Answer 2

If case & accent sensitivity are the same on both development and production machines than most likely you won't notice any problems related to different collations.

Sorting for example may not work exactly the same which may not be critical for lot's of small applications.

Answer 3

For VARCHAR columns, collations affect the range of characters that can be stored. (See my blog post)

For both VARCHAR and NVARCHAR columns, collations affect comparison and sorting, as different collations (locales) have different sort orders and handle accented characters and casing differently.

You can define a collation on server, database, and column level, and even override it on statement level using the COLLATE clause.