Sql-server – Should I change the old datetime columns from SQL Server 2005 to just date datatype for SQL Server 2012

date formatsql serversql-server-2012

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.

Best Answer

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.

Related Question