Incorrect datatype can cause many issues in sql. Below is the list compiled from many places, Mainly from Thomas Larock’s webcast.
Disk Storage
– Longer Maintenance (Index Rebuild, Reorganize, Unnecessary LOB data, prevents online index rebuilds up to 2008 R2 etc.)
– Longer Recovery
– Longer Backups
– Extra Physical I/O
– Hidden Cost when clustered key is long which is not needed. All non-clustered indexes include the clustering key
Memory– Extra Bytes need to read into Buffer
– Extra Logical I/O
Performance
– High chance of Implicit Conversion
– High Memory Grant needed as some other query might wait for memory grant. This can cause plan go serial
– 14 byte identifier added in case of triggers
What’s Remedy
– Compression ( Only in Enterprise Edition)
– Change datatype using script
– Always use backup Compression
My Notes 