The Heap™ – Consultancy ©®

Zikato

05:41

Morning

Paul White

07:07

Morning

Zikato

07:29

This made me chuckle

Paul White

I'm sure those messages are unrelated 😀

ypercubeᵀᴹ

09:39

> First of all, you need to change your mindset when it comes to Cassandra. We understand that it is a difficult thing to do when you are used to running old, monolithic relational databases which are not designed for true high availability.

Why some need to throw unrelated / untrue / harsh comments about other DBMS's in answers?

Paul White

typical Aussie

ypercubeᵀᴹ

Or the SQLpro guy. Question about SQL Server has a section about:

> In low-end RDBMS like MySQL ...

Paul White

Yeah he's a special one too

ypercubeᵀᴹ

In another question (tagged Postgres only) he has this

> > PostGreSQL is unnable to ..., while professionnal RDBMS like Oracle or MS SQL Server does not need ...

Paul White

French

Anyway I suppose it's just tribal behaviour

I don't think such things add anything to an answer, but that just affects how I vote on them

Vérace

10:16

A chairde - Morning all!

Zikato

I think it's the mentality of: I have to put down other choices to feel better about my choice.

Charlieface

4

Q: How to check if WHERE = value does not exist and make it zero by default?

My goal is to check if lineId's value exists. If not, it has to be zero by default. Here is my datas: For example: SELECT lineId from table_name WHERE lineId = 80 There is no lineId = 80, then I want to see that lineId = 0 records. Thanks, sincerely :)

sql-server

This particular problem is quite difficult to do if you want to do it in a single query, while avoiding an extra sort as well as avoiding any lookups on the second half of the query. I couldn't find any way to avoid that last issue.

Even if I add a dummy row in the middle of the union, the Merge Concatenation still pulls at least one row from each set.

Actually scratch that, it doesn't work anyway.

Vérace

I'm a francophone and a francophile - but that SQLpro appears to be a "trou d'uc"! He talks about PostGreSQL fundamentalists and (this is hilarious) postGreSQL ayatollahs!. Another gem - In short, with PostGreSQL the choice of collation is between plague and cholera …. Nuff said!

Zikato

10:36

Hmm, was there any CU in SQL Server 2019 which didn't introduce a new bug?

We've postponed so many updates because there were known bugs in them and went from CU10 to CU16 and it has bug in the DBCC CHECKDB

Charlieface

Hard to know if the bugs are new or have always been there.

Zikato

I've tested it in Docker. Introduced in CU13, fixed in CU17

Paul White

10:50

@Charlieface Plan above

Writing the query is left as an exercise for the reader

Charlieface

Was literally about to say: I throw down the gauntlet. Who can get my query to only pull rows from the first half of the union? I'll play first: a dummy VALUES with two LEFT JOINs doesn't work dbfiddle.uk/AK_Hf67k

Your query I can't even imagine how you could do that without requiring a sort and I'm thinking pretty hard

Paul White

@Zikato Have you ever written code that doesn't contain any bugs?

Zikato

@PaulWhite Will I win a "mastering lock escalation" sessions if I get it right?

Paul White

Aug 29 at 13:38, by Erik Darling

nope

Zikato

@PaulWhite SELECT @@VERSINO

Paul White

10:58

@Zikato Seems to have a bug

Charlieface

@Zikato No you get some free BrOz "how to think like the engine" sessions

Paul White

I've always wondered how one could think like the engine without knowing everything about how the engine works in detail

Zikato

how to think like Thomas the tank engine

Paul White

I suppose "think somewhat similarly to the engine" doesn't have the same impact

Anyway I don't have a million-dollar car collection so who am I to judge

Charlieface

@Zikato Joke probably lost on most of the (un)developed world which don't have British TV and books

Paul White

11:01

And certain ex-colonies 😀

I wonder if having a fat controller helps I/O performance

2

Zikato

