SQL Server smalldatetime vs datetime2
I've read several articles about using data types that support dates in SQL Server but I'm still undecided about what kind of data to use.
All the people says to use the new and powerful datetime2 type but smalldatetime is still more convenient in terms of memory (6 bytes vs 4 bytes)
This is my scope:
A table defined in this way:
Date: typeof(<Date type to choose>)
Value1: typeof(int)
Value2: typeof(int)
Value3: typeof(int)
Then I have these requirements:
for the Date column, I need precision up to minute.
In this table I will store up to 1 million records every day so big big data.
My question is: do I have to use the old smalldatetype that saves me 2 mb every day?
OR
Should I use the new and powerful datetime2 datatype?
Thanks
Answer
datetime2(2) is 6 bytes and gets you not just seconds but up to 2 places of milliseconds (00:00:00.00 through 23:59:59.99).
smalldatetime is 4 bytes and has the potential to save you 2 bytes per row, if this column is part of a clustering key then those 2 bytes saved will have an impact on each nonclustered index.
One important feature of smalldatetime is that it rounds to the nearest minutes instead of truncating the seconds. Depending on how the data is inserted into the table and if this behavior is desired or not, you may need additional preproccessing of this value prior to inserting it into the table.
Example:
create table t (sdt smalldatetime, dt2 datetime2(2))
insert into t values
('2017-01-01T11:22:22.33','2017-01-01T11:22:22.33')
,('2017-01-01T11:22:33.33','2017-01-01T11:22:33.33')
select
sdt = convert(char(23),sdt,121)
, dt2 = convert(char(23),dt2,121)
from t
rextester demo: http://rextester.com/JPMEE57778
returns:
+-------------------------+-------------------------+
| sdt | dt2 |
+-------------------------+-------------------------+
| 2017-01-01 11:22:00.000 | 2017-01-01 11:22:22.33 |
| 2017-01-01 11:23:00.000 | 2017-01-01 11:22:33.33 |
+-------------------------+-------------------------+
I really dislike this rounding, so that would probably be enough for me to go with datetime2(2) despite the 2mb per day savings unless it was part of the clustering key.
Reference: