SQL Collation and related performance impact, viewing collation in query plans

Hi there. A few clarifications need to be made to the info presented here.

whether a Literal predicate which had different accent sensitivity or collation when compared to a column would trigger an implicit conversion resulting in potential performance degradation

1. String literals and variables initially use the database's default collation as their collation.
2. Assuming no explicit collation set via COLLATE clause on the predicate, the collation of the literal or variable is irrelevant (from a performance perspective, though for VARCHAR data it can be relevant if any characters having values 128 - 255 / 0x80 - 0xFF are present) since collation precedence uses the collation of the column.
3. implicit conversions, by themselves, are not the issue, as whatever performance degradation they cause is minimal and likely barely noticeable.

If the literal being compared has a different collation or is explicitly converted to a different collation, collation precedence kicks in

1. yes, if there is any difference in collation, for any reason, collation precedence will determine which collation to use, of if both sides have equal weight, then an error will be thrown.
2. a literal (or variable) always starts out with the database's default collation. But this doesn't matter since collation precedence will use the collation of the column.
3. an explicit collation (i.e. set via the COLLATE {collation_name} clause on a predicate) will override the column's collation, but if there is any degradation in performance in this scenario (and that's usually only possible, though not even guaranteed, if the column is indexed), then nobody should really be surprised because it was an explicit change that is clearly stated in the query 😉

Is the literal collated to the collation of the Database or the collation of the column given they both are different? ... We actually get a Seek here, which means the literal here was converted to the collation of the column and not the database.

The question and answer here are both poorly phrased and hence can be misleading.

1. the actual answer to the question is: BOTH. Initially (i.e. when the query is being parsed), literals and variables use the database's default collation. Then, if compared to a column having a different collation than the database (or COLLATE clause is used), the collation will be converted to the column's collation (or to what is specified by the COLLATE clause).
2. literals (and variables) need to have a collation, so it doesn't make sense that they would be converted to the database's collation; they are already using the database's collation.

With regards to a collation difference causing performance degradation, that can only really happen under the following conditions:

1. column is indexed, and ...
2. COLLATE clause is specified on the predicate
3. COLLATE clause specifies a different collation than the column is using (and hence was used in creating the index)

However, the scenario that typically does cause a performance degradation and is characterized as being caused by an "implicit conversion" (even though that isn't the real issue) is datatype precedence:

1. column is VARCHAR.
2. column is using a SQL Server collation (i.e. name starting with "SQL_").
3. column is indexed.
4. column is compared to NVARCHAR literal or variable, or NVARCHAR column using the same collation (different collation would simply error)

I have a detailed write-up about this here:

https://sqlquantumleap.com/2017/07/10/impact-on-indexes-when-mixing-varchar-and-nvarchar-types/

Take care,
Solomon...

https://SqlQuantumLift.com/
https://SqlQuantumLeap.com/
https://SQLsharp.com/