Well I have a rapid-fire joke approach. Someday one of them will stick

Paul White

Until then every one of them will suck

Zikato

It helps that I have no shame

Paul White

Big advantage in life for sure

Zikato

IIRC George Carlin was narrator for Thomas the tank enginee. There are supercuts on youtube when they mix his narration with some of his not so kid-friendly standups

Paul White

11:08

Man I miss execution plans in db<>fiddle

2

Even copying the xml into Plan Explorer is a faff

Paul White

11:31

SET SHOWPLAN_ALL is tedious

SET STATISTICS PROFILE will have to do

Paul White

11:50

Alternate solution

Erik Darling

12:17

Someone likes window functions this evening

Paul White

Vérace told me they were great

Erik Darling

Hard to trust a guy with that name.

Paul White

I'm working on the basis he'll eventually be right about something

ypercubeᵀᴹ

asking silly questions ;)

How does "all operations are online" coincide with your suggestion to "Uninstall Cassandra from ALL the nodes" for doing a rollback? — ypercubeᵀᴹ 22 secs ago

Paul White

It's an online uninstall ofc

Erik Darling

12:27

Always Uninstalled©

I should take that to the Teams marketing department

Paul White

When have they ever implemented a good suggestion?

Erik Darling

🕕🕦🕚🕐🕥🕧🕙🕣🕠🕝🕢🕟🕜🕤🕡🕞🕘🕒🕗🕔🕑🕖🕓🕛

Paul White

That's pretty

Erik Darling

That’s my new time series database

Charlieface

12:46

@PaulWhite Not sure about this. Maybe COUNT(CASE WHEN lineId = 80 THEN 1 END) OVER () and WHERE Count80 = 0 OR lineId = 80?

I'd be interested to know the efficiency of using an aggregate like that.

