Datetime data types
-
Comments posted to this topic are about the item Datetime data types
-
This was removed by the editor as SPAM
-
Good question. I still like the datetime data type.
-
Nice one to make us think about the use of datetime2 and precision. It's too much of a habit to use datetime after all these years.
-
Of the three options posted, I have to disagree that one is more correct than another based on the givens in this question.
Granted that a precision of less than three on the datetime2 will use only 6 bytes, this is only part of the answer - imho.
Running eight different configurations and just comparing the data type size, I have the following:
In the attached image, dto = datetimeoffset, dt = datetime, dt2 = datetime2, and prec2 is used to denote the use of a precision of 2 to force the 6 byte size.
I have circled in red the dt2_prec2 data size (used data in the reserved pages). And yes, this by default is initially smaller than the other tables by almost 200kb for 100,000 rows.
But consider that the datetime without compression has a lower overall reserved page size than either of the uncompressed datetime2 tables. This can be construed as using the least amount of storage because it is fewer pages and therefor smaller. And for the record, all of these have had a clustered index created after the table was populated.
The bigger issue is highlighted in green and yellow. In yellow, we see that the datetimeoffset compressed table requires the same number of pages as the datetime2 table that is compressed. Now take a look at the green where all of the used data is the same for all of the compressed tables - regardless of the precision or data type. This is another one of those areas where the interpretation can be that all of these tables require the same storage because they compress equally.
I am left with a conundrum in that while I believe the wanted answer is datetime2, I don't believe there is a correct answer listed with the present wording.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Nice work, Jason. I like the way you questioned BOL and looked at actual data to find the answer that lies beneath the answer.
-
Ed Wagner (12/4/2015)
Nice work, Jason. I like the way you questioned BOL and looked at actual data to find the answer that lies beneath the answer.Thanks Ed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (12/4/2015)
Of the three options posted, I have to disagree that one is more correct than another based on the givens in this question.Granted that a precision of less than three on the datetime2 will use only 6 bytes, this is only part of the answer - imho.
Running eight different configurations and just comparing the data type size, I have the following:
In the attached image, dto = datetimeoffset, dt = datetime, dt2 = datetime2, and prec2 is used to denote the use of a precision of 2 to force the 6 byte size.
I have circled in red the dt2_prec2 data size (used data in the reserved pages). And yes, this by default is initially smaller than the other tables by almost 200kb for 100,000 rows.
But consider that the datetime without compression has a lower overall reserved page size than either of the uncompressed datetime2 tables. This can be construed as using the least amount of storage because it is fewer pages and therefor smaller. And for the record, all of these have had a clustered index created after the table was populated.
The bigger issue is highlighted in green and yellow. In yellow, we see that the datetimeoffset compressed table requires the same number of pages as the datetime2 table that is compressed. Now take a look at the green where all of the used data is the same for all of the compressed tables - regardless of the precision or data type. This is another one of those areas where the interpretation can be that all of these tables require the same storage because they compress equally.
I am left with a conundrum in that while I believe the wanted answer is datetime2, I don't believe there is a correct answer listed with the present wording.
Thanks for your post which brought me awesome information about a nice question ( an easy one as often for the end of the week ). When SQL Server 2012 was released , I have done the same tests and I was surprised that compression was rubbing the difference of size suggested by the BOL between datetime and datetime2 data types. But compression is supported only with the Entreprise ( and Developer , my favorite ) editions. For the Express editions , the difference can be seen. My good remembrance helps me to find the good answer.
Have a good week-end for everybody.
-
Nice learning question, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Haven't used the datetime2.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply