The Heap™ – Consultancy ©® - 2022-10-20

« first day (4307 days earlier) ← previous day next day → last day (859 days later) »

Erik Darling

00:51

(removed)

mustaccio

> Parentheses were a mistake.

Erik Darling

mustaches are sort of like a parenthesis for your mouth

if you think about it

ahem

mustaccio

01:14

why would someone start thinking about it

ahem for sure

Erik Darling

maybe they're just looking for a place to sit

hard to tell

Paul White

01:37

Since when do parentheses go above

Analogy seems flawed

Simile, whatever

Erik Darling

depends on your perspective

Charlieface

02:03

Imagine if English was postfix compiler language. A sentence like "@PaulWhite (being the mod) deleted the comment" becomes "@PaulWhite the mod being () the comment deleted"

English was a mistake.

J.D.

02:15

Mistakes were a mistake.

3 hours later…

John K. N.

05:44

Morning

John K. N.

06:32

White bread was a mistake

Paul White

Morning

Zikato

06:46

@PaulWhite twitter.com/Zikato25/status/1503061579952279561

Morning

Paul White

07:20

ha ha ha

Had a postgres answer accepted today. Is it a sign

Zikato

08:04

The end is nigh

Erik Darling

08:55

@PaulWhite why is Adam always so far ahead of the curve

Erik Darling

09:18

The Lock Escalation Threshold – Part 3

2

Zikato

10:18

my day now got better

2 hours later…

Paul White

12:47

Liz Truss is resigning after 45 days as UK PM

Zikato

to be fair, most of it was mourning period where nothing happened

Paul White

That is very fair

Erik Darling

They should just go back to monarchy for a bit

Josh Darnell

That must be some kind of record, right? Wild.

Paul White

I believe it's the shortest ever term

Zikato

12:58

twitter.com/_AlexYates_/status/1583078854687543296

> "Her premiership was shorter than her leadership campaign." @BBCNews

Paul White

And as Z said much of the 45 days were quiet due to recently dead Queen

@Zikato Oh crikey yeah, that dragged on forever

Zikato

I guess they just weren't Ready4Rishi

Erik Darling

this was all boris's master plan

now he's going to become queen of england

Paul White

@Zikato 🤣 well tbf the MPs were, this is what happens when ordinary people get a vote

@ErikDarling truly 4D underwater chess

Zikato

We're living in interesting times

Paul White

13:03

Will there be a general election though, this is the question

Also, I guess the lettuce won

Paul White

13:15

> A leadership election will be completed within the next week to replace Truss, who is the shortest serving prime minister in British history.

George Canning previously held the record, serving 119 days in 1827 when he died

-Reuters

Erik Darling

13:32

has she said why yet

i can't bring myself to track anything down just yet

Paul White

waves hands generally

Presumably, the visit from the chairman of the 1922 committee earlier indicated to her there were enough of her MPs' votes to pass a motion of no confidence or whatever

Erik Darling

how did she lose it so fast

Sean Gallardy

Confidently unconfident

Erik Darling

assuming there was enough confidence to get her in in the first place

Paul White

the mini budget

bbaird

13:37

@PaulWhite Yes - 6NF breaks some RM requirements (mainly around NULLs) without some additional support on top. I come to see it more as a "physical storage optimization" instead of an actual normal form for that reason.

Paul White

databases are all about abstraction

no one should care how data is stored

there's quite some disagreement about nulls in the relational model

Erik Darling

put it all in blogs

Paul White

no one reads blogs

everyone reads the transcript

Erik Darling

i read yours at 5am

helped me fall back asleep

Paul White

fair

did you dream of locks

Erik Darling

13:40

i drifted off thinking to myself "i was right all along"

Paul White

I'll never get bored of stating the obvious

Erik Darling

i dreamt of murdering the editor who didn't fix your <= conversions in markdown

Paul White

Yeah, I fixed those half an hour ago

They creep in with the final transition from Word to Wordpress

please see the star board

Tom V

blushes

I skimmed the last messages, didn't look closely enough

Paul White

