« first day (4202 days earlier)      last day (33 days later) » 

5:42 AM
@PaulWhite I'll send the invoice later
5:56 AM
Is it possible to retrieve the base date value from a datetime and/or dateteime2 column without conversion?
I'm using the anchor calculation trick.
But what's the problem with the conversion?
6:26 AM
No problem. I'd like to know what is stored in the database.
Your trick produces 2022-07-07 00:00:00.000 for me, but that is possibly linked to Regional Settings and Windows Locales and the likes.
Well it should return the datetime/datetime2 value at midnight (without the time component). The current value is GETDATE() which is 2022-07-07
DBCC Page show me this:
Memory Dump @0x000000004FE8A060

0000000000000000:   10001400 97398c00 cbae0000 20587552 4726440b  ....—9Œ.Ë®.. XuRG&D.
0000000000000014:   020000                                        ...

Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8

DateTimeTest = 2022-07-07 08:30:32.610

Slot 0 Column 2 Offset 0xc Length 8 Length (physical) 8

DateTime2Test = 2022-07-07 08:30:32.6100000
But I don't trust it....
7:13 AM
Happy world chocolate day. Indeed, what else could it be?
7:40 AM
Which info do you not trust? Does sys.dm_db_page_info (2019+) show the same thing?
3 hours later…
10:14 AM
BoJo finally got out of the denial phase.
10:51 AM
he is still clinging to the position. delusional enough to think he could stay for 3 more months, until new party leader is elected
1 hour later…
@ErikDarling I know folks who use 'Millwall supporter' perjoratively, or even as an adjective as in 'Millwall supporting twat.'
Is it just me, or should these folks reconsider their branding?
Maybe to Association of Managing General Agents.
Petitions are like feedback.azure.com. No meaningful change happens
For the curious, a MGA is a type of entity that writes insurance based of a pool of capital managed by a third party. It's different from a broker in that it can write its own insurance products, whereas a broker resells insurance products underwritten by a third party. Brokers can sell insurance written by MGAs.
There are about 300 MGAs in the UK.
1 hour later…
1:39 PM
@Zikato they are the millenials thoughts and prayers
@PaulWhite well, the UK thingy keeps on going
@ConcernedOfTunbridgeWells no one likes us we don't care
1:54 PM
@ypercubeᵀᴹ are you around?
I am here, yeah
2:07 PM
I've been reading about compression in SQL Server editions for a particular use case we have. My understanding is that ROW and PAGE compression are not like a binary compression like zip - columns like xml, nvarchar(max), varbinary are not even affected, so if we want to compress large XML objects we would need to really use COMPRESS/DECOMPRESS and these are only available starting in 2016. That seem accurate summary?
@ypercubeᵀᴹ Running postgres, a table valued function seems to have very fluctuating performance
but I can imagine the plan varying a lot by input
is there something like a plan cache/plan reuse?
can I do smoething like "recompile" like I would on a sql sproc?
is the function defined as sql or pgplsql?
can I force a plan to be recalculated?
2:11 PM
@ypercubeᵀᴹ you can sue a plan...and even resue them?
@TomV it's long time since I worked with postgres functions so not sure
If i remember well, some plans are only visible to the session so they are gone when you launch a new one
Are you sure that the problem is due to the same plan reused?
@CadeRoux Yes. XML compression is new for SQL Server 2022.
Sometimes it's the opposite reason, planner taking too much time while a cached generic plan might work just fine
@TomV there's this setting that you experiment with: postgresql.org/docs/current/…
@CadeRoux if you're not querying the data as xml, e.g. just store/retrieve then it's less space-consuming to store as nvarchar max and apply some other form of compression.
you can also bypass the XML validation checks on insert
2:26 PM
@ErikDarling Even clustered column store from 2017, which can include zip-style compression
it's a real treat
I haven't tried the xml compression in 2022 yet. Last I looked it was behind a trace flag.
@ErikDarling I think the particular data is varbinary because I think it can vary by source, and yes, it's really just archival from my understanding, but is also kept in case of need to reimport.
So in this case, in SQL 2012, customers are out of luck completely, and in SQL 2016, we could use COMPRESS/DECOMPRESS.
And then when we get people onto newer and newer versions of SQL Server, more options open up
People used to sometimes write their own compression routine in SQLCLR
That's also an option, I guess. Normally this data is purged at 3 months
2:30 PM
Before compress/decompress were added to the language itself
We want to keep more data because we want to do some analysis, but we can't exfiltrate data from customer sites.
Your other option is not to store the damned thing in the database at all
So the idea would be to start keeping more data on site, until we can go through the varying data and figure out how to deidentify it and remove the PII/PHI
Then we will have a large enough set to give to the ML people to do their analysis.
But we can't keep more data without the space being reduced.
@PaulWhite Yes, I think ideally this wouldn't be in the cardiac database but some other independent data management system.
Some of the varbinary are I assume PDF and images from the carts
if you don't care very much about it you can use mongodb
I don't care much about it because it's a system before my three systems!
Especially when I don't have that much confidence in the ML people doing a better job dealing with the analysis than the existing devices team. But :shrug:
2:38 PM
sometimes it works just as well to make data up
3:08 PM
has anyone seen this error?
> The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.
it happened for multiple clients, against multiple databases
@ErikDarling I could secretly recruit the device mapping team to generate awkward data to sabotage the ML team to preserve their jobs but I think the current plan is to have the ML actually generate a tool for the devices team.
A: Intermittent "batch is aborted" exceptions

