Source: https://james.rhoat.com/p/implicit-conversion/
Site: James Rhoat
Title: Implicit Conversion in SQL Server
Description: How to audit your database for implicit conversions, identify data type mismatches across columns, and find conversion warnings in the plan cache.
Date: 2021-01-06

***

# Implicit Conversion in SQL Server


Implicit conversions are one of the highest-leverage performance
problems hiding in SQL Server workloads. They happen when the
optimizer has to convert a value from one data type to another at
runtime — usually because a column type and a parameter type don't
match — and the conversion can silently disable index seeks and
turn a fast query into a full table scan. The damage compounds
under load: the same query that ran in milliseconds against a few
thousand rows starts taking seconds against a few million.

The hard part is that nothing about the query text gives the
problem away. There's no error, no warning at compile time, and
the conversion itself is so fast that no individual call looks
slow. You have to inspect the query plan or audit your column
types proactively to find them.

## Audit Your Data Types

The following query shows a count of each data type in use across all user tables. This is a useful starting point for spotting mismatches that lead to implicit conversions.

```sql
SELECT
    t.name AS TypeName,
    COUNT(t.name) AS CountOfType
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id = t.user_type_id
JOIN sys.tables AS tab ON tab.object_id = c.object_id
WHERE tab.is_ms_shipped = 0
GROUP BY t.name
```

## Further Reading

- [Exploring the Plan Cache -- Warnings](https://blogs.msdn.microsoft.com/blogdoezequiel/2013/08/01/sql-swiss-army-knife-13-exploring-the-plan-cache-part-1/#Warnings) -- finding conversion warnings inside cached query plans
- [Finding Implicit Column Conversions in the Plan Cache](http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx) -- Jonathan Kehayias's approach to mining the plan cache for implicit conversions
- [Implicit Conversion Costs](http://sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs) -- measuring the real performance impact
- [Do SQL Server User Defined Datatypes (UDT) Affect Performance?](https://www.mssqltips.com/sqlservertip/1725/do-sql-server-user-defined-datatypes-udt-affect-performance/) -- whether UDTs introduce hidden conversion overhead