Priority order of sorting rules
The priority order of the sorting rules (also known as the sort rule type forced rules) is a term that gives a set of rules, and this set of rules determine:
The ordering rule for the final result of the expression of the character string. The sorting rules used by the operator of the sorting rule, these operators use string, but do not return strings, such as LIKE and IN.
The priority rules of the sorting rules apply only to string data types: char, varchar, text, nchar, nvarchar, and ntext. Objects with other data types do not participate in the evaluation of the sorting rules.
The sorting rules of all objects can be classified into one of the four categories. The name of each class is called the sort rule flag.
Sort Rules Sign Object Type Force The defaults for any Transact-SQL string variable, parameters, string values, directory built-in functions, or no string input but generate built-in functions output. If an object is declared in a user-defined function, a stored procedure or trigger, the object assigns the default sorting rule for the database in which functions, stored procedures, or triggers are created. If the object is declared in the batch, it assigns the default sorting rule for the current database used to connect. Implicit X column reference. The sorting rules expressed (represented by X) are obtained from the sorting rules defined in the table or the column. Even if the CREATE TABLE or CREATE VIEW statement is explicitly assigned a sort rule, the column is still returning. Explicit X Explicitly projected into a specific sort rule (represented by x) using the collate clause explicitly projected to a specific sort rule (represented by x). No sorting rules indicate that the value of the expression is the result of the operation between the two strings, and the two strings have the conflict rules of the implicit sort rule flag. The results of the expression are defined as unsorted rules.
The sort rule flag of simply expressing only a string object is the sorting rule flag of the reference object.
If the two operands of the two operands referenced by the complex expression are identical, the sorting rule flag of the complex expression is the sorting rule flag of several expressions.
If the sorting rules of the two operands referenced by complex expressions are different, the sorting rule flag of the complex expression final result is based on the following rules:
Explicitly preferred. The implications are preferred to force the default. In other words, explicitly> implicit> Force default combination two explicit expressions that have been assigned to different sort rules will generate errors. Explicit X Explicit Y = Error combination Two recessive expressions with different sort rules will result in no sorting rules. Rolls X Recessisy Y = No Sort Rules The expression of the aliasing rules with any indication of any flag, except for explicit sorting rules (see the next item), producing no sorting rule flags. No sorting rules any mark = no sorting rules will combine the expression combination of the expression of the unssetted rules and explicit sorting rules will generate an expression of an explicit flag. No sorting rules explicit X = explicit
The following example illustrates the above rules.
Use Tempdb
Go
Create Table Testtab
ID INT,
Greekcol nvarchar (10) Collate Greek_ci_as,
Latincol nvarchar (10) collate latin1_general_cs_as
)
INSERT TESTTAB VALUES (1, N'A ', N'A')
Go
The predicates in the query below have a collision of the rules, so errors are generated:
SELECT *
From testtab
Where greekcol = latincol
The following is the result set.
MSG 446, Level 16, State 9, Server CTSSERV, LINE 1
Cannot Resolve Collation Conflict for Equal to Operation.
The predicate in the query is taken in the sorting rule Greek_ci_AS, because the right expression has an explicit mark, takes a priority to the hidden flag of the right expression: SELECT *
From testtab
Where greekcol = latincol collate Greek_ci_as
The following is the result set.
ID Greekcol Latincol
------------------------------------- -
1 a a
(1 row affed)
The CASE expression in the query below has no sorting rule flag, so it cannot be operated in the sequencing rule in the selection list. However, these expressions can be operated by operators that do not distinguish the sorting rules.
Select (Case When ID> 10 Then Greekcol Else Latincol End)
From testtab
The following is the result set.
MSG 451, Level 16, State 1, Line 1
Cannot Resolve Collation Conflict for Column 1 in Select Statement.
Select Patindex (Case When ID> 10 Then Greekcol Else Latincol End), 'A')
From testtab
The following is the result set.
MSG 446, Level 16, State 9, Server Leih2, Line 1
Cannot Resolve Collation Conflict for Patindex Operation.
Select (Case When ID> 10 THEN GREEKCOL ELSE LATINCOL END) Collate Latin1_General_ci_as
From testtab
The following is the result set.
--------------------
a
(1 row affed)
The following table summarizes the above rules.
Operation Mandatory Sign Explicit X Remaining X Mandatory Default No Sort Rules Explicit Y Generating Error Result For Explicit Y results as explicit y hidden Y results is the explicit Y ruthenium is no sorting rule result The implicit y results are no sorting rules. The default result is the explicit X result is the implicit X result is forced that the result is no sorting rule. No sorting rules result is the result of the unsorted rule for no sorting rules. Sorting rules
Operators and function distinguished sorting rules or disseminated rules:
Distinguishing the rules indicate that the specified no sorting rule operand is compiled. Expression results cannot be no sorting rules. Non-distinguishing rules indicate that the operands and results can be no sorting rules.
The comparison operator and the Max, Min, Between, Like and IN operators are distinguished from sorting rules. The string used by the operator is assigned a sorting rule flag with a higher priority order. The Union operator is also distinguished from the sorting rules, and all string operands and final results are assigned a sorting rule of operands with the highest priority order. The order rule priority is evaluated by column assessment of Union operands and results.
The assignment operator does not distinguish between the sorting rules, projected on the left sorting rules on the right.
String Crapen Unit operators do not distinguish between sorting rules, two string operands and results are assigned a sorting rule mark with an operand with the highest order rule priority order. UNION ALL and CASE operators do not distinguish between sorting rules, all string operands and final results are assigned a sorting rule flag with the maximum number of operands. The order rule priority is evaluated by column assessment of the union all operands and results.
Cast, Convert, and Collate functions distinguish between CHAR, VARCHAR, and TEXT data types. If the input and output of the CAST and Convert functions are strings, the output string has the sorting rule flag of the input string. If the input is not a string, the output string is forced by default and being configured to connect the current database used by the current database, or the sorting rule of the database that references CAST or CONVERTs, stored procedures, or trigger databases. For a built-in function that returns a string but does not use a string input, the result string is forced by default and being assigned the rules of the current database, or contains a user-defined function that references the function, a stored procedure or trigger database. rule.
The following functions are distinguished from the sorting rules, and their output strings have the sorting rule flag of the input string:
Charindex Difference IsNumeric Left Len Lower Patindex Replace Reverse Right Soundex Stuff Substring Upper
The following additional rules apply to the ordering rules priority sequence:
There is no multiple collate clauses that are already an explicit expression. For example, the following WHERE clause is illegal, because the expression specified by the collate clause is already an explicit expression: where column = ('abc' collate french_ci_as collate french_cs_as
The Text Data Type does not allow code page conversion. If the code page of the sorting rule is different, text expressions cannot be projected from a sort rule to another. If the sorting rule code page of the right text operabid is different from the left text operand, the assignment operator cannot assign a value.
Determine the priority order of the sorting rules after the data type conversion. The number of operands of the resulting sequence rule can be different from the number of operands that provide the final result data type. For example, examine the batch of batch:
Create Table Testtab
(PrimaryKey Int Primary Key,
Charcol char (10) collate french_ci_as
)
SELECT *
From testtab
WHERE Charcol Like N'Abc '
Simple expression N'ABC ''s Unicode data type has a higher data type priority, so the result expression assisted the Unicode data type to N'ABC'. However, expression Charcol has a hidden sort rule flag, while N'ABC 'has a lower forced default sort rule flag, so the sort rule used is Charcol's French_CI_as sorting rules.
