CODEWITHSUNDEEP
sql-serversmalldatetime
Allan Xu
Allan Xu

Reputation: 9298

Any good reason not using SMALLDATETIME when I don't need to store seconds?

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?

Upvotes: 0

Views: 1299

Answers (2)

DForck42
DForck42

Reputation: 20357

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.

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

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.

Upvotes: 1

Related Questions