November 12, 2024 at 7:00 am
This is probably Microsoft pushing back to try and force JEFF to move from datetime to datetime2 ๐
November 12, 2024 at 8:36 am
bump.. page bug
November 12, 2024 at 2:04 pm
Well that was one heck of a three days...
First Summit Seattle under my belt and I'm both burned out and still geeked.ย I wasn't able to attend even 1/2 the sessions I wanted to, just because I still haven't figured out how to be in 3 or 4 places at once.ย Honestly, Friday I had to bail from my final scheduled session early to go back to my hotel and unwind / crash / zone out for a while.
Tuesday, I get to start selling the bosses on not only sending me back next year, but pushing some of the developers to go both from our Org and other Orgs within the company.
Maybe next year, too, I'll spring for an extra night or two out-of-pocket so I can actually see more of Seattle than the two blocks between my hotel and the convention building.ย (joking / not joking)
BTW, Grant KILTS REPRESENT!ย ๐
(Somehow, I posted this in the Fantasy Football topic...)
November 12, 2024 at 2:59 pm
(Somehow, I posted this in the Fantasy Football topic...)
I think that shows you how burned out you still are. ๐
I read a couple of Steve's posts about it last week, and he seemed to be really enjoying it there. From what I've seen other people's posts and social media, they were having a good time too. I saw a couple of pictures of Grant in his Kilt on there too, he looked great!
I completely forgot to register for the live stream, so when I did (a little before the initial keynote) it took 2 days for my email to arrive and I completely missed it all.
Honestly, would be something I'd love to go to, but not sure I could get the sign-off from the business for the trans-atlantic flight. SQL Bits 2025 is signed off at least, but it's still not actually announced when and where. Hopefully it's soonโข, and maybe I'll catch a few of you there.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 12, 2024 at 3:28 pm
Heh, I originally posted my comment on Sunday morning, it took me until today to realize it was in the wrong topic!
It's definitely a worthwhile event I feel, and I don't think I'm going to have to work too hard to sell the bosses on going next year.
One of the things they announced is in 2025 they're taking Summit on the road so perhaps you could convince your bosses to send you to the (planned) Netherlands event:
PASS Summit On Tour will see us host smaller scale events aligning with the data communities in New York, Dallas and the Netherlands in 2025.
November 12, 2024 at 4:36 pm
I cannot sufficiently nor politely express the growing level of disgust I've developed about Microsoft and SQL Server, especially since 2012 sp3.
I'd written some simple code to determine a difference between the millisecond DATEPART of two dates with times and it was missing the very specific test examples I created.ย Then I found out why (major facepalm, head-desk 3X).
THE FOLLOWING CODE CHANGES THE COMPATABILITY LEVEL OF THE CURRENT DATABASE AND WILL NEED TO BE MANUALLY CHANGED BACK BECAUSE THE GO's ARE REQUIRED TO MAKE IT WORK.
Here is the simplest form to explain the reason why the test failed.ย To summarize, the code works as expected in 2014 or less and fails since they made the "breaking change" supposed accuracy "improvements" to the DATETIME datatype in 2016 and up.
SELECT CurrentCompatibilityLevel = compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
---------------------------------------------------------------------------------
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 120 --2014
GO
SELECT SqlServer2014_DT = DATEPART(ms,CONVERT(DATETIME ,'2022-07-03 13:41:52.007'))
,SqlServer2014_DT2 = DATEPART(ms,CONVERT(DATETIME2,'2022-07-03 13:41:52.0071265'));
GO
---------------------------------------------------------------------------------
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130 --2016
GO
SELECT SqlServer2016_DT = DATEPART(ms,CONVERT(DATETIME ,'2022-07-03 13:41:52.007'))
,SqlServer2016_DT2 = DATEPART(ms,CONVERT(DATETIME2,'2022-07-03 13:41:52.0071265'));
GOHere are the results.ย See the problem?ย "Well done" Microsoft. ๐
I gather that the data type has not changed. The suspect is the datepart function. The binary values are the same in both compatibility levels, 0x0000AEC700E1BB82 and 0x0761263FD07222440B, respectively. Mind you, the former is a small-endian float format, and the latter is a big-endian variable-length format. Another twist is that DATETIME2 is the default DATETIME2(7) if the precision is omitted. One could play around with this but to your point, it is an unwanted change in functionality, yet another M$ pita.
๐
November 12, 2024 at 4:43 pm
He he, 2022-07-03 13:41:52.0070000 is now unequal to 2022-07-03 13:41:52.007 depending on the data type ๐
๐
November 12, 2024 at 4:44 pm
Yep... I'm well aware of the default precision for DATETIME2 being 7.ย That's the whole thing... I'm writing code to check forย properย conversions from DATETIME2 (default of 7 for resolution, which is also indicated by the data I provided).
To the best of my knowledge, DATETIME is NOT represented by a binary like DATETIME2 is but I'll need to double check.
And, when I say the broke the DATETIME datatype, I DO mean that I don't care if it's the DATEPART code or the actual datatype.ย The result is the same... a totally incorrect answer for the DATETIME datatype code.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2024 at 4:50 pm
He he, 2022-07-03 13:41:52.0070000 is now unequal to 2022-07-03 13:41:52.007 depending on the data type ๐ ๐
Show your code, ol' friend, so that people don't just look at the final 4 digits and say "of course they're different".ย And, yeah... that's what I'm getting at in my post... MS broke some serious stuff when they released their "breaking change" in 2016, which put the screws to other things, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2024 at 5:12 pm
Eirikur Eiriksson wrote:He he, 2022-07-03 13:41:52.0070000 is now unequal to 2022-07-03 13:41:52.007 depending on the data type ๐ ๐
Show your code, ol' friend, so that people don't just look at the final 4 digits and say "of course they're different".ย And, yeah... that's what I'm getting at in my post... MS broke some serious stuff when they released their "breaking change" in 2016, which put the screws to other things, as well.
I am curious about the use case where this degree of precision will make a difference.
I am wondering if MS is doing things now that MySQL did years ago.
In 2002-2003, the idea of using MySQL to save on licensing was put forth. I built the MySQL server, did a data conversion, ran the various processes, and executed the reports to compare numbers.ย They didn't match.ย I spent days looking at my conversion scripts. What did I do wrong?ย These were straight data dumps.
PCWeek ( I am seriously dating myself) did a test to determine the fastest database between SQL, Oracle, MySQL, and DB2.ย It was a Java front end.ย MySQL won, SQL Server was dead last.ย The explanation for the speed of MySQL, and I can't remember the specifics, was that internally it truncated decimal numbers.
The re-wrote the front end using .Net.ย SQL Server blew the rest of them away.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 12, 2024 at 5:35 pm
Eirikur Eiriksson wrote:He he, 2022-07-03 13:41:52.0070000 is now unequal to 2022-07-03 13:41:52.007 depending on the data type ๐ ๐
Show your code, ol' friend, so that people don't just look at the final 4 digits and say "of course they're different".ย And, yeah... that's what I'm getting at in my post... MS broke some serious stuff when they released their "breaking change" in 2016, which put the screws to other things, as well.
Just ran your code Jeff and replaced the last four digits in compatibility level 130.
๐
Whipping up a couple of CTEs with a join on the values is elementary ๐
November 12, 2024 at 6:13 pm
I will elaborate Jeff on the subject shortly.
๐
Certain that there is a bug there, the microsecond rounding used to work for the datatime data type, interesting to see if other float based data types are affected. Feels like going into a restaurant and getting a different meal from the the same item on the menu depending of the time of order..... ;-(
November 12, 2024 at 7:46 pm
Well that was one heck of a three days...
First Summit Seattle under my belt and I'm both burned out and still geeked.ย I wasn't able to attend even 1/2 the sessions I wanted to, just because I still haven't figured out how to be in 3 or 4 places at once.ย Honestly, Friday I had to bail from my final scheduled session early to go back to my hotel and unwind / crash / zone out for a while.
Tuesday, I get to start selling the bosses on not only sending me back next year, but pushing some of the developers to go both from our Org and other Orgs within the company.
Maybe next year, too, I'll spring for an extra night or two out-of-pocket so I can actually see more of Seattle than the two blocks between my hotel and the convention building.ย (joking / not joking)
BTW, Grant KILTS REPRESENT!ย ๐
(Somehow, I posted this in the Fantasy Football topic...)
Glad you enjoyed it. A lot of fun for me, though this was me late Friday. Had 30 minutes before the wrap meeting and I was done.
November 12, 2024 at 7:51 pm
It's interesting to talk with people using PostgreSQL (mostly) and MySQL (a few). Also CosmosDB and others.
Many of them find SQL Server to work better and they enjoy it. My view is that if you are very price sensitive, you just deal with PG/MySQL/etc., or if you have devs that don't want to bother maintaining a schema, they go to Mongo/Cosmos/pick-your-key-value-store, and then they don't think about all the tech debt in C#/Java to maintain the schema on read.
SQL Server isn't growing as fast as some others, but from what I see talking to lots of people, ORCL and SQL Server still rule in many places, and in many important systems. Just like DB2 is still used in legacy systems that can't easily be rewritten to use something else.
Still lots of career opportunities in this space, despite the issues MS has added. I don't know how many people need the precision Jeff sees issues with. I think most people just need dates and are glad we have that type.
November 12, 2024 at 8:36 pm
Well that was one heck of a three days...
First Summit Seattle under my belt and I'm both burned out and still geeked.ย I wasn't able to attend even 1/2 the sessions I wanted to, just because I still haven't figured out how to be in 3 or 4 places at once.ย Honestly, Friday I had to bail from my final scheduled session early to go back to my hotel and unwind / crash / zone out for a while.
Tuesday, I get to start selling the bosses on not only sending me back next year, but pushing some of the developers to go both from our Org and other Orgs within the company.
Maybe next year, too, I'll spring for an extra night or two out-of-pocket so I can actually see more of Seattle than the two blocks between my hotel and the convention building.ย (joking / not joking)
BTW, Grant KILTS REPRESENT!ย ๐
(Somehow, I posted this in the Fantasy Football topic...)
THANK YOU!
Happy to hear you enjoyed the event. If you're crawling out the door on Friday and you're shattered all weekend, you did things correctly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 66,691 through 66,705 (of 66,715 total)
You must be logged in to reply to this topic. Login to reply