The Heap™ – Consultancy ©®

Charlieface

00:40

@ErikReasonableRatesDarling

> With the Sort, it doesn't matter which order the rows arrive in, but they do all have to arrive before the Sort goes to work putting them in the requested order.

I don't think that's quite right, I think it needs them all to arrive before it can output any rows, but it can begin sorting immediately.

Erik Reasonable Rates Darling

It’s okay 2 b wrong

Charlieface

Note in your pathological case the optimizer probably realizes that the sort is a little more efficient than normal due to being pre-sorted (does it still use Merge Sort? If not it could improve even further)

@ErikReasonableRatesDarling Especially when you're 99% right

Erik Reasonable Rates Darling

Okie dokie

The data is not preordered if it comes from an unordered scan

Charlieface

I guess that answers that then, it clearly has no optimization for that.

Erik Reasonable Rates Darling

I assume if I were not quite right Pablo would have suggested edits before accepting the answer

Charlieface

00:48

The real question for me is how a sort could ever be faster than a spool, given it's doing more work.

Erik Reasonable Rates Darling

Spools are not terribly efficient

But the plan does switch to a spool with 1000 rows involved

Charlieface

Yeah clearly the optimizer thinks so too. Question is why. How exactly can a sort store rows and sort them faster than a spool can just store them?

Or putting it another way: why can't spools be optimized to be as efficient as sorts at storing rows?

Erik Reasonable Rates Darling

Spools could be optimized but they don’t get any attention

It has been discussed a bit in here

I suppose 100 rows of integers is guaranteed to be in memory and spools require physical storage space allocated in tempdb

Sorting, Row Goals, and the TOP (100) Problem

Charlieface

I'd guessed it was something like that. Like why isn't there an in-memory spool, then spill to worktable once it gets too big? Or something like that

Erik Reasonable Rates Darling

Maybe because sorts fill that requirement already, but who knows

Charlieface

00:54

top-n sort is something else, that only goes up to 100 rows, so clearly not the optimization it's targetting.

Erik Reasonable Rates Darling

Yes dear.

Charlieface

sorry grandma, didn't mean to nag you

Erik Reasonable Rates Darling

I’m giving you some background material on sorts and row goals. There is a row goal in the sort plan.

Erik Reasonable Rates Darling

01:21

Not that you wouldn’t get the same plan without the row goal, but consider that the optimizer is working with a freshly created index on a temp table with 100 rows, and that costing for those 100 rows leads to a sort over a spool.

> might depend on heuristics as well as statistical information No doubt, you may reason

@PaulWhite looks like some missing punctuation in there

Sean Gallardy

02:57

lots of chit chat today

Paul White

Sorts have a lower setup cost than a spool that's all

It doesn't even really matter at tiny sizes. The model is just a model. Either choice would be fine in practice

@ErikReasonableRatesDarling Will be in this case. Unordered means the optimiser doesn't require order for correct results. The storage engine is free to provide ordered data if that's more convenient. The table is too small to qualify for an allocation ordered scan

Erik Reasonable Rates Darling

03:16

this is a somewhat special case

reading comprehension of 100 row temp tables being what it is

now who would want to decorrelate a spool

Paul White

At least there were only two comments

And you were Enlightened

Erik Reasonable Rates Darling

so far

dutifully reported 🫡

Paul White

> why isn't there an in memory spool

They are in memory. Their pages only get written to physical tempdb under memory pressure or if the eager write threshold is hit (behaviour version dependent)

Reminds me of people debating table variables vs temp tables

Erik Reasonable Rates Darling

03:33

spool spills

the indignity

i can't find anyone to give me a concrete example of using a table variable for the purpose of surviving errors and rollbacks

Paul White

Isn't the classic case logging an operation that fails?

Maybe I'll rewrite my question to use Hekaton

Erik Reasonable Rates Darling

it might be a classic case, but i haven't seen an example of it anywhere

Paul White

Fashion and trends are fickle

