SQL Server smalldatetime vs datetime2
Solution 1
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:
smalldatetime
datetime2
- Disk space is cheap - that is not the point! - Kimberly Tripp
- Ever-increasing clustering key – the Clustered Index Debate……….again! - Kimberly Tripp
- The Clustered Index Debate Continues… - Kimberly Tripp
Solution 2
Date ranges : smalldatetime: 1900-01-01 through 2079-06-06 and datetime2: 0001-01-01 through 9999-12-31
Storage :If you don't need seconds, just hours and minutes then storage is like smalldatetime at 4 bytes, as opposed to datetime2(0) at 6 bytes.
datetime2 is an upgrade in range of values, precision (no rounding!), so you should go with datetime2 for big data.
Oscar Peli
iPhone developer. If you need iPhone/iPod/iPad apps just look to my site: http://iphone.metaextender.com
Updated on June 29, 2022Comments
-
Oscar Peli about 2 years
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 butsmalldatetime
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