Site: James Rhoat — Giving back to the community
Source: https://james.rhoat.com/p/implicit-conversion/
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
Categories: sql-server, SQL Server implicit conversion, query plan performance warnings, data type mismatch in SQL Server, plan cache analysis, SQL Server query optimization
Word count: 275
Reading time: 2 min

Contents:
- [Audit Your Data Types](#audit-your-data-types)
- [Further Reading](#further-reading)

***

# 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


---

## Related Posts

- [Volume Shadow Copy Service (VSS)](https://james.rhoat.com/p/vss-backups/page.md) — Jan 15, 2021: How VSS and the SQL Writer Service interact with SQL Server backups, including supported operations, limitations around point-in-time recovery, and common troubleshooting steps.
- [Vertical Partitioning](https://james.rhoat.com/p/vertical-partitioning/page.md) — Jan 14, 2021: How vertical partitioning (row splitting) reduces page waste and improves query performance in SQL Server, with a one-million-row demo.
- [Understanding Storage in SQL Server](https://james.rhoat.com/p/understanding-storage/page.md) — Jan 13, 2021: How SQL Server organizes data at the page and extent level, and why LOB, in-row, and row-overflow storage matter for performance and design.