Problems with Database collation

The collation of the database is good when installed using the default. For example, in the Simplified Chinese environment, when installing SQL Server, the default is Chinese_prc_ci_as, the English environment is sql_latin1_general_cp1_ci_as. In most cases, the default option has been satisfied with our needs, but if you want to support multiple languages, then you can only choose to support Unicode encoding sql_latin1_general_cp1_ci_as, otherwise there will be garbled, and application query/ Updating the data also requires a flag with N as the Unicode character. I think a lot of people don't pay much attention to this problem.

One example is the collation inconsistency between the instance level and the database level, which can also cause some unexpected problems. For example, all user databases are shared by tempdb, and if there is a user database collation different from tempdb, the use of temporary tables or table variables may cause problems.

Another example is the following (SQL Server 2008R2), where the collation of the Test1 mapped database is inconsistent with the collation of the instance, the property of the login is opened-the securable object, a 468 error message is reported.

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/83/66/wKioL1dySQ6R2BZCAABrhEc2EgU205.png-wh_500x0-wm_3 -wmp_4-s_284814729.png "style=" Float:none; "title=" 01.png "alt=" Wkiol1dysq6r2bzcaabrhec2egu205.png-wh_50 "/>

Figure-1: Properties of the login name

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/83/67/wKiom1dySQ7BiQf6AABIOhVwXVU672.png-wh_500x0-wm_3 -wmp_4-s_3810418991.png "style=" Float:none; "title=" 02.png "alt=" Wkiom1dysq7biqf6aabiohvwxvu672.png-wh_50 "/>

Figure-2:468 Errors reported

So it's better to collation a case of database and instance. In database planning, it is recommended to use SQL_Latin1_General_CP1_CI_AS all, although the application will add N, but for future expansion, it is worthwhile to do so.

This article is from the "fishparadise" blog, make sure to keep this source http://fishparadise.blog.51cto.com/11284420/1793732

Problems with Database collation