Implicit Conversion in SQL Server

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.

1
2
3
4
5
6
7
8

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 – finding conversion warnings inside cached query plans
• Finding Implicit Column Conversions in the Plan Cache – Jonathan Kehayias’s approach to mining the plan cache for implicit conversions
• Implicit Conversion Costs – measuring the real performance impact
• Do SQL Server User Defined Datatypes (UDT) Affect Performance? – whether UDTs introduce hidden conversion overhead