By: Aaron Bertrand
In this video tutorial we will look at the different data types that SQL Server offers for storing date and times in a database table. We will compare the differences for the data types as well as do a storage test to determine the difference for each date data type.
Referenced Links in Video
- SQL Server Dates Tips (bit.ly/mssql-dates)
- Bad Habits Revival (bit.ly/sql-bad-habits)
- AT TIME ZONE – a favourite feature in SQL Server (bit.ly/at-time-zone)
Recommended Reading
- Date and Time Conversions Using SQL Server
- Format SQL Server Dates with FORMAT Function
- Add and Subtract Dates using DATEADD in SQL Server
- SQL Server Date and Time Functions with Examples
T-SQL Script
The below script will allow you to build the test table and load up the table with the various random dates as discussed in the video. You can also download the script.
-- This script creates 27 tables, with the 9 data types
-- specified in each of three formats (normal clustered
-- index, row compressed, and columnstore compressed).
-- Then it populates them with 1,000,000 rows, measures
-- space used, and drops them. You can run it over and
-- over, leaving no trace, unless you already have a
-- table named dbo.[does not exist] (you won't anymore).
-- I placed the resulting output script in a comment below,
-- but I find the generation more fun (and easier to perfect).
-- If you want to change any aspects (like using datetime2(3)),
-- you can do that here rather than search/replace in the
-- output, which is tedious and error-prone.
DECLARE @cr nchar(2) = nchar(13) + nchar(10);
DECLARE @dt nvarchar(max) = N'DROP TABLE IF EXISTS dbo.[does not exist]',
@ct nvarchar(max) = @cr, -- create table
@pt nvarchar(max) = @cr, -- populate table
@su nvarchar(max) = @cr, -- run sys.sp_spaceused
@sql nvarchar(max); -- assemble parts
;WITH data_types AS (
SELECT r,n,t FROM (VALUES
(1, 'Datetime2s_7', 'datetime2(7)'),
(2, 'Datetime2s_0', 'datetime2(0)'),
(3, 'Datetimes', 'datetime'),
(4, 'Smalldatetimes', 'smalldatetime'),
(5, 'Dates', 'date'),
(6, 'DatetimeOffsets_7', 'datetimeoffset(7)'),
(7, 'DatetimeOffsets_0', 'datetimeoffset(0)'),
(8, 'Times_7', 'time(7)'),
(9, 'Times_0', 'time(0)')
) AS dt(r,n,t)),
storage_types AS (
SELECT r,n,tsql FROM (VALUES
(1, 'regular', '(d));'),
(2, 'row', '(d)) WITH (DATA_COMPRESSION = ROW);'),
(3, 'columnstore', ' COLUMNSTORE);')
) AS st(r,n,tsql))
SELECT @dt += N', ' + @cr + N' dbo.' + dt.n + N'_' + st.n,
@ct += N'CREATE TABLE dbo.' + dt.n + N'_' + st.n
+ N'(d ' + dt.t + N', INDEX cix CLUSTERED' + st.tsql + @cr,
@pt += @cr + CASE WHEN st.r = 1 AND dt.r = 1 THEN
N';WITH x AS (SELECT n = 1 UNION ALL SELECT n+1 FROM x WHERE n < 1000000)
INSERT dbo.Datetime2s_7_regular(d) SELECT DATEADD(SECOND, n, sysutcdatetime())
FROM x OPTION (MAXRECURSION 0);' + @cr + N'ALTER TABLE dbo.Datetime2s_7_regular REBUILD;'
ELSE N'INSERT dbo.' + dt.n + N'_' + st.n + N'(d) SELECT d FROM dbo.Datetime2s_7_regular;' END,
@su += N'EXEC sys.sp_spaceused N''dbo.' + dt.n + N'_' + st.n + N''';' + @cr
FROM data_types AS dt CROSS JOIN storage_types AS st
ORDER BY st.r, dt.r;
SET @sql = N'SET NOCOUNT ON;' + @cr + @dt + ';' + @cr + @ct + @cr + @pt + @cr + @su + @cr + @dt;
EXEC sys.sp_executesql @sql;
/* resulting script
SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.[does not exist],
dbo.Datetime2s_7_regular,
dbo.Datetime2s_0_regular,
dbo.Datetimes_regular,
dbo.Smalldatetimes_regular,
dbo.Dates_regular,
dbo.DatetimeOffsets_7_regular,
dbo.DatetimeOffsets_0_regular,
dbo.Times_7_regular,
dbo.Times_0_regular,
dbo.Datetime2s_7_row,
dbo.Datetime2s_0_row,
dbo.Datetimes_row,
dbo.Smalldatetimes_row,
dbo.Dates_row,
dbo.DatetimeOffsets_7_row,
dbo.DatetimeOffsets_0_row,
dbo.Times_7_row,
dbo.Times_0_row,
dbo.Datetime2s_7_columnstore,
dbo.Datetime2s_0_columnstore,
dbo.Datetimes_columnstore,
dbo.Smalldatetimes_columnstore,
dbo.Dates_columnstore,
dbo.DatetimeOffsets_7_columnstore,
dbo.DatetimeOffsets_0_columnstore,
dbo.Times_7_columnstore,
dbo.Times_0_columnstore;
CREATE TABLE dbo.Datetime2s_7_regular(d datetime2(7), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Datetime2s_0_regular(d datetime2(0), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Datetimes_regular(d datetime, INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Smalldatetimes_regular(d smalldatetime, INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Dates_regular(d date, INDEX cix CLUSTERED(d));
CREATE TABLE dbo.DatetimeOffsets_7_regular(d datetimeoffset(7), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.DatetimeOffsets_0_regular(d datetimeoffset(0), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Times_7_regular(d time(7), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Times_0_regular(d time(0), INDEX cix CLUSTERED(d));
CREATE TABLE dbo.Datetime2s_7_row(d datetime2(7), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Datetime2s_0_row(d datetime2(0), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Datetimes_row(d datetime, INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Smalldatetimes_row(d smalldatetime, INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Dates_row(d date, INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.DatetimeOffsets_7_row(d datetimeoffset(7), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.DatetimeOffsets_0_row(d datetimeoffset(0), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Times_7_row(d time(7), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Times_0_row(d time(0), INDEX cix CLUSTERED(d)) WITH (DATA_COMPRESSION = ROW);
CREATE TABLE dbo.Datetime2s_7_columnstore(d datetime2(7), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Datetime2s_0_columnstore(d datetime2(0), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Datetimes_columnstore(d datetime, INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Smalldatetimes_columnstore(d smalldatetime, INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Dates_columnstore(d date, INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.DatetimeOffsets_7_columnstore(d datetimeoffset(7), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.DatetimeOffsets_0_columnstore(d datetimeoffset(0), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Times_7_columnstore(d time(7), INDEX cix CLUSTERED COLUMNSTORE);
CREATE TABLE dbo.Times_0_columnstore(d time(0), INDEX cix CLUSTERED COLUMNSTORE);
;WITH x AS (SELECT n = 1 UNION ALL SELECT n+1 FROM x WHERE n < 1000000)
INSERT dbo.Datetime2s_7_regular(d) SELECT DATEADD(SECOND, n, sysutcdatetime())
FROM x OPTION (MAXRECURSION 0);
ALTER TABLE dbo.Datetime2s_7_regular REBUILD;
INSERT dbo.Datetime2s_0_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetimes_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Smalldatetimes_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Dates_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_7_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_0_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_7_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_0_regular(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetime2s_7_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetime2s_0_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetimes_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Smalldatetimes_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Dates_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_7_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_0_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_7_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_0_row(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetime2s_7_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetime2s_0_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Datetimes_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Smalldatetimes_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Dates_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_7_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.DatetimeOffsets_0_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_7_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
INSERT dbo.Times_0_columnstore(d) SELECT d FROM dbo.Datetime2s_7_regular;
EXEC sys.sp_spaceused N'dbo.Datetime2s_7_regular';
EXEC sys.sp_spaceused N'dbo.Datetime2s_0_regular';
EXEC sys.sp_spaceused N'dbo.Datetimes_regular';
EXEC sys.sp_spaceused N'dbo.Smalldatetimes_regular';
EXEC sys.sp_spaceused N'dbo.Dates_regular';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_7_regular';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_0_regular';
EXEC sys.sp_spaceused N'dbo.Times_7_regular';
EXEC sys.sp_spaceused N'dbo.Times_0_regular';
EXEC sys.sp_spaceused N'dbo.Datetime2s_7_row';
EXEC sys.sp_spaceused N'dbo.Datetime2s_0_row';
EXEC sys.sp_spaceused N'dbo.Datetimes_row';
EXEC sys.sp_spaceused N'dbo.Smalldatetimes_row';
EXEC sys.sp_spaceused N'dbo.Dates_row';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_7_row';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_0_row';
EXEC sys.sp_spaceused N'dbo.Times_7_row';
EXEC sys.sp_spaceused N'dbo.Times_0_row';
EXEC sys.sp_spaceused N'dbo.Datetime2s_7_columnstore';
EXEC sys.sp_spaceused N'dbo.Datetime2s_0_columnstore';
EXEC sys.sp_spaceused N'dbo.Datetimes_columnstore';
EXEC sys.sp_spaceused N'dbo.Smalldatetimes_columnstore';
EXEC sys.sp_spaceused N'dbo.Dates_columnstore';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_7_columnstore';
EXEC sys.sp_spaceused N'dbo.DatetimeOffsets_0_columnstore';
EXEC sys.sp_spaceused N'dbo.Times_7_columnstore';
EXEC sys.sp_spaceused N'dbo.Times_0_columnstore';
DROP TABLE IF EXISTS dbo.[does not exist],
dbo.Datetime2s_7_regular,
dbo.Datetime2s_0_regular,
dbo.Datetimes_regular,
dbo.Smalldatetimes_regular,
dbo.Dates_regular,
dbo.DatetimeOffsets_7_regular,
dbo.DatetimeOffsets_0_regular,
dbo.Times_7_regular,
dbo.Times_0_regular,
dbo.Datetime2s_7_row,
dbo.Datetime2s_0_row,
dbo.Datetimes_row,
dbo.Smalldatetimes_row,
dbo.Dates_row,
dbo.DatetimeOffsets_7_row,
dbo.DatetimeOffsets_0_row,
dbo.Times_7_row,
dbo.Times_0_row,
dbo.Datetime2s_7_columnstore,
dbo.Datetime2s_0_columnstore,
dbo.Datetimes_columnstore,
dbo.Smalldatetimes_columnstore,
dbo.Dates_columnstore,
dbo.DatetimeOffsets_7_columnstore,
dbo.DatetimeOffsets_0_columnstore,
dbo.Times_7_columnstore,
dbo.Times_0_columnstore;
*/Video Transcript
Hi, I'm Aaron Bertrand with a series of short videos for MSSQLTips.com. Today I wanted to talk a little bit about when to use each of the date/time datatypes.
So what are those datatypes?
We have the original that shipped with the very first version of SQL Server I ever used, datetime and smalldatetime. And then in 2008, a bunch of new types were added. One was date that stored only the date with no time component, and datetime2 which was more precise than datetime at no additional storage cost, and in some cases, lower storage cost. Then we have datetimeoffset which is like datetime2, but it also stores the offset from UTC. A little trick about that one though is that at least initially, it was not DST aware, so if you needed to keep track of DST, that wasn't handled automatically for you. And then there was time which was analogous to date, so this is time without the date component.
Some of the more detailed particulars about these datatypes.
So datetime has a precision of about 3 milliseconds and requires eight bytes of storage. The values started in 1753 because of some odd calendar things that happened in 1752 and that go all the way until 9,000 years from now or whatever.
Smalldatetime has a granularity of one minute, so if you didn't care about seconds, that's probably the one that you would choose for a savings of four bytes. The problem is that those only go back to 1900, and they only go about 60 years into the future today. I would say that 99% of the usage I've seen, at least prior to 2008, was datetime. I didn't see a whole lot of smalldatetime out in the world. Then in the 2008 type, so date, granularity of one day. Storage is only three bytes, so this is a much better choice if you're only storing the date and the time component is midnight or you don't care about the time and you've never going to use it, much better to use date.
Datetime2 avoids the 3 millisecond rounding issue so you can store much more granular values, but you could also store less granular values than datetime where you only store the second, and you don't pay for the additional storage of the sub-millisecond values or microsecond values. You can save about two bytes per row using that, which doesn't sound like a lot, but we'll get there.
Datetimeoffset is the same as datetime2 but it requires an additional two bytes for that offset value. And then time which is three to five bytes depending on the precision that you want to use.
So what I did to demonstrate, or to try to at least figure out how much does using a different datatype actually save you? So I created a bunch of different tables with a million rows of the same original values. The source values were the same, so I took a datetime2 with seven milliseconds, or seven subseconds of precision. Seven decimal places after the seconds and then created a million values by adding a second to each successive value. So I had a million distinct values of datetime2 seven, put them in a table and then fed a bunch of other tables, all of which had each of the different datatypes. And so just to calculate how much storage space a million rows would take up.
And these were the results, so datetime was 38 megabytes. And then with row compression, it was 28. And with columnstore compression, it was 7.8 megabytes, so you get a really, really good compression ratio for some of these values.
Smalldatetime, you would think it would be half the storage space, but it actually wasn't. You don't quite get half the storage space, there's just a lot of... Just the different way that those values are stored.
The most surprising one to me was when you use the date values because there were so few unique values, adding a million seconds and then... Or adding a second to a million different values and then truncating all the hours, and minutes, and seconds anyway so that they would all truncate to a very small number of date values, and we see for date down here, the columnstore compression of the date values, even though the raw was in the same vicinity as all the other datatypes, columnstore compression, there was a really, really big anomaly there where we have compressed way better.
The point of this though is that if you look at the raw values or even the row compressed values, the difference in storage when you're storing a million rows really isn't that... isn't really that significant. The issue comes when you're storing a billion rows, or you have 12 columns that are storing datetime information, these little deltas in how much storage space you pay really add up. And again, it's not about the storage per se, it's about how much these values take up in memory when you load these tables into the buffer pool. So that's the bigger concern there for me.
So anyway, this is easy to test, I can share these scripts.
So some general guidance about datetime types if you care about precision, so if that 3 milliseconds is a pain for you, or you want to use a little bit less space by using less precision or even the same amount of precision you have with datetime, use datetime2 for new development. If you only care about time, use time. There's no reason to store a datetime value, so you can always ignore the date. Similarly, if you only care about date, don't store a time value, or midnight, that you're just going to ignore and throw away.
Now the caveat there, if you ever plan to combine them and try to merge these two things together, so we have the date separately and the time separately, and you say, "Well, what does it look like when it's the date and the time?" Just use datetime2, it's much easier to extract the date and extract the time from a datetime2 value than it is to build one out of the date and the time separately.
If you care about the origin timezone, so this isn't always the case, but if you need to know what was the value, what was the datetime value in UTC but what was the original timezone for that data, use datetimeoffset. So again, this isn't DST aware. You've got to build DST awareness around this. Or if you're on 2016 and above, you can use AT TIME ZONE. Rob Farley has a great post about AT TIME ZONE. I can't even think in those terms, so I'll have a link at the end of the video to show you about that.
My personal preference, I would rather store the data in UTC always. Then there are no gaps, I don't care what timezone, I don't care about DST, there's no duplication. You get gaps when the clocks change, you get duplication at the other end of the season, and it's just a mess, I would rather have all my servers running in UTC and storing UTC. It's much easier to translate a UTC value to some specific timezone than to translate a whole bunch of different origin timezone data into some consistent datetime range.
Of course, I'm suggesting all this, but it's not that easy to just change. You can't just go and change all your datetime columns to date or datetime2, so that is more for new development for an existing schema, unless you do plan to go and change all of those, and if you need to stay backward compatible, just use datetime or smalldatetime.
I do get questions and I do see people struggling with this all the time. People want to store their specific regional format in a table so they want to use string data. Or they're on an older version of SQL Server, SQL Server 2005 it would have to be, where they want to store just the date and not the time, and they would store as a character. Or the data warehouse folks who really want, because some textbook said so, they really want to store a date as an integer, which I'm happy to have that debate outside of this context. But my advice for this, if you are storing date or time data and you think you want to use a datatype that isn't in the date or time set, my answer is no. Please don't do this, you lose validation, you lose all the builtin functionality. Just you lose a lot from that.
So I want to thank you for watching this video. I'll have more coming soon. Some quick links for you to use. I made nice little Bit.ly links so it would be easy to copy and remember. MSSSQL-dates, that is all of the date-related or the date category tips here at MSSQLTips.com. SQL-bad-habits, that's a link to what I call my Bad Habits Revival, which is just an index of all the bad habits and best practices blog posts that I've written. A lot of those have to do with date and time datatypes. And then the article I referenced that Rob Farley wrote about AT TIME ZONE is at at-time-zone. There are other resources below, thanks again for watching, and I'll see you in the next video.