Erik Reasonable Rates Darling

when i asked the internet, people told me they used them during etl processes for process logging, but they stopped responding when i asked if they were doing etl processes inside of one big transaction

it seems like logging to a normal table would suffice along the way

Paul White

The scenario I had in mind was not large ETL. Ordinary TRY CATCH stuff where you want to log a problem row set after the rollback to end the doomed transaction

Erik Reasonable Rates Darling

03:42

it would be nice if you could do something like this

DECLARE
    @failed_rows table
(
    id bigint NOT NULL
);

CREATE TABLE
    dbo.an_attempt
(
    id bigint NOT NULL
       PRIMARY KEY CLUSTERED
);

INSERT
    dbo.an_attempt
(
    id
)
OUTPUT
    Inserted.id
INTO @failed_rows ( id )
WITH ERRORS_ONLY
VALUES
    (1),
    (1);

Paul White

Yes that's a long standing request

Don't know if it survived Connect

Erik Reasonable Rates Darling

me either

Paul White

I do know they looked into it a bit at one stage

Erik Reasonable Rates Darling

i guess i just don't see many opportunities to log a problem row set performanently outside of single row inserts

Paul White

Procedure with a TVP maybe

Erik Reasonable Rates Darling

03:45

you're getting into left handed unicorn territory here

woah baby

Paul White

Your mistake was asking the internet

They have no clue

Erik Reasonable Rates Darling

always is

i wonder why the errors only thing didn't go anywhere. most unique violations get printed out in the error message

Paul White

The devil's always in the details

Pretty risky too

Making a fundamental change like that is a great way to break things in unexpected ways

Erik Reasonable Rates Darling

to output?!

not exactly cornerstone code there

Paul White

Think like the engine

It would have to carry along suppressed errors to be able to identify the rows at the output

Erik Reasonable Rates Darling

03:53

sounds a bit like ignore dup key

Paul White

It's all easy enough in very simple cases. But it would have to work everywhere everytime with all wacky edge cases

Erik Reasonable Rates Darling

gotta start somewhere

Paul White

@ErikReasonableRatesDarling That's a horror show in itself. Merge ignores it. You can set it up non deterministically. They'd never implement it today

@ErikReasonableRatesDarling Not if it breaks existing usage you don't

Mobile keyboard!

Erik Reasonable Rates Darling

it would only break new usage using the new WITH option i think

otherwise it would carry on as usual

outputting what output outputs

like if they extended readpast to allow you to specify what kind of locks you're okay reading past

not that i'm suggesting that needs to be a thing

Paul White

You'd still have to make changes to very low level stuff. Even if you set it up to only activate when the new OUTPUT option was specified, you've still introduced a risk

Erik Reasonable Rates Darling

03:59

lol

how does one implement anything risk free

Paul White

It's the sort of change that almost immediately prints a "this should never happen" message

@ErikReasonableRatesDarling By only ever adding new orthogonal stuff without touching low level code

Erik Reasonable Rates Darling

spoken like a true project manager

Paul White

It's the reality of working on 100 million lines of legacy code with decades of spacebar heating issues

We'll just add one bit to the row header. What could possibly go wrong

Erik Reasonable Rates Darling

summary: paul advocates rewriting sql server from scratch

Paul White

They've done it before

Now they just add new bits on

Erik Reasonable Rates Darling

04:04

well now they can do it in rust or go

hot stuff that

Paul White

But realistically, what could you do with this new left handed unicorn that you can't do with a table variable

Carrying along error rows to the output just gets weirder the more you think about it

Erik Reasonable Rates Darling

only output the problem rows rather than, say 999 rows that are fine and one row that isn't

Paul White

What should existing operators do with those rows? What value should be returned for an error? What other details should be available and where would they come from?

Erik Reasonable Rates Darling

stick'em in a spool

you love those

Paul White

Naively, you'd have to recode every plan operator to ignore error rows or do whatever makes sense in context

