Data Types - Does size really matter? - DateTime

How many of us have just created a new table and put a Created Datetime and Modified Datetime field in with the default DateTime datatype? How many of us have to create a DateTime field for a column which just needs to hold date only?

Some of you may say, 'oh, it's just a DateTime column. How bad can it be?'

Well, let's break it down a little and provide perspective.

Most tables have 1-2 DateTime fields at a minimum. A create date, modified date. Other fields might be order date, process date, purchase date, fulfill.

A DateTime datatype column with a date/time stored in it is 8 bytes and looks like this: 

DateTime                                  Data Length

2021-03-21 23:11:53.440          8

A DateTime2 datatype column with a date/time stored in it could be 6, 7, or 8 bytes and look like this:

DateTime	Data Length
2021-03-21 23:11:53	6
2021-03-21 23:11:53.4	6
2021-03-21 23:11:53.44	6
2021-03-21 23:11:53.443	7
2021-03-21 23:11:53.4428	7
2021-03-21 23:11:53.44278	8
2021-03-21 23:11:53.442781	8
2021-03-21 23:11:53.4427813	8

Notice how after the seconds, we can have fractional seconds with precision from 1 to 7. Depending on how much you actually need, you can trim down your precision which trims down your data size.

If you only need seconds, you can save 2 bytes per record. This adds up. 

Let's run through a quick real-world example.

Table 1 has just over 513 million records (513,968,384), and we are using the data type DateTime.

The table size is 3,921.26MB which is 3.82GB.

Backing up Table 1, which had 1,340,410 pages, took 9.331 seconds at 1,122.275 MB/sec  - Keep in mind this is a very fast NVME drive. This will most likely take longer on your SAN, depending on your throughput.

Restoring Table 1 which had 1,340,410 pages took 7.162 seconds at 1,462.154 MB/sec.

Table 2 has the same 513 million records, but we are using DateTime2 with zero (0) precision.

The table size is 2,940.95MB which is 2.87GB.

Backing up table 2 which had 1,211,426 pages took 8.260 seconds at 1,145.794 MB/sec.

Restoring table 2 which had 1,211,426 pages took 6.872 seconds at 1,377.220 MB/sec.

For one column, we see about 1 GB in space savings and time saved in backup/restore.

Now find all of the DateTime columns and count the records in your database, and then you can calculate how much potential space savings you may be able to save.

Here is a query that will count all rows for all columns in the database.

SELECT
    SUM(ps.row_count) AS TotalRows
FROM
    sys.tables AS t
    INNER JOIN sys.schemas AS s
        ON s.schema_id = t.schema_id
    INNER JOIN sys.columns AS c
        ON c.object_id = t.object_id
    INNER JOIN sys.dm_db_partition_stats AS ps
        ON ps.object_id = t.object_id
WHERE
    TYPE_NAME(c.user_type_id) = 'datetime'
GROUP BY
    TYPE_NAME(c.user_type_id);

Depending on your needs, you might only need seconds, so you can go from 8 to 6 bytes. If you need more precision or at least the same as DateTime, you could go from 8 to 7 bytes.

Let's look at it now as a whole. Let's say you have a total of 10 Billion DateTime records in your database, which is 74.5GB. If you were to only need seconds, you could convert and save about 18.6GB. If you needed 3 precision which is the same as the current DateTime, you would save 9.31GB. Now multiply your savings for each full backup you make. That's quite a bit of disk savings.

We can even take this a step further if you haven't yet and add data compression at the page level for both the table and the clustered index.

Here is a comparison of the backup and restore between DateTime and DateTime2(0).

Backing up DateTime, which had 1,340,410 pages, took 9.331 seconds at 1,122.275 MB/sec.

Backing up DateTime2(0) Compressed 866,154 pages took 6.901 seconds at 980.557 MB/sec.

Restoring DateTime which had 1,340,410 pages took 7.162 seconds at 1,462.154 MB/sec.

Restoring DateTime2(0) Compressed 866,154 pages took 5.115 seconds at 1322.937 MB/sec.

Try it, let me how much space you save. Let me know in the comments below. I'd love to know!