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 that needs to hold the 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, process, purchase, and fulfillment dates.
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 we can have fractional seconds with precision from 1 to 7 after the seconds. Depending on how much you 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. Depending on your throughput, This will take longer on your SAN.
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 use 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 10 Billion DateTime records in your database, which is 74.5GB. If you only need seconds, you could convert and save about 18.6GB. If you needed three precision, 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 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, and let me know how much space you save. Let me know in the comments below. I'd love to know!