Collation precedence rules

Following are collation precedence rules:

1. Any literal, variable, parameter or other string would have the collation of database. This is called Coercible-default.
2. Columns collation (implicit) takes precedence over Coercible-default.
3. Explicit collation takes precedence over columns collation (implicit).

This can be written as Explicit > Implicit > coercible-default
The below script explain this:

CREATE TABLE TestTab

(PrimaryKey int PRIMARY KEY,
CharCol char(10) COLLATE French_CI_AS,
)
INSERT INTO TestTab VALUES (1, 'abc')
SELECT 'ABC' as [Coercible],
CharCol + 'ABC' as [Implicit],
CharCol + 'ABC' COLLATE greek_ci_as AS [Explicit]
INTO testTbl2
FROM TestTab
GO

Here using the SELECT INTO statement I am creating a table testTbl2 so that we can check the collation of these columns. Now as per the collation precedence rules:

1. Collation of column [Coercible]: as per rule 1 collation of literal ‘ABC’ should be the collation of database that is: SQL_Latin1_General_CP1_CI_AS.

2. Collation of column [Implicit]: as per rule 2 it should be French_CI_AS.
3. Collation of column [Explicit]: as per rule 3 it should be greek_ci_as.

Now let us check it with sp_help testTbl2 and see the result in below screenshot.