Remus RusanuThese errors indicate that the CommandTimeout is reached (default is 30 seconds) before the server returns results. This does not indicate any bug and no SP can solve it. This is a performance problem (most likely missing indexes) and must be investigated as a performance problem. Try following t...

weird. Cheers!
@CadeRoux can't wait to see that timeline
I say weird because normally you see a timeout error.
makes me wonder if my XEvents session is filtering out the timeout error :-)
3:18 PM
maybe it's causing it
go to the naughty corner
idk could be a number of things, are you using MARS or async processing?
anyway sounds like a programming problem
@PaulWhite yah after looking at it more indepth, I imagine there is a client-side error at that time around timeouts - I'll confirm with the dev team. No MARS as far as I know. I doubt they are using any kind of asynchronous calls, but since I am only new to this dev environment, I can't say for sure.
don't you use a lot of service broker?
ok, some. But just to relay the messages from the XEvents session into a database. The database involved is not one that had any problems.
@ErikDarling but yes, you're right, I have been known to do some crazy things with SB, so perhaps you're not so cheeky after all.
3:45 PM
Have you tried running it on a Mac?
trick question, nothing runs on macs
> UK Prime Minister Boris Johnson resigns as Conservative Party leader
yes well they should be careful what they wish for
seems like a complete shitshow over there
though tbf they're just following the pattern
everything is a shitshow everywhere
perhaps docker will fix it
4:14 PM
i was thinking about writing a post about sql server metrics that it doesn't make sense to monitor anymore
the only thing that comes to mind is ple
aside from various queue lengths that i don't have a lot interesting to say about
and like buffer cache hit ratio
@PaulWhite What's a docker?
curiously, if you type that into el goog, the first result is spot on
@Zikato A person working in a dock, obviously.
The circle is complete
4:28 PM
So, we just adopted a kitten
Did you name it Aslan?
big mistake
now you're gonna have a box of cat shit in your house
time to think outside the box
hopefully the cat doesn't
as long as it's only thinking
So far we've settled on the name Loki. Kids have trouble pronouncing it though
4:34 PM
ditch the kids
Sounds reasonable
you can sell them for a pretty good price
so describe the kitteh then
Looks like this
oh that's cute
4:37 PM
until it shits in your shoes
low key
Erik, did a cat hurt you when you were younger?
@PaulWhite lakhi
@Zikato no i just find them offensive as pets
I wouldn't have them in a city
@Zikato are you just going to leave that lion there
@PaulWhite yes, have them in the country where they have more natural predators.
i do appreciate bodega cats, since they kill rats
4:41 PM
He needs time to leave on his own. Get to know the apartment, etc.
Erik hates anything that rhymes with 'mac'
i like snacks
bit of cheese
maybe some apple slices
i like various types of racks as well
backs can be alright
The cat sat on the mac
i like hacks, after all i am one
A new Dr. Seuss in the making
4:46 PM
Weird that guy spent his whole life pronouncing his own name wrongly
Erik, how are you writing a blog post each day? Did you sell your soul to the devil?
well first i set the bar for quality really low
then i write about the same dozen things over and over again
Ahh, the Big Bang Theory approach
cast a wide net
someday i'll have blog traffic
5:01 PM
My blog traffic
Ah, the Brent spike
ha, I wish!
It's the bot-network-giving-me-an-unsolicited-traffic-sample-so-I-would-rent-them spike. It just rolls off the tongue
well, I visit your blog every day. You know, the shakers and movers
I visit for the one-liners!
5:11 PM
lukewarm takes ahoy
1 hour later…
6:40 PM
I visit for the deep technical details
7:12 PM
James Caant (82)
@PaulWhite is the "caant" on purpose?, like, he can't?
@Lamak Well he's dead so
yeah, was wondering if you were joking or not
Yep only tasteful good humour here
so that's the reason @billinkc is no longer around
2 hours later…
9:08 PM
@PaulWhite who’s going to play Boris Johnson now?
Well that's a very good question
I do not have a very good answer 4u
Q: How to better do a implicit conversion (cast) for a join?

Racer SQLI have a strange query like this: SELECT T1.* FROM Table1 T1 INNER JOIN #TEMP an ON an.Id = T1.AccNum LEFT OUTER JOIN Table2 T2 ON CAST(T2.Ref_tranID as bigint)= T1.Table1ID AND CAST(T2.Ref_TranLineID as int) = T1.ItmSeq ...

Can anyone explain that code formatting style
9:32 PM
2 hours later…
11:58 PM
> explain that code formatting style
See a keyword -- press [Tab]
make sure it inserts 0x09, not spaces

« first day (4202 days earlier)      last day (33 days later) »