reading the transcript is an acquired skill

like eating anything except steak

everyone's on a journey

some on the slow bus

Josh Darnell

13:45

The QA department opened a ticket with the dev team this week:

> In the [...] window we use 3 contractions (couldn't, haven't, it's) but do not contract "does not". We should either use contractions for all 4 (if the informal is fine for messaging) or use no contractions.

Like...what

Erik Darling

that's a fair point

consistency is key

Paul White

You sound like the Solar Winds editing team

Josh Darnell

It made me laugh a little.

Paul White

They don't have anything better to do either

Josh Darnell

And cry a little.

@PaulWhite Haha right.

Paul White

13:47

Tell them you willn't be fixing it

Charlieface

Oct 14 at 12:41, by Paul White

@Charlieface It might be highly unlikely. Many highly unlikely things have occurred in recent times, not all of them appearing to have a solid rational basis

Josh Darnell

@PaulWhite That's a good idea.

Charlieface

I'm starting to get the hang of reading the transcript I think

Josh Darnell

In reality, it's not a big deal, because we're just going to put that in the backlog behind all of the urgent projects we have and it will get done in some sprint in the distant future.

I just thought the timing (both of our teams are really busy) and triviality of it were funny 😀

Paul White

You were right

I wonder how many end users have complained they couldn't get past that window due to the inconsistent contractions

Literally stuck there staring at the screen, unsure what to do, for months on end

Sean Gallardy

13:54

@JoshDarnell Sev-A high priority, do the needful asap, life and death here, drop everything else

Erik Darling

@JoshDarnell why put it off though? it's changing one word. it seems an easy thing to mark as fixed.

Paul White

mark's fixed, please

Erik Darling

how will he mark his territory

Paul White

will'e

how'll

he'k

I wonder if there's more to making a software change than editing the word

Maybe a process of some kind

Erik Darling

y'all'd've g'it

Paul White

13:57

The other team'll've made the opposite change and there'll'b merge conflicts

Josh Darnell

@SeanGallardy Right! On it, boss!

Paul White

Everyone should go work for Forrest. I hear he has sensible work practices

Sean Gallardy

@PaulWhite What'f we'ren't pros at GIF making?

Paul White

You're clearly unemployable

3

Sean Gallardy

Whelp, cat's out of the bag now

Paul White

14:02

Reminds me of Jeff Attwood's policy of not fixing pluralisation errors in the Stack UI

Fix one, you just encourage people

Threatened to beat people to death with a giant S for reporting them

In the days when SO Inc had a sense of humour

Josh Darnell

@ErikDarling Sometimes we do that. The official policy is to put it in the backlog and let it get prioritized with all the other work. But if it's simple, and someone isn't completely underwater with projects and production issues, we'll just make the change.

mustaccio

> some on the slow bus

and some on the short one

Paul White

I'm glad someone noticed that

King Charles I and King Charles II both dissolved parliament

We now have a King Charles III

Erik Darling

trifecta!

Sean Gallardy

I'm on all the buses

Charlieface

14:18

BBC: "Reports suggest Boris Johnson could stand in Tory leadership contest" it's like somthing out of a Punch and Judy show

The only thing keeping me from utter despair is knowing how much worse any other party would be.

John K. N.

@JoshDarnell should that not be ...isn't... instead of ...it's... ?

Asking for a friend.

Josh Darnell

@JohnK.N. "It's" is the contraction for "it is" - QA was targeting all contractions, it's just a coincidence that the other three were "___ not" contractions 😃

Paul White

I bet you're glad you brought this up

bbaird

Can we stop talking about contractions and dump on MongoDB for a bit?

John K. N.

@JoshDarnell 🙄

ypercubeᵀᴹ

14:31

@Charlieface I don't know much about UK politics but seriously: what could be worse than this?

Surrender to Putin?

Paul White

They could annex themselves into China, perhaps

One country, n systems

I quite liked Boris

Don't know much about his policies or anything tho

Josh Darnell

@JohnK.N. I didn't mean that to come across as insulting, sorry if it did.

Paul White

Pretty sure John KN would state a reason for being upset if he were

John K. N.

@JoshDarnell not at all. No worries.

...as Paul would put it.

Just pulling your leg.

Josh Darnell

Ah, good.

Paul White

14:38

Whew that was a close one.

John K. N.

I'm a sarcastic little < your favorite expletive > at times.

Paul White

23 hours ago, by mustaccio

> that type of language is not acceptable on the Stack Exchange network. Please remember that this is supposed to be a professional environment; you can review the Code of Conduct for a refresher on how users are supposed to interact here.

Josh Darnell

That quote is taking on mythical status in this room rather quickly.

Paul White

Future classic

Meanwhile, I have spent the last several hours trying to convince my father-in-law's AMD A9 laptop to upgrade to Windows 21H2, from 18,000 kms away

Nov 30, 2012 at 2:40, by SQLkiwi

Regarding The Battle, progress is slow, but I'm in it for the long haul :)

I'm contracting "let us" into "lettuce" for everyone in the UK today

2

John K. N.

@PaulWhite AMD processor?

Paul White

14:45

@JohnK.N. Two cores!

Still faster than Erik

user image

zoooooooooooooom

John K. N.

I'm thinking of purchasing AMD Ryzen™ 9 5900X Desktop Processor for my new computer.

Paul White

I hear people say those are neat

John K. N.

Apparently they run at warmer temperatures when idle than Intel processors do.

bbaird

@JohnK.N. Useful in the winter, especially if it sits next to your feet

Paul White

15:05

This thing has 4GB RAM and the slowest HDD I have encountered for a long while

John K. N.

Ever considered inserting an SSD? Or does the laptop still have EIDE?

Paul White

I would consider it if the laptop weren't 18000km away

Also, he hadn't used it at all in like a year

John K. N.

Ah... now I get it.

Paul White

Quite the backlog of updates to apply

John K. N.

sux

Paul White

15:07

It's a 1TB HDD partitioned into 893GB and 25GB

John K. N.

:-|

Paul White

841GB free

The salesperson told him it had plenty of "memory"

John K. N.

Luvly

@PaulWhite Will you be travelling there in the near future to whack the salesperson?

Paul White

It's a motivating factor

Sean Gallardy

@PaulWhite Still faster and cheaper than most cloud databases

Paul White

15:21

Yeah, and like the cloud, I don't think the CPU is the main problem

Sean Gallardy

I believe a VM with those specs (2 full cores and 4 GB of memory) would cost >= $131/mo

Paul White

bargain

I'll see if he's up for renting it out

Sean Gallardy

I didn't even add in the 1 TB of disk he had

Imagine paying ~1500 a year for 2 cores and 4 gb of ram

Paul White

US$ too

Sean Gallardy

That laptop probably cost $700

doubtful it takes $800 worth of electricity and cooling

Paul White

15:29

Especially not when it's switched off for a year

Sean Gallardy

🤣😂🤣😂

I hear energy prices are high these days

Paul White

I heard that too

Erik Darling

@PaulWhite do you remember where that itzik article about variable assignment not being reliable is

Paul White

@ErikDarling Doesn't ring an immediate bell. Unreliable in what context?

String concatenation? Order of results?

Sean Gallardy

15:47

Speaking of which, another issue of shouldn't-have-been-inlined inlined function causing problems.

Paul White

go on

Sean Gallardy

I don't know, didn't really care... you know, queries and all. Something about using synonyms for temp tables.

Paul White

I'll look forward to the vague note in the CU then

Sean Gallardy

16:03

I didn't know people still used synonyms TBQFH

Paul White

P sure you can't use them with temp table but w/e

Erik Darling

@PaulWhite yes, that. order of results, sometimes ending up null or not actually concatenating anything.

Charlieface

@ErikDarling Not sure about Itzik, but it's been noted on here already many times.

8

A: How to use COALESCE with multiple rows and without preceding comma?

Just to add to Aaron's answer above... Be aware that an ORDER BY may break by only including the last item in your query. In my case, I was not grouping, so not sure if that makes a difference. I'm using SQL 2014. In my case, I have something like value1, value2, value3... but my result in the v...

Paul White

@ErikDarling itprotoday.com/sql-server/…?

Charlieface

31

A: nvarchar concatenation / index / nvarchar(max) inexplicable behavior

TLDR; This is not a documented/supported approach for concatenating strings across rows. It sometimes works but also sometimes fails as it depends what execution plan you get. Instead use one of the following guaranteed approaches SQL Server 2017+ SELECT @a = STRING_AGG([msg], '') WITHIN GROUP (O...

Erik Darling

16:13

@PaulWhite that's the one i was thinking of, thank you

Charlieface

6

A: Order by 1 shortens the result set to one row

Is this a bug in SQL Server? No. From PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location The correct behavior for an aggregate concatenation query is undefined. See also this excellent answer by Martin Smith over at stackoverflow. nvar...

Erik Darling

@Charlieface thanks, appreciate the additional links

Paul White

no worries

Charlieface

It's even in the documentation now, believe it or not learn.microsoft.com/en-us/sql/t-sql/language-elements/…

Paul White

If memory serves, Hugo K provided that example

Erik Darling

16:17

docs++

anyway, i asked because a couple weeks back @PaulWhite chastised me for doing variable assignment in a particular way for batching/loop code

Paul White

annoys me so much MS broke support.microsoft.com/en-us/kb/287515

Erik Darling

should i assume that the behavior with order by when building strings could also screw up variable assignment in that case?

Paul White

betaarchive.com/wiki/index.php/Microsoft_KB_Archive/287515

Charlieface

Github says William Assaf committed the change github.com/MicrosoftDocs/sql-docs/commit/…

Paul White

@Charlieface Yes, I meant Hugo K provided it privately (MVP route) and it ended up in the dcos

I forget who it was that originally asked for a compact example

@ErikDarling Depends on your exact example, but in general it's never safe to assign more than once, logically

There are some pretty horrible edge cases

Erik Darling

16:25

i'm sure

michaeljswart.com/2022/09/batching-follow-up

something similar to that example

Paul White

Oh yeah that

@ErikDarling Where did we have that conversation? I sort of remember it, but not where

Erik Darling

it was in here, you gave some examples using max or offset/fetch

and then a gist with a cursor or some such

Paul White

Sep 23 at 0:01, by Paul White

/*Starting place*/
SELECT TOP (1)
    @NextBatchMax  = SQ1.id
FROM
(
    SELECT TOP (1000) aia.id
    FROM dbo.as_i_am AS aia
    WHERE aia.id > @LargestKeyProcessed
    ORDER BY aia.id ASC
) AS SQ1
ORDER BY SQ1.id DESC;

Oh yeah

Erik Darling

that's the one

ha ha, couple weeks ago. i'd say my brain is porridge but i'm pretty sure goldilocks finished the bowl.

Paul White

Sep 22 at 16:48, by Erik Darling

https://gist.github.com/erikdarlingdata/5605b5bdea1613e3109fe954c094fd75

@ErikDarling Same, I went checking my email and all sorts

My Windows Update is now 74% complete after 7 hours or so

Erik Darling

16:36

blow on it

Paul White

It was Tibor Karaszi who asked for the compact string aggregation example

Erik Darling

good ol' tibor

Paul White

Demo: dbfiddle.uk/wi5_b600

@ErikDarling It's 18 000km away

Erik Darling

gonna need a good pull on the vape to hit that

Paul White

right on

Erik Darling

16:48

how many updates is it working on

must be a good chunk

Paul White

Just the one for the past several hours

Feature update to Windows 10, version 21H2

user image

It moved from 73% to 74% about an hour ago, which was exciting

Erik Darling

oh yeah that was a big one

Vérace

A chairde - Morning all!

@Zikato To be fair, it's always mourning on the Heap!

Erik Darling

@PaulWhite lousy e-cores

Paul White

2 hours ago, by Paul White

Still faster than Erik

Sean Gallardy

17:12

@PaulWhite I don't know, I could have read the issue wrong. Like I said, query stuff so not very interesting :D

Paul White

sure

Sean Gallardy

I know there was a synonym

and I might have made up a temp table

but to be fair, they are themselves made up

Paul White

yeah, I think synonyms have come up before with function inlining

Paul White

17:28

support.microsoft.com/en-us/topic/…

no mention of synonyms

maybe schema binding doesn't work?

user image

yay

Sean Gallardy

Just wait for the restart 🍿

Yeah somehow they were able to make a synonym for a temp table.

I can totally run this: create synonym dbo.doesntexist for #temp_table and I didn't even create a temp table named that

Erik Darling

sounds like a DNS problem

Sean Gallardy

17:45

That's enough query stuff for at least a month for me

Paul White

@SeanGallardy huh well what do I know then

Sean Gallardy

@PaulWhite more than I

Paul White

@SeanGallardy Yeah, I know, I remember but at least it is forward progress

Perhaps I'll create a synonym for Windows 10

Sean Gallardy

Call it Windows Mac

But first say that it'll be the last version of Windows ever made

Then make a new version

Paul White

ha ha ha

I still refuse to upgrade to Win 11 on the basis it can't possibly exist

1 hour later…

Erik Darling

19:17

this week i'm working with a client that proper cases all their queries

Select Columns From Table Join Table Where Thing And Thing Or Thing

driving me willy bonkers

Josh Darnell

Oof

You know you're in for a bad time with a Microsoft ticket if the email address has "(Tek Experts)" appended to the end @SeanGallardy.

Erik Darling

NOOOOOOOO

Paul White

19:39

@ErikDarling I'd have to add a surcharge to deal with that

Still better than lower case aliases though

Alias with confidence

Charlieface

@PaulWhite There's a script at the bottom of that with this gem

SELECT 'EXECUTE sys.sp_refreshsqlmodule ''[' + OBJECT_SCHEMA_NAME(object_id) + +'].' + '[' + OBJECT_NAME(object_id) + ']'';' AS 'RefreshStatement'

hmmm, no use of QUOTENAME and use of single-quoted column names and use of system functions instead of joining the sys.objects view, all in one line. Not bad.

Erik Darling

@PaulWhite that's a real good point, there

Paul White

@Charlieface Microsoft write the worst SQL

bbaird

19:59

@PaulWhite I remember when they rolled out their fancy new graph syntax and every example was something that could be done with an inner join or correlated subquery

Sean Gallardy

@JoshDarnell MindTree is really who you want to be afraid of

Josh Darnell

@SeanGallardy Good to know this isn't the bottom of the barrel I guess.

Sean Gallardy

@JoshDarnell Not all vendors are bad, unfortunately so many are terrible that the generalization holds true.

Also, the good ones tend to not stay vendors very long.

Josh Darnell

They escalated my ticket to the engineering team, and just send me an email every few days saying they haven't heard back from engineering yet, and don't have an ETA.

It's a lot of fun.

Sean Gallardy

That sounds about right

Cade Roux

20:49

@Charlieface I also frequently encounter issues with aggregates and views, where parameters cannot be pushed in (although they can be, and that's often my key workaround - adding the parameters to an inline table-valued function to get past the aggregator).

Charlieface

@CadeRoux Arguably TVFs are a better version of views in almost every way. There is almost nothing that views can do that they cannot, including being updatable. The only things I can think of are they cannot be indexed (obviously they can still utilize indexed views in their plans) and the rarely used CHECK OPTION.

1 hour later…

J.D.

22:00

@Charlieface Single-statement TVFs, true. MSTVFs leave too much trouble for the developer to get into, and prevent parallelization in consuming queries.

Erik Darling

22:43

MSTVFs only have a serial zone where the table variable is populated/read from

2

scalar UDFs (assuming no froid-lining) prevent it in total

« first day (4307 days earlier) ← previous day next day → last day (859 days later) »

Transcript for

Oct '2220

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2025 Stack Exchange Inc; legal

mobile