Prevent Data Loss when Changing Datatypes

Understanding data types in SQL server is required for anyone who is concerned with their database's functionality and/or performance (pick your metric). Understanding the differences and properties of these datatypes is useful for anyone who works with the database: dba, datmodeler, or developer. The time it takes to learn about the datatypes in depth (down to the internal) is time well spent.

Choosing SMALLDATETIME over DATETIME can cause a 1s error due to rounding while choosing DATETIME over SMALLDATETIME will double the size requirements for that column (if you think 2bytes per row is trivial, you've never worked on a large db :p).

Likewise, making schema changes to production databases must be well thought out and datatypes must be understood in order to prevent data loss.

A simple way to prevent data loss is to test out your proposed schema changes in a small temp table. By turning on the STATISTICS I/O option for the session will allow you to view the I/O of the change. If no I/O is reported, you'll be assured that the change didn't touch the base data; the operation will be fast. If your change doesn't require physical access to the base data you can be reassured that you aren't losing any of your valuable (?) data.