Incorrect Datatype Selection in SQL

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

– 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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s