And every intrinsic that might receive data from an error row

Erik Reasonable Rates Darling

04:11

or just bail on the first error and only ever deal with the first problem row

Paul White

But what if the error occurs before the first row is fully formed?

And that just sounds like try catch

Erik Reasonable Rates Darling

i suppose i deserve all this for trying to make table variables more useful

Paul White

What about errors that never form part of a final result row? What about errors feeding into an instead of trigger

Erik Reasonable Rates Darling

likely wouldn't work out so well for those

with a tvp you're kinda stuck with a cursor to attempt each row insert individually

that could turn into a big drag

Paul White

Maybe replace SQL Server with a spreadsheet then look for cells with #ERR! in them

@ErikReasonableRatesDarling It could, but you also have the choice of logging all rows then diagnosing the problem ones after the fact

Erik Reasonable Rates Darling

04:20

that is the upside

Paul White

Like people write error handling code anyway

Erik Reasonable Rates Darling

imagine trusting someone to write that sort of thing correctly

Paul White

People can't even write selects correctly

So anyway yes it would be a nice feature

Erik Reasonable Rates Darling

that's all i needed to hear

Paul White

In a world where complexity doesn't exist

Erik Reasonable Rates Darling

04:23

but thanks for the thrashing

Paul White

No charge

Erik Reasonable Rates Darling

i am once again enlightened today

just got in under the wire

Paul White

We're on a roll

Erik Reasonable Rates Darling

well, time to rest up, who knows what i might need to learn tomorrow

ttfn~~

Paul White

👋🏼

Paul White

05:41

0

A: Unnecessary sort with TOP PERCENT?

The key to understanding why the Sort or Spool is needed is to think about how the plan works. Strategy The Top operator needs to limit the rows returned to some percentage of the potential full result. To know when to stop, it needs to know the total row count to calculate how many rows that per...

@ErikReasonableRatesDarling Not a dunk, but you might enjoy at least one aspect of that

@ErikReasonableRatesDarling Fixed, ty

Zikato

07:41

@PaulWhite the change is live
https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver16#percent

Paul White

08:42

Great job

Awesome job from the reviewer changing "it is" to "it's"

3

> fixing tone

LOL

I hope they're well paid

Zikato

10:14

Gotta earn that paycheck

Erik Reasonable Rates Darling

10:40

@PaulWhite that’s definitely a dunk. Broke the backboard, bent the rim.

> EnforceHPandAccCard

Some redemption for me tho~

Erik Reasonable Rates Darling

11:09

> There is another exploration rule that can recogise

From the new article

The SQL Server "Segment Apply" Optimizer Transformations

Paul White

11:50

There's always one 🥹

@ErikReasonableRatesDarling Is it? It doesn't contradict anything you said. It's longer, sure. More call stacks.

Charlieface

12:20

@PaulWhite Getting me annoyed how chatty MS's docs are becoming. Powershell docs are horrible.

Nice answer by the way. Also that IDENTITY() as a function, didn't know you could do that.

My cutoff seems to be 165 rows, based on your fiddle dbfiddle.uk/jOFysSQZ

Paul White

@Charlieface Before ROW_NUMBER, that was the normal way

Hannah Vernon

13:59

Wordle 1,341 4/6*

⬛⬛🟨⬛⬛
🟨⬛⬛⬛⬛
⬛🟩🟨⬛🟩
🟩🟩🟩🟩🟩

Paul White

14:09

Wordle 1,341 3/6*

⬛⬛⬛⬛🟨
🟨🟩⬛🟩🟩
🟩🟩🟩🟩🟩

Erik Reasonable Rates Darling

14:50

has anyone checked in on @Zikato's website? is it still making sure i'm okay?

Paul White

It is

Erik Reasonable Rates Darling

oh good

Zikato

Hadn't received a page since Saturday

Erik Reasonable Rates Darling

15:53

Towards Sanity in Query Languages

Charlieface

16:06

