SQL Server Performance

Save Disk Space with SQL Server 2008 DATETIME2 Data Type

Discussion in 'Greg Larsen's Blog' started by Greg Larsen, Mar 19, 2008.

  1. Greg Larsen New Member

    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
  2. BrianH123 New Member

    What is "percision"? I've never seen that word before. I know it's not a typo, since you spelled it like that seven times. Also, what does "substainal" mean. What does "now multiple that" mean?In the event you're a smart person who just can't spell, have you considered using spell-check?
  3. Greg Larsen New Member

    Sorry it took so long to get back to you. Percision is the number of digits to the right of the decimal point, and represent fractions of seconds.
  4. RickNZ New Member

    I think you mean "precision" ...

Share This Page