This is the definition of collate on books online. When I was helping my colleagues solve a database problem today, I encountered the cannot resolve collation conflict for equal to operation,
Update sec_usrdata
Set userid = new_id
From sec_usrdata, map_table
Where userid = old_id
The sec_userdate table requires collate chinese_taiwan_stroke_ci_as when creating a table, but this statement is not added when map_table is created, resulting in an error in data update. In addition, I haven't written any script for a long time. I forget that I can add from after update. Khan ~~ Paste to save ^
CollateA clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.
SyntaxCollate <collation_name> <collation_name >::=
{
Windows_collation_name} | {
SQL _collation_name}
ArgumentsCollation_nameis The Name Of The collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified
Windows_collation_nameOr
SQL _collation_name.
Windows_collation_nameIs the collation name for Windows collation. See windows collation names.
SQL _collation_nameIs the collation name for a SQL collation. See SQL collation names.
RemarksThe collate clause can be specified at several levels, including the following:
- Creating or altering a database.
You can use the Collate clause of the create database or alter database statement to specify the default collation of the database. you can also specify a collation when you create a database using SQL Server Enterprise Manager. if you do not specify a collation, the database is assigned the default collation of the SQL server instance.
- Creating or altering a table column.
You can specify collations for each character string column using the Collate clause of the create table or alter table statement. you can also specify a collation when you create a table using SQL Server Enterprise Manager. if you do not specify a collation, the column is assigned the default collation of the database. you can also use the database_default option in the Collate clause to specify that a column in a temporary table use the collation default of the current user database for the connection instead
Tempdb.
- Casting the collation of an expression.
You can use the Collate clause to cast a character expression to a certain collation. character literals and variables are assigned the default collation of the current database. column references are assigned the definition collation of the column. for the collation of an expression, see collation precedence. the collation of an identifier depends on the level at which it is defined. identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database. for example, two tables with names differing only in case may be created in a database with case-sensitive collation, but may not be created in a database with case-insensitive collation. variables, Goto labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database, and then referenced when the context has been switched to another database. the identifiers for variables, Goto labels, temporary stored procedures, and temporary tables are in the default collation of the instance. the collate clause can be applied only for
Char,
Varchar,
Text,
Nchar,
Nvarchar, And
NtextData types. collations are generally identified by a collation name. the exception is in setup where you do not specify a collation name for Windows collations, but instead specify the collation designator, and then select check boxes to specify binary sorting or dictionary sorting that is either sensitive or insensitive to either case or accents. you can execute the system function
Fn_helpcollationsTo retrieve a list of all the valid collation names for Windows collations and SQL collations: Select * From: fn_helpcollations () SQL Server can support only code pages that are supported by the underlying operating system. when you perform an action that depends on collations, the SQL Server collation used by the referenced object must use a code page supported by the operating system running on the computer. these actions can include:
- Specifying a default Collation for a database when you create or alter the database.
- Specifying a collation for a column when creating or altering a table.
- When restoring or attaching a database, the default collation of the database and the collation of anyChar,Varchar, AndTextColumns or parameters in the database must be supported by the operating system.
Code Page translations are supported
CharAnd
VarcharData types, but not
TextData type. data loss during code page translations is not reported. if the collation specified or the collation used by the referenced object, uses a code page not supported by windows, SQL Server issues error. for more information, see the collations section in the SQL Server Architecture chapter of the SQL Server books online.
