Using SQL Server 2014:
I am working with a few large log tables and one of their columns is CreateDate of datatype DateTime.
There are a few indexes based on the CreateDate columns. All tables already include large number to rows.
Question #1: is there any good reason not to convert all of the CreateDate columns to SMALLDATETIME type?
Question #2: is there any safe alter table table statement that converts existing DATETIME data to SMALLDATETIME without any error?
Question #3: when I use ALTER TABLE, what happens to existing indexes based on CreateDate?
Yes you can use Small date time datatype for your case.
The warning when dealing with smalldatetime
Smalldatetime will have 0 seconds every time with no milliseconds. But be aware that small date time will round the minute value to nearest minute value.
Example if your datetime column contains value like '2016-08-06 11:49:31.667'. Then small date time value will be '2016-08-06 11:50'. It has rounded the datetime to nearest value 50th minute, where it is actually 49th minute.
The way I think the best to modify data
My advice is to create one more column with smalldatetime datatype. And update it by converting your datetime column to smalldatetime. And drop your datetime column.
Indexers part when Changing datatype
Coming to Indexers. You could create the same indexers on this column. Up to my knowledge It is good to have Indexer on Smalldatetime than having it on datetime. Because datetime value differ in max of each column with milli seconds.
I hope this info might be help you.