Microsoft
MicrosoftWhat other data types do you generally see frequently falling under the implicit conversion list? We are facing similar conversion perf issues with our ORM application. Thank you for the detailed write up.
MicrosoftThe most common and classic perf killer when coming from Oracle databases is VARCHAR vs NVARCHAR, but issues can also arise with some other data types as well such as CHAR vs NCHAR, INT vs BIGINT, TEXT vs NUMERIC, DATE vs DATETIME2. These mismatches do not always cause perf issues. The optimizer can adjust to handle conversions effectively within the same family type without wrecking performance in some cases. In contrast, different families (e.g. Unicode vs non-Unicode strings, or strings vs numeric) cannot be directly compared without transforming the data, often the indexed data, leading the optimizer to scan. The key is whether the conversion can be applied before index lookup (e.g. converting a literal or parameter) or if it must be applied to each row of the indexed column. The latter scenario is what degrades performance in most cases.
