Now look at the query plans of the following queries, that differ by a single character.

If you look closely, the query that uses an nvarchar parameter does an index scan while the one that users varchar does an index seek. This is very important, because an index seek is orders of magnitude faster than a scan.

The reason this occurs is because the parameter and column have different collation sets.

We are having two MQTs (one of them is having large volume of data) . We are going to join these two MQTs on three fields and these fields are having datatype as varchar(30) in one MQT and in another as varchar(9).

How much it will impact performance of queries if these fields have same column width means varchar(9) in both MQTs ?