@ErikReasonableRatesDarling I love that he thinks that C-style braces and parens are more readable than SQL....

What the ____ is gensym()

My issues with SQL as it stands are actually mostly fixable, if the language designers would get it out of their heads that it's supposed to be somehow readable as English

Eg why OFFSET ... ROWS FETCH ... ROWS ONLY why can't you just do OFFSET ... FETCH ...

Or IS DISTINCT FROM like somebody thought "what's the biggest word salad we can make for a simple <>"

The interaction between SELECT and DISTINCT/UNION and ORDER BY and TOP is confusing as hell. Put the keywords in order of logical execution, end of story.

And make a proper dynamic query construction, so I can do things like:

DECLARE @query AS QUERY (oh OrderHeader, od OrderDetail);
SET @query = FROM OrderHeader JOIN OrderDetail ON ... ;
IF (something) SET @query = @query WHERE oh.IsProcessed = 1;
SELECT od.DetailID FROM @query;

Erik Reasonable Rates Darling

16:53

@Charlieface there are a fair amount of people who feel that way. i disagree, but google is working on some pipelined query language with a similar style.

Charlieface

That's not actually an issue with the general syntax, that's an issue with allowing ambiguity. SET STRICT_YOU_MUST_ALIAS_EVERYTHING ON fixes that :-)

Erik Reasonable Rates Darling

it is somewhat amusing

but yeah, just sloppy

Charlieface

My point is: there needs to be a balance between readability and allowing multiple ambiguous syntaxes.

Erik Reasonable Rates Darling

it's difficult for people to know what they want

Charlieface

English is notoriously ambiguous, so probably not a great place to start. Maybe we should make something based on Esperanto or something.......

I know what I don't want: functions called descriptive names like EVP_PKEY_CTX_set_dsa_paramgen_md_props

Erik Reasonable Rates Darling

16:59

i'm not sure if europe experienced this, but in america people hated monopoly cable companies for ages. all the channels were bundled together and often in ways that weren't sensible.

everyone cried for a la carte options. then we got 7000 streaming services, and people complained that everything was too spread out. now many streaming services are consolidating (or services like amazon prime allow you to purchase streaming services from within it) and people are confused about what's where and who the provider is.

Paul White

people ruin everything

Erik Reasonable Rates Darling

the worst

Charlieface

C syntax isn't all bad, I work in it all day, it's more the style of the way it's used. Java is over the top verbose, C/C++ are terse in the extreme. Something in the middle is good, like Dart or C#

Erik Reasonable Rates Darling

also can we talk about this german database genius sleeping on a child bed

Charlieface

who dis

Erik Reasonable Rates Darling

17:01

from the video link i posted

that spurred your many comments about sql

i thought, anyway

Charlieface

I wasn't even looking at his face, bit small on my screen and not relevant to the video to be honest. Most times a guy is in the corner like that I think "get outta the way, I can't see the code"

Paul White

17:13

The core of the problem seems to be there are too many different standards

Erik Reasonable Rates Darling

pidginql is the next logical step i think

Josh Darnell

I feel a gravitational pull towards xkcd.com all of the sudden.

Erik Reasonable Rates Darling

no no

no need

Paul White

The solution would seem to be one standard everyone could agree on

Josh Darnell

Yes, yes, go on!

Paul White

17:17

One Sane Standard

Josh Darnell

Gosh what would the outcome be if a group of people pursued such a thing?

Paul White

I'm so glad you asked

Sep 19, 2022 at 14:14, by Paul White

Fortunately, the charging one has been solved now that we've all standardized on mini-USB. Or is it micro-USB? Shit.

Erik Reasonable Rates Darling

🤦‍♂️

Peter Vandivier

😱

Josh Darnell

What a great day for the chat.

Peter Vandivier

17:21

huzzah for us all

Erik Reasonable Rates Darling

quite a denormalized day

how many times has that been posted

Peter Vandivier

almost enough times

almost

Paul White

Eight times