Speaking of which, I can never work out if FIRST_VALUE... OVER (...ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is faster or slower than FIRST_VALUE... OVER (...ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Paul White

@Charlieface You're thinking along the right lines, but that would give you a multi-spool plan without help

Sounds like you have many questions

Another single-access plan with an obvious implementation

Charlieface

@PaulWhite brentozar.com/pastetheplan/?id=SkTZNGUgs

Paul White

@Charlieface Yes exactly, with a helper table

@Charlieface I'll show you my solutions now dbfiddle.uk/05H3MIpW

Charlieface

@PaulWhite That one I can assure you will be dog slow

Paul White

@Charlieface Why?

I thought the game was no sorts and only a single table access

Charlieface

12:54

If you have a lot of rows I should have said. Because it's aggregating them into a single giant nvarchar(max) and then splitting it back out using STRING_SPLIT.

Paul White

@Charlieface Only rows that either match or are zero

Charlieface

Well the game is a fast solution, I doubt that's one of them.

Paul White

Ok

Sean Gallardy - Mostly Retired

Still a bunch of query talk in here :/

Charlieface

Moving goalposts and all that

Paul White

12:56

@SeanGallardy-MostlyRetired I thought I banned you already

Is this a sock-fish account

Sean Gallardy - Mostly Retired

@PaulWhite I think the 2 year ban finally wore off :D

Paul White

Damnit I should set a reminder

Sean Gallardy - Mostly Retired

I'll send you a reminder postcard

Paul White

Appreciated, thanks

@SeanGallardy-MostlyRetired You caught us at a bad moment to be fair

Most of the time it's just Erik being indecipherable

ypercubeᵀᴹ

maybe a cursor solution would be faster ;)

Sean Gallardy - Mostly Retired

12:58

It's like I know when everyone's pants are down... it's my special skill.

Charlieface

@PaulWhite Also if you are reading all the rows for both halves that doesn't count either. You need to only read the 80 rows, then read the 0 rows only if no rows from the first half.

Paul White

@SeanGallardy-MostlyRetired At some stage you'll learn what comes next

Sean Gallardy - Mostly Retired

Maybe we should stop sending him booze and he'll be cipherable?

Maybe, when I grow up!

Paul White

@SeanGallardy-MostlyRetired I can't even imagine what that would be like

Charlieface

JEAGL somebody?

Sean Gallardy - Mostly Retired

13:02

@PaulWhite Now that I have internet access again, I finally was able to respond to emails.

Paul White

@SeanGallardy-MostlyRetired Ooo I will check my email with great interest at some point later then

Sean Gallardy - Mostly Retired

It's not super interesting I also forgot to tell you I switched roles

Now I work on millions of WorldWideImporters databases

Paul White

Are you a competent engineer now?

OMG the cloud got him

Sean Gallardy - Mostly Retired

@PaulWhite let's not get TOO crazy

I'll introduce bugs, just for you to find, like a game of hide and go scratch your eyes out

Paul White

I never use the cloud on principle

Sean Gallardy - Mostly Retired

13:05

That's why you're better than most

Paul White

Ok you can stay

Sean Gallardy - Mostly Retired

:O

Paul White

How I resisted the urge to kick you out after your first message I'll never know

Sean Gallardy - Mostly Retired

The willpower of many men!

Paul White

Heroic effort

Your avatar still gives me the giggles btw

Sean Gallardy - Mostly Retired

13:08

superhuman for sure, you should be the next Marvel character

I need a new avatar

It's been too long between changes

Paul White

@SeanGallardy-MostlyRetired Sadly I am massively unqualified

Disney won't return my calls either, for some reason

Sean Gallardy - Mostly Retired

Maybe they can't find NZ?

Paul White

Understandable if so

You're right the email wasn't very interesting. I replied anyway.

Invoice attached.

ypercubeᵀᴹ

@PaulWhite I suspect Amazon interception

Paul White

@ypercubeᵀᴹ 🤣

Sean Gallardy - Mostly Retired

13:21

@PaulWhite 😂🤣 it was a good email

Erik Darling

I’ll sue the both of you for excluding me from emails

Paul White

No one knows your super-secret email address

ypercubeᵀᴹ

@Charlieface what about this one? brentozar.com/pastetheplan/?id=rywa2fIxi

Charlieface

@ypercubeᵀᴹ Good point, that works as well as mine in the simple case. Do note though that it doesn't answer my problem: it will still read at least one row from the TOP in order to know when to stop.

Paul White

13:36

Better, because the code and plan are both simpler

Adding an index to avoid a sort is cheating though

ypercubeᵀᴹ

13:49

OK but that changed only the table scan to index seek.

Paul White

@ypercubeᵀᴹ It also provides the ordering for the ORDER BY?

ypercubeᵀᴹ

right

Paul White

So you'll have a sort for that without the index

ypercubeᵀᴹ

Yeah, I tried first without adding more rows and index but I don;t remember what the plan was

Not bothered to recreate. Who am I anyway, just a user of low-end, non-professional DBMS's ;)

Paul White

ha ha

Charlieface

14:04

Is there any way to disable merge concatenation, but only for a one union not the other?

Hannah Vernon

so crazy, I've had an instance of SQL Server 2019 CU17 in "stopping" state for 16 hours.

Paul White

@HannahVernon Sounds like a bug

Hannah Vernon

that's what I think too

Paul White

@Charlieface No

Charlieface

@HannahVernon Probably some shutdown code which has deadlocked. Just kill the process and move on.

Hannah Vernon

14:07

The action 16 hours ago was I shut it down from SQL Server Configuration Manager from the local server console. I just did SHUTDOWN WITH NOWAIT; from SSMS and it shut down immediately. Previously to that, I couldn't see anything in the session DMVs that was waiting.

pretty weird

Sean Gallardy - Mostly Retired

@PaulWhite Sounds like not a bug :D

Paul White

Old habits die hard

Sean Gallardy - Mostly Retired

I'm contractually obligated to have every 17th reply be "have you tried moving it to the cloud?"

Paul White

4 hours ago, by Zikato

Hmm, was there any CU in SQL Server 2019 which didn't introduce a new bug?

Sean Gallardy - Mostly Retired

It's double the bugs if one of my fixes went in on that CU

Paul White

14:20

Can't believe they ever let you within 100 feet of the source code

Charlieface

@SeanGallardy-MostlyRetired Check you JOIN conditionss, it's duplicating your bugs

ypercubeᵀᴹ

@PaulWhite you're right of course: dbfiddle.uk/dcJr8guq

Sean Gallardy - Mostly Retired

@Charlieface HA! I only use CROSS APPLYs! sunglasses.gif

Or was that cross joins

either way, works_on_my_machine (tm) (j/k all jokes)

Charlieface

@ypercubeᵀᴹ Completely off course, right out into the Pacific

Paul White

What sort of world is this where ypercube abuses apostrophes and Sean doesn't

ypercubeᵀᴹ

14:24

@Charlieface saved just in time, thnx

Sean Gallardy - Mostly Retired

@PaulWhite Up side down... but I believe you already call that right side up?

Paul White

I would

Erik Darling

> I just learned some NUMA related knowledge and I want to know:

Sean Gallardy - Mostly Retired

＼（〇_ｏ）／

I'm writing a response to that question

Paul White

Have you tried moving it to the cloud

Mangled Instance preferred

Sean Gallardy - Mostly Retired

14:40

😂😂😂😂

Erik Darling

also not to backtrack too much, but my first kid loved thomas the tank engine. we actually just gave away about 50 of those toy train cars to another family since they're not really played with much anymore.

had an aquarium car that did a whole light up thing

quite a show

it seems like this search engine with no pinterest results idea has legs

Paul White

colony detected

@Charlieface There's this dbfiddle.uk/pL1faVHn

At the cost of possibly reading one qualifying row twice

It's better with an index ofc

@ErikDarling Does NUMA mean Never Use Managed-Instance on Azure?

Charlieface

Don't see how it's better than what I have, or than @ypercubeᵀᴹ 's answer.

Try as I might, I just can't remove the second Merge Concatenation. My latest attempt dbfiddle.uk/Z8gJLHuu

Paul White

14:55

5 hours ago, by Charlieface

This particular problem is quite difficult to do if you want to do it in a single query, while avoiding an extra sort as well as avoiding any lookups on the second half of the query. I couldn't find any way to avoid that last issue.

@Charlieface Well technically it's 'better' by not reading any rows from the 'second half of the query'.

Charlieface

Same difference I suppose. Point is: you just want to read the first side once, and not any of the rows on the second side. Imagine the two halves are both very complex or expensive derived tables, you want to avoid reading from them if not necessary.

Eg they have blocking hash joins. You want to avoid reading even a single row, otherwise the hash build phase will run

Erik Darling

sounds like you need a temp table

have you tried mastering temp tables

Paul White

@Charlieface Are the goalposts on wheels now?

Erik Darling

anti-gravity goal posts

ypercubeᵀᴹ

@PaulWhite on Enterprise. Not the edition, the Star Trek one ;)

