3

My database was developed in 2005 for SQL Server 2005 version.

In that time for columns which store pure date without time we used datetime data type.
Our database has survived up today and now we are using SQL Server 2012.

Should I consider to change my columns from datetime to just date in places where I do not need to store time?

I am afraid what will happen with indexes on columns which changing type from datetime to date, what will happen whit statistic. Why I am thinking about changing datatype is because I think that I can gain more performance with just date type, also I want to save some space ? Does exist anywhere some general guidelines about switching from datetime to date. Is there really huge differences in performances between these two types.

2

In SQL Server 2012, date takes 3 bytes while datetime takes 8 bytes. If your table has millions of millions of rows, then yes, then the IO and logical page size should be reduced and your performance will be quicker.

First though i'd suggest to try dumping the table into a testing table, convert the column, apply your indexes and benchmark using real life workloads against this table using the original as a baseline.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.