I was spending some time over the last few weeks researching new features in SQL Server 2008 for a presentation I gave today at the Olympia Area SQL Server User Group in Tumwater, Washington, USA. So of course I had to take a closer look at the new date/time data types as part of this review.
Most everyone knows that there are a number of different date/time related data types coming out with SQL Server 2008. But for this BLOG post I want to focus in on just one new data type, DATETIME2. The new DATETIME2 data type is much like the existing DATETIME data type, except that it allows more percision for the time portion of the date/time. This new data type can now support time values that are accurate to 100 nanoseconds.
This new DATETIME2 data type allows you to specify how much percision you want to store in the time portion. The more percision you want the more disk space it take. A DATETIME2 data type takes from 6 to 8 bytes depending on the percision.
While I was building my examples for my user group presentation I built some queries that showed how much storage was taken for each of the different percisions. While building my DATALENGTH function calls for different DATETIME2 percisions I found out something amazing about the DATETIME2(3) format. If you use a DATETIME2(3) format for a date/time column instead of a DATETIME data type it stores the same data value but using one less byte. Thats right 7 bytes instead of 8.
Now I know saving 1 byte doesn't seem like a lot. But now multiple that by the number of different dates you might have in your 10 million record table. That savings of 1 byte could account for a substainal savings of disk space. I know disk space is cheap these days. But if you use less disk space to store your records then that equates to more records read per I/O, and faster query performance. So if you like the exiting percision of DATETIME, then remember to use DATETIME2(3) data type when you start building SQL Server 2008 applications.
Happy database designing,
Greg Larsen, MCITP