Erik Darling

14:59

kerbal space goal posts

Sean Gallardy - Mostly Retired

you all are going to drive me to start drinking

Paul White

@SeanGallardy-MostlyRetired It really is the only answer

Charlieface

@ErikDarling And that has the extra cost of writing and reading them. To be honest, if I was faced with this problem I would normally just do two separate SELECT queries with a @@ROWCOUNT check in between. Someone asked me just a couple of days ago about this exact problem, which is why I got interested.

Sean Gallardy - Mostly Retired

There's a bottle of type-2 diabetes (Moscato) somewhere around here

Paul White

@SeanGallardy-MostlyRetired So are you doing the same work now, just in a different space, or has the role changed more than that? Feel free to email instead.

Sean Gallardy - Mostly Retired

15:02

@PaulWhite I'll email you but essentially new role, new team, new everything :/

I'm not sure yet if it's a good or a bad thing

Charlieface

@PaulWhite On an Artemis I rocket. Oh sorry that never took off spacenews.com/…

Sean Gallardy - Mostly Retired

I'm 100% cloud now, but having said that I'm currently doing commit to sqldumper - so... /shrug

Paul White

@SeanGallardy-MostlyRetired Most active code path in SQL Server

Sean Gallardy - Mostly Retired

🤣😁

Paul White

