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

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

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s