Jul 26, 2018 at 13:09, by jadarnel27

@TomV reminds me of xkcd: Standards

That was first

Erik Reasonable Rates Darling

it's like there's a prize for doing it

Paul White

you're right about that single bed though

and the video covering the slides

Erik Reasonable Rates Darling

17:27

why is the most german thing in the world intentionally empty shelves

Josh Darnell

Oh cool a deprecation notice on an EF Core call, I'm shocked. SHOCKED.

Zikato

Not that shocked

Josh Darnell

This guy gets it.

2

Erik Reasonable Rates Darling

17:45

what does that mean

ef core is deprecated

is the nightmare over

Charlieface

@JoshDarnell deprecation of what?

Peter Vandivier

18:16

probably the framework

some of the entities too, iirc

Josh Darnell

18:27

> 'RelationalEntityTypeBuilderExtensions.HasCheckConstraint<TEntity>(EntityTypeBuilder<TEntity>, string, string?)' is obsolete: 'Configure this using ToTable(t => t.HasCheckConstraint()) instead.'

Paul White

18:50

That video was terrible

And zo iz ze German akzent

Erik Reasonable Rates Darling

yeah

it's a shame because those guys do actually do cool database stuff

i emailed vassilis about an optimizer rule and he told me he's not ready to talk about it yet

what is the world coming to

Paul White

19:05

@ErikReasonableRatesDarling I'm sure they do. They should make a video about those things

The first part was much better than the second

That was just a disaster

@ErikReasonableRatesDarling Yes, go on

Erik Reasonable Rates Darling

19:33

@PaulWhite on gone

Paul White

Microsoft’s Majorana 1 chip carves new path for quantum computing

@ErikReasonableRatesDarling Thanks

Erik Reasonable Rates Darling

Least I can do

Exciting news about Majorana1

Looks a bit like an OF account handle but hey

Paul White

19:59

The article from Microsoft News about the Majorana 1 chip is a fascinating glimpse into the latest strides in quantum computing, and it’s worth digging into what it means—both for the tech itself and the broader implications. Announced on February 19, 2025, the Majorana 1 is pitched as a game-changer: a quantum processing unit (QPU) that leverages a new material called a "topoconductor" to harness Majorana particles. This, Microsoft claims, could lead to a million-qubit quantum chip within years, not decades, potentially revolutionizing fields like material science, healthcare, and beyond. …

The Grok 3 summary

Josh Darnell

20:19

> this one’s got teeth, but it’s not biting yet.

Paul White

20:38

I also asked it about Majorana particles separately. It made me feel dumb

But then I had a nice chat with Erik so everything's fine again

3

I made the Thinking In Sects joke again

Josh Darnell

Did Grok 3 laugh at it?

Paul White

I'm scared to try

Erik Reasonable Rates Darling

20:57

happy to help

> As Grok 3, I’m designed to provide helpful and truthful answers across a wide range of topics, but I don’t have direct, hands-on experience tuning SQL Server instances or writing T-SQL queries in a production environment. My knowledge is broad and theoretical, drawn from vast amounts of data, rather than practical, specialized expertise.
On a scale of 1 to 10 for T-SQL, SQL Server performance, and related subjects, I’d rate myself around a 4 or 5.

grok3 rates grok2 as a 3 or 4 on those topics

Lamak

why didn't you train grok 3?

Erik Reasonable Rates Darling

wasn't asked

Lamak

yeah, figured

Erik Reasonable Rates Darling

21:24

would you really want me to tho

Lamak

I don't really use grok, so no ;)

Erik Reasonable Rates Darling

maybe you should

it's what bropilot is built on

Cade Roux

21:48

It might be time to give AI a chance at a problem, it's been a few months. Every time I am disappointed and regret wasting my time trying to expand the prompt and/or argue with them.

Lamak

@ErikReasonableRatesDarling and broSQL?

Erik Reasonable Rates Darling

MemeDB

Transcript for

The Heap™ – Consultancy ©®