I wonder who's the Top Man at Microsoft now

Perhaps they new renamed the role Top Man Cloud Edition?

@ErikDarling Are you recommending a spool

Erik Darling

15:05

@Charlieface well yes but it gets the complexity out of the way in one go

@PaulWhite a human spool, highly optimized

Paul White

Oh well that's alright then. I thought we had an imposter

Sean Gallardy - Mostly Retired

Honestly, it's really ugly right now. I left (for reasons) and then 2 weeks later a bunch of others left. If you look at my previous role, in the last 1 month, 43% of the team left with another 15% of the team waiting on offers.

Paul White

Sounds v sensible to me

If I worked at MSFT, I'd have left by now too

Possibly more than once, just for emphasis

Sean Gallardy - Mostly Retired

That's how you get promotions at MS

leave, then come back 1 year later for a role 2 levels higher

repeat

Paul White

Ah true

Erik Darling

15:08

cookies started that

Sean Gallardy - Mostly Retired

It was there well before him

Paul White

Well hopefully it won't apply to Dr No

Sean Gallardy - Mostly Retired

lmfao

Management in my old team was pretty bad, when 58% of your team leaves in a month you'd normally say, wow what's going on... but instead they just say, all these people are problems we got rid of.

Paul White

@SeanGallardy-MostlyRetired Isn't that just general corporate practice anyway? Fail to keep people happy, hire them back at $$$ later?

Sean Gallardy - Mostly Retired

Don't worry though, the person hired 9 months ago and has no knowledge about anything will be filling my old role

Paul White

15:10

Should fit right in

Also: that escalated quickly

The only reason I'd take a role as an escalation engineer is to have that gif on my desk

Sean Gallardy - Mostly Retired

It did and didn't. We gave feedback for 2.5 yeas about the stuff and nothing changed.

Paul White

At least you still get to use Teams every day right?

Sean Gallardy - Mostly Retired

/groan

Paul White

every. fricken. day

Sean Gallardy - Mostly Retired

literally.

Paul White

15:13

Man it's a good job I'm unemployable like Erik

Sean Gallardy - Mostly Retired

Makes you want to play on railroad tracks some days

Erik Darling

@PaulWhite you didn't have to get a single tattoo to get there either! well done.

Paul White

@ErikDarling Not that you know about anyway

Sean Gallardy - Mostly Retired

Oh this just got interesting.

Paul White

@SeanGallardy-MostlyRetired Those are called training days

> training

Sean Gallardy - Mostly Retired

15:15

No such thing as training days here

blasphemer

Paul White

Yeah but you get your choice of modern T-shirts

Erik Darling

mostly retired at microsoft

Paul White

TMCEAM (Top Man, Cloud Edition, at Microsoft)

Sean Gallardy - Mostly Retired

I'll make that my new title

Erik Darling

15:39

cloud rules everything at microsoft
satya get the money
dolla dolla bill, y'all

Paul White

16:31

@Charlieface More code: dbfiddle.uk/Xx6v2Be0

First side read once, second side only read if necessary. No sorts. Single query.

Erik Darling

16:46

@PaulWhite yeah but

Paul White

😀

⚽🦶

Erik Darling

i hope someone tests this solution against a 28 trillion row table that only has line id 140 in it.

=|

Paul White

17:08

