Next week on Wednesday 28 July 2021, I will be presenting a brand-new session titled “How SQL Server stores that data type” for the free EightKB virtual conference.
I’ve made it a 500-level talk, as reading hex and binary for fun requires a bit of mental arithmetic. Bob Ward, famous for his half-day brain-melting sessions from PASS Summits of yore is also doing a talk, and I figured I need to bring my A-game.
The sessions are a great selection of in-depth technical content about SQL Server internals. Along with my session, we’ve got Torsten Strauß talking about parallelism in Microsoft SQL Server; Jeff Moden talking about GUIDs in a way that defies all common beliefs regarding GUIDs and fragmentation (I’m really looking forward to this one); Bob Ward talking about SQL waits, latches, and spinlocks; and finally, Pam Lahoud with a deep dive into SQL Server on Azure Virtual Machine Storage.
You don’t want to miss this if you like SQL Server internals. There’s still time to register at eightkb.online. Please take note of the times, as they are listed in UTC. My session starts at 16:00 UTC, which is 10am Mountain.
The session was awesome, but I have one detailed question.
select cast(‘2000-01-01’ as datetime)
–result: 2000-01-01 00:00:00.000
select cast(‘2000-01-01’ as datetime) + 0.5
–result: 2000-01-01 12:00:00.000
select cast(‘2000-01-01’ as datetime) + 0.75
–result: 2000-01-01 18:00:00.000
Could it be that the fractional day calculation above is different than the actual stored time part that is an multiple of 300 ms?
Ahhh, your question is not what I was thinking about. This is covered in more detail by Jeff Moden’s SQL Spackle series on SQL Server Central I noted, but: if you do addition and subtraction on a DATETIME value, the integer value (before the decimal place) is the number of days, and the fraction portion will be a fraction of a day. Whereas, what I was referring to was how the fraction value of the time is persisted on disk. In other words, at 7 milliseconds past midnight on 1 January 1900, you’ll see 0x00000000 00000002 on disk (the space is a convenient way to separate the date from the time for the sake of this example).
I hope that clears things up!
yup… that was the answer i thought it would be. 🙂
Thanks for the clarification !
Theo:)
Randolph,
I absolutely loved your EightKB session! I’ve never heard you give a presentation before and, I have to say, you definitely have the voice for a presenter. I especially loved the binary details about pages (manipulation of and storage binary data was one of my first loves even back in high school in the ’60s) and also pertains to indexes so serious double treat for me. I was very happy to see a deeper dive on GUIDs than I’ve seen most others do and all of the rest of it was absolutely fascinating because of your impeccable order or revelation, your presentation style, and the raw knowledge you shared.
VERY well done and you can absolutely bet on me running through that awesome presentation a couple of times more. Thank you for the obvious work that you put into making it.
Heh… and thank you for not ripping me to shreds on my opinion of either the usefulness of the DATETIME datatype or the art of direct datetime manipulation, which so many other people seem to poo-poo doing. 😀
Truly high praise, Jeff. Thank you so much for your glowing review. I took great enjoyment and edification from your presentation as well, and promptly changed an older blog post about GUIDs on clustered indexes 😀. I have enjoyed our sparring in the comments previously, because you have given me a new perspective on why things are the way they are, which is invaluable.
Thank you for the feedback. I believe I found that change at the following.
https://bornsql.ca/blog/how-sql-server-stores-data-types-guid/
To be sure though, for Random GUIDs, I recommend rebuilding at 1% instead of 5%.
You might want to take a look at one more.
https://bornsql.ca/blog/clustered-indexes-clustering-keys-primary-keys/
And thank you for those updates to help others out. I’ve contacted a few other authors on this same subject (old blog updates) and most of them have simply and completely ignored the suggestion. Apparently, they don’t actually respond to comments made by others like you do (and THANK YOU for what you do on your blogs! They’re awesome!)
I’ve updated both posts. Thanks again. I completely forgot about the second one: it’s been a while since I wrote it, and advice certainly can change over 5 years.
Comments are closed.