0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Allan Xu
  • 7,998
  • 11
  • 51
  • 122
  • According to the MSDN, it suggests other data types for new work. This could mean that the feature could be deprecated in future releases, but they have not explicitly said that. https://msdn.microsoft.com/en-us/library/ms182418.aspx – dfundako Nov 09 '16 at 19:21
  • 2
    Or you could shell out for Enterprise edition if you haven't already and use page compression, which is likely to save far more than 4 bytes per row. `SMALLDATETIME` is really quite terrible due to its rounding issues. It doesn't just ignore seconds, it can make your timestamps jump into the future. There are very few logs where you could honestly say this reduction in accuracy doesn't matter. – Jeroen Mostert Nov 09 '16 at 19:31

2 Answers2

1

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.

Community
  • 1
  • 1
Shakeer Mirza
  • 5,054
  • 2
  • 18
  • 41
0

I honestly think that, if it's not broken, don't change it. At least, don't change it without a full scale investigation and a plan. Here's why:

  1. There could be dependencies based on the data type that you don't know about. If this is a mature product, ie it hasn't been created within the last couple of months, then you probably don't know everything that's hitting that column or columns that could break if you change the data type.

  2. The precision MIGHT be important to someone somewhere.

  3. Data saved. Yes, SmallDateTime is half the size of DateTime, but depending on how many records you have and how large the table is, that might not be that big of an issue for you. Yes, a smaller data size could potentially make things ever slightly faster.

DForck42
  • 19,789
  • 13
  • 59
  • 84