Batch mode version with clustered index and helper table. MAX UNBOUNDED instead of FIRST_VALUE.

Erik Darling

cool

check you out

chasing those goal posts

i bet you can't do that without writing a query

Paul White

Probably not actually

Erik Darling

well then

game over

Paul White

😥

Erik Darling

17:32

the fastest query is the one you don't run

4

Paul White

very wise

Erik Darling

sort in the query you don't run

Zikato

18:25

Or go to the UK, they'll get you sorted.

Paul White

Geezer

Pulp - Sorted For E`s And Wizz (Jools Holland 2002)

►

Sorted for E's & Wizz

"Sorted for E's & Wizz" is a song written and performed by the English band Pulp for their 1995 album Different Class. Based lyrically on a phrase that lead singer Jarvis Cocker overheard at a rave, the song features lyrics examining the hollow and artificial nature of drug culture. Because of its subject matter, the song sparked controversy in the UK, where several tabloids attacked the song. "Sorted for E's & Wizz" was released as a double A-side single with "Mis-Shapes" in September 1995, and reached No. 2 in the UK Singles Chart as well as No. 6 in Ireland. It was Pulp's second successive No...

Has it's own wiki page!

Back when recreational drugs were controversial

Erik Darling

19:06

but what is wizz

Zikato

ypercubeᵀᴹ

@ErikDarling something that gives you speed ;)

Erik Darling

19:30

Oh not something that makes you pee

Paul White

19:42

Whomever

Erik Darling

Oh yeah huh

Charlieface

20:21

@PaulWhite Cracked it finally! I whittled down your batch mode query again and again, and eventually ended up back at row-mode. All you need to do is use an OUTER APPLY for the second half, and push the first half's lineId into a VALUES (this must be the first table in the FROM of the second half). This causes it to get a Startup Filter which means the whole branch doesn't run.

dbfiddle.uk/_qVIRrCz

brentozar.com/pastetheplan/?id=B1Wk6uIxs

Paul White

20:38

@Charlieface Did you test that with a value that doesn't exist like say 80

Zero also exists

Charlieface

dbfiddle.uk/ZoxUWRTG

Forgot and ended up taking out that first VALUES clause

So basically each half uses a dummy VALUES to generate a guaranteed single row, then left-join everytung else

Simple version of same dbfiddle.uk/YP_fVcXp

Paul White

21:07

db<>fiddle is too hard to use on my phone. The logic seems right except for the case when no zero rows exist in the table but I'm not certain previous solutions handled that correctly either. Assuming correct means no rows returned. But yes the key is the startup filter.

Michael Green

'Morning.

Paul White

The single row table is a useful simplification over the window function. I've used that before no idea why it didn't occur to me here. Blame Verace and his window functions perhaps

ypercubeᵀᴹ

@Charlieface it will return a row (with NULL) if the table has no 140 or 0

Charlieface

@ypercubeᵀᴹ dbfiddle.uk/zPoqNP4_

ypercubeᵀᴹ

yep

Paul White

21:16

Feels like either the outer apply can be a cross apply or the second left join an inner join (but not both). I haven't tried it though

Cross join really

ypercubeᵀᴹ

@Charlieface you are right of course that t1 is a derived table. Not sure about the second case. Why isn't the t1 (in the CROSS APPLY version) a derived table as well?

Also: you seem to imply that the terms subquery and derived table are disjoint. I was thinking along the terms that a derived table is a kind of subquery.

Charlieface

21:55

@ypercubeᵀᴹ Yes they are disjoint (or rather mutually exclusive). A subquery is a query that is logically executed once per row of the outer table. Whereas a derived table is not, it exists independently (in logical terms) from the outer table.

CROSS JOIN does not: the table you reference must stand on its own (must be independenly executable), that is what is termed a derived table.

But CROSS APPLY behaves similarly to a subquery (although it can return more than one column or row) as it is executed once per row of the outer table, so I think that's how you would classify it.

ypercubeᵀᴹ

I've just checked the SQL standard (well, a copy of a draft)

<lateral derived table> ::=
  LATERAL <table subquery>

<derived table> ::=
  <table subquery>

<table subquery> ::=
  <subquery>

<subquery> ::=
  <left paren> <query expression> <right paren>

I understand what you mean though. With "subquery', we usually refer to subqueries in the WHERE clause

(annoyed with myself, not anyone else ;)

Paul White

Subquery and derived table are often used interchangeably. Table expression is another. Personally, I don't think the distinction is clear or even important.

@ypercubeᵀᴹ What do you call a subquery that returns a scalar value in the SELECT clause though?

ypercubeᵀᴹ

scalar subquery?

Paul White

Well exactly. And maybe even a scalar correlated subquery.

microsoftpressstore.com/articles/article.aspx?p=2233323

> Derived tables (also known as table subqueries) are defined in the FROM clause of an outer query.

Logically executed per outer row is just another way of saying correlated

A table expression or derived table can also be correlated or not

It's really not important

The documentation for APPLY also prefers derived table over subquery. The right side of an apply can be any table source even a function. I think it's a stretch to call that a subquery in the everyday sense

People also tend to say subquery to describe anything that looks like a query, but is not at the outermost scope

No doubt everyone has their own set of preferences on this

ypercubeᵀᴹ

22:26

@PaulWhite I raise the stakes to a scalar correlated recursive cte subquery ;)

Paul White

@ypercubeᵀᴹ Only somewhat related, have you ever found a real clear answer to any substantial question in the SQL Standard?

ypercubeᵀᴹ

only available in some unprofessional DBMSs ;)

@PaulWhite haha, no

Paul White

@ypercubeᵀᴹ great apostrophe adjustment there 🤣

ypercubeᵀᴹ

only silly technical details, like is a view a table or not ;)

@PaulWhite I have been taking notice!

Paul White

It does seem written to be deliberately opaque

ypercubeᵀᴹ

22:29

dbfiddle.uk/Tf4rHIo0

Paul White

@ypercubeᵀᴹ that ought to be illegal! I thought you were joking

4 mins ago, by ypercubeᵀᴹ

@PaulWhite I raise the stakes to a scalar correlated recursive cte subquery ;)

Erik Darling

@ypercubeᵀᴹ that's messed up and i hate it

Paul White

Nice demo though

ypercubeᵀᴹ

@ErikDarling I know. Totally unprofessional.

Paul White

Have you tried being a SQL Pro

Erik Darling

22:33

@ypercubeᵀᴹ well the formatting certainly is

Paul White

How did your Pinterest tweet get 300 likes

Don't say SEO

Erik Darling

i have no clue

i've said much better things that hardly got a sniffle

Paul White

Non-deterministic

Charlieface

23:00

@PaulWhite Yes, works fine with a CROSS APPLY. I realized the second half didn't need it, you just need to force a Filter with a Startup, and I managed that with TOP, see dbfiddle.uk/TCqmGrt1 and brentozar.com/pastetheplan/?id=r1Ki-jIxi

I've now modified my answer with this new option

Anyway, bedtime. I've wasted enough of my time on APPLY today to last a lifetime

Erik Darling

this sort of reminds of that demo adam used to have

where startup expression predicates wouldn't go parallel

had about the weirdest syntax i've ever seen in it

i wonder if it would be useful here

Charlieface

23:27

@ErikDarling Do tell...

Paul White

The issue as I recall was parallel startup filters still opening their child operators, which was by design. We did heaps of hairy stuff with % modulus and stuff to work around other issues

The was a good Connect item for it but well

Erik Darling

23:48

i think i had applied it to stackoverflow tables at some point

lemme see if i can find it

gist.github.com/erikdarlingdata/…

Transcript for

The Heap™ – Consultancy ©®