The Heap™ – Consultancy ©®

Paul White

00:09

I wouldn't say you are unreasonably difficult

Michael Green

01:33

'Morning all.

Hannah Vernon

02:52

@PaulWhite thanks, i think

J.D.

03:50

03/28's:

Wordle 647 3/6

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

J.D.

04:06

Sunrise

Zikato

06:20

Morning

Wordle 648 5/6*

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

Paul White

07:35

@HannahVernon Maybe. It's hard to remember how discoverable it was before I knew how to get to it from the 'impact' box on user activity page summary

I think if you have a non-comment flag declined you get a more direct link in the message

ypercubeᵀᴹ

08:13

@HannahVernon yeah, it's hard to find if you don't know where it is: Activity -> IMPACT section, "helpful flags" is a link

Charlieface

09:48

I've always thought that tiny link was stupid and completely undiscoverable. I keep forgetting where it is and I go on that page every couple weeks.

Paul White

10:00

@HannahVernon By the way, this link works for everyone dba.stackexchange.com/users/flag-summary/current

2

That link appears on the help pages dba.stackexchange.com/help/declined-flags and dba.stackexchange.com/help/privileges/flag-posts

Charlieface

10:16

That still isn't very discoverable. You would think there would just be a Comments button on the left along with Answers Tags Badges etc, but no, Someone needs to go back to UX school.

Paul White

It's possible it's deliberately hard to find.

@Charlieface Comments?

Seems like a suboptimal design to me, but what would I know

Charlieface

10:52

@PaulWhite Sorry meant to say Flags, but yeah Comments is missing also.

John K. N.

11:24

Wordle 648 3/6

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

Morning

Had a good starting word today. Alas, the sought after word wasn't....

Zikato

11:49

Any GitHub expert know why it shows diffs where there are no diffs?

I've used both manually cloned version and the Edit button in the GUI. The manually cloned repo doesn't show these changes on commit, only when Pull Request is created

My guess was encoding or different line ends. But no warning from SQL prompt on different line endings and for encoding it seems too random

J.D.

@Zikato Yea that's what I was about to suggest or for some reason different whitespace characters? Could throw both into Notepad++ or something and turn on the Show All Characters to double check. Weird.

Zikato

I have autocrlf so it would have to be the original that has the mix. But it's weird that even GitHub gui would change that

well, it was a roundabout way of saying: I blame @ErikDarling
because he's the maintainer of that repo

J.D.

lol

Charlieface

12:10

GitHub's diff is garbage, given it's based on the standard file-based diff command. We need a diff tool that actually understands syntax, so it can ignore insignificant whitespace, and insignificant reordering of declarative syntax.

The amount of times I'm looking through diffs of XML and I realize the only changes are attribute="value"</tag> and attribute="value" </tag>

Paul White

Which is that programming language in which whitespace is important?

Charlieface

For some reason the XML formatter in VS has changed to add whitespace after the last attribute. So annoying.

@PaulWhite Python off the top of my head. And XML at the beginning or inside node names.

Or for that matter any language inside a quoted string

Paul White

Probably 🐍 I was thinking of

Charlieface

?

Paul White

Python = 🐍

Whitespace (programming language)

Whitespace is an esoteric programming language developed by Edwin Brady and Chris Morris at the University of Durham (also developers of the Kaya and Idris programming languages). It was released on 1 April 2003 (April Fool's Day). Its name is a reference to whitespace characters. Unlike most programming languages, which ignore or assign little meaning to most whitespace characters, the Whitespace interpreter ignores any non-whitespace characters. Only spaces, tabs and linefeeds have meaning.A consequence of this property is that a Whitespace program can easily be contained within the whitespace...

Erik Darling

12:21

@Zikato often that's because of tabs vs spaces

Zikato

But that wouldn't be changed by the github edit gui. I also look at tabs vs spaces in my code editor

Hannah Vernon

@PaulWhite cheers!

Zikato

well, adding ?w=1 after the url ignores the whitespace, but I can't force people to do it

Paul White

Haskell was the other one

John K. N.

The difference between 0D 0A and 0A 0D and 0D

Zikato

12:24

Anyway, here it is. I'd be grateful if you could take a look @ErikDarling github.com/amachanic/sp_whoisactive/pull/94/commits/…

Erik Darling

i just looked

Zikato

in that case I'm grateful

J.D.

Oh noes, someone disagrees lol

Paul White

Silliness wrt tabs will not be tolerated!

Erik Darling

you're hitting the same thing as: github.com/amachanic/sp_whoisactive/pull/89/files

Zikato

12:25

yeah, well at least now I know to add the ?w=1

J.D.

@PaulWhite wrt? I'm not familiar with that acronym

Zikato

@PaulWhite Converted me months ago to spaces. Mainly due to blogging

Paul White

@J.D. with respect to

J.D.

Ah

Paul White

@ErikDarling What is that same thing?

Erik Darling

12:27

the inconsistent line breaks

that @Zikato is talking about

from the pull request @Zikato made just now

because @Zikato hit an error converting xml in who is active

poor @Zikato

Zikato

Won't you think of the people with more than 128 nodes in their XML plan?

Paul White

Sounds like a query needs tuning

Erik Darling

i do in all my stored procedures

can't help adam being selfish

is try_cast available in sql server 2005?

your change might break the documentation

> Version 11.32 - July 3, 2018 (ALL SQL Server versions 2005+)

Paul White

Added in 2012 IIRC

Erik Darling

yes, yes

Paul White

12:33

Yeah I saw the joke but you know

It appears TRY_CAST is not a reserved keyword but TRY_CONVERT is. Never noticed that before.

Erik Darling

yep

wild, huh?

Zikato

I like Glenn Berry's approach of having a script per SQL version

Paul White

way out wiggy weird

Erik Darling

go contribute to glenn berry's repo

Paul White

ha ha ha

Who even uses CAST anyway.

Erik Darling

12:35

fools

leading comma people

Paul White

COALESCE types

Erik Darling

✝

be gone

Zikato

I mean why pay price for a backwards compatibility and not use the new features

Erik Darling

you can do both with dynamic sql

Paul White

I hear that's vulnerable to injection

Time for a cultural joke

Erik Darling

12:38

everyone keep your clown noses on, i'm not gonna talk about anyone's mom anymore

Zikato

when you're a hammer all the problems look like a dynamic sql

Paul White

that probably makes more sense in Czech

Erik Darling

well jesus was a carpenter

i bet when he comes back he's gonna love dynamic sql

Paul White

there's not much in the bible about his feats of carpentry is there?

or are there a few verses about a particularly nice cabinet he made?

it's been a while

Erik Darling

got me

i don't think he even made crosses

Paul White

12:44

made some people quite cross

Erik Darling

not hard to make italians angry

Paul White

made a few mistakes along the way

but nailed it in the end

Hannah Vernon

Wordle 648 3/6*

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

Sometimes you just get a good start, hey

Erik Darling

still blew it

Paul White

yeah that totally should've been a two

Hannah Vernon

12:47

I was thinking it would have been because my 2nd guess was fairly uncommon

Erik Darling

Zikato

@ErikDarling so is that a "no" on the PR?

Erik Darling

what would be the least problematic line ending?

Zikato

CRLF windows, LF Linux

Paul White

That dialog box is so stupid

Hannah Vernon

12:48

I go with typewriter line endings

Paul White

It pops up sometimes though I only ever edit my own code

Erik Darling

@PaulWhite have you tried being consistent?

Paul White

Humans really can't standardise on anything

Hannah Vernon

Not even humans

Erik Darling

@Zikato i don't know, adam usually surprises me with what he does and doesn't care about.

Zikato

12:55

Adam: I'm pretty sure the whitespace issues are due to Windows-style line endings. I flipped the proc over to Linux-style during the last release cycle but maybe it's not consistent.

Erik Darling

try_flip

Paul White

seems like linux was a mistake

Erik Darling

if you say linux backwards you get windows line endings

Paul White

with just a line feed, words on the next line would be horribly indented

I just don't understand Linux at all

it's worse than tabs

Erik Darling

i would also blame tabs for that

Paul White

13:02

you can't be just feeding lines all willy-nilly, the carriage must be returned

Sean Gallardy

If you don't return it, then it's just stealing

Lamak

Wordle 648 4/6

⬜⬜⬜⬜🟨
🟨⬜🟨⬜🟩
⬜🟩⬜🟨🟩
🟩🟩🟩🟩🟩

Erik Darling

⬜⬜⬜⬜⬜
⬜⬜⬜⬜⬜
⬜⬜⬜⬜⬜
⬜⬜⬜⬜⬜
⬜⬜⬜⬜⬜
🟩🟩🟩🟩🟩

J.D.

13:21

@PaulWhite Tabs > Replication tho?

Sean Gallardy

putting multiples of 3-4 spaces is evil

Erik Darling

tabs are noxious

Hannah Vernon

Not as bad as commas at the end of lines though.

John K. N.

writing with line feed only
                                      is what happens when the lever
                                                                                    on the on the typewriter is broken

Josh Darnell

13:38

> The reason I post everything publicly is so that you can answer your own questions for stuff like this. I don’t even require an existing database to test – the script creates a new database for you. Please stop asking me to do additional work here and on LinkedIn – the idea of this post is so that you can do your own testing. Thanks for understanding.

Sean Gallardy

13:58

@HannahVernon I was hit up yesterday to look at why SQL Server wasn't shutting down, said it just say there for 4 hours doing nothing but won't shutdown. Sounded familiar. Guess what I found on one of the threads.... isitservicebroker.com

Hannah Vernon

LOL

I have no analytics or any server side tracking for that site, so it's nice to hear someone mention it

Sean Gallardy

I saw it waiting on service broker in notify shutdown and was like... yeah, I know someone that would appreciate this

Charlieface

14:32

If you create a trigger on master for the event ALTER_DATABASE, which contains the statement WHILE (1=1) WAITFOR DELAY 23:00:00; and you execute an ALTER DATABASE, will all other possible DDL and DML statements on that database get held up with a Sch-M lock? Asking for a friend...

mustaccio

4

Sean Gallardy

14:50

@Charlieface You need better friends

Zikato

15:04

@mustaccio Impressive!

Charlieface

asking for a friend

== English == === Pronunciation === === Phrase === asking for a friend (idiomatic, humorous) Indicates that a question is embarrassing by pretending to be asking on behalf of another person. 2016, "Celebrities React to Presidential Town Hall Debate 2016", Just Jared (Oct 9, 2016): How many more of these #debates do we have to suffer through? Asking for a friend. — Eva Amurri Martino (@TheHappilyEva) 2018, "Lakers survive Ball's gaffe to beat Thunder in OT". — LeBron James (@KingJames) "So what really is "in the shooting motion" rule?!?!?" James wrote on Twitter. "Asking for a friend...

Sean Gallardy

I know, I was being funny... well I thought I was being funny

Josh Darnell

15:31

@SeanGallardy I thought it was funny 😁

Sean Gallardy

I knew I liked you, Josh

Erik Darling

pertinent to the sp_whoisactive discussion earlier, does anyone know how to configure github so that on commit or merge, some action runs to clean files? ideally looking to normalize line endings, replace tabs with spaces, and probably clean up trailing white space on new lines.

Charlieface

@SeanGallardy quora.com/Why-is-it-so-difficult-to-convey-your-message-in-type

@ErikDarling git rm -r *

Joking...

Erik Darling

looks legit

Charlieface

@SeanGallardy Looks funny now that I realize you knew. I guess my response was even funnier.

Erik Darling

15:46

Mar 14 at 14:59, by Erik Darling

i thought i was funny until i met you

swasheck

looks arent everything

Erik Darling

what do you have frog eyes

Sean Gallardy

@Charlieface JEAGL in proper chat fashion

mustaccio

16:04

@ErikDarling github.com/marketplace/actions/lint-action

Erik Darling

@mustaccio thanks

dumb question: does it support sql? i don't see it listed here

or rather, i see a lot of language specific things, but not sql

Zikato

16:27

@ErikDarling I don't have anything at ready, but I think I should be able to help with those

Erik Darling

i thought you already did stuff like that

Zikato

yeah but in Azure DevOps, not GitHub Actions. And there are several tiers - you can set the line endings with .gitattributes. Replace tabs with spaces and trailing ws would be a powershell either in githook or in the GitHub workflow

mustaccio

@ErikDarling It supports Prettier, which has a SQL plugin. Don't ask me how to put this all together though

Unless you want to get invoiced, that is

Erik Darling

16:58

@mustaccio you can send the invoice to adam machanic, ha ha ha

J.D.

19:26

Anyone using Row-Level Security in SQL Server?...like it / hate it?

Sean Gallardy

20:04

I mean, it's ok

Erik Darling

as long as you don't use a scalar udf for the security function it's tolerable

perhaps it's less icky with froid, but i wouldn't chance it

J.D.

@ErikDarling True, definitely still room to make them a performance bottleneck even with single statement TVFs. E.g. I'm doing one right now that references a recursive CTE that's a little iffy at the moment.

Erik Darling

yes you always seem to find the most obtuse way to do things

J.D.

😆 obtuse is subjective if we aren't talking triangles.

I think I might've found a bug with RLS. Seems like if you reference an object that has an RLS predicate against it, inside of a different TVF used for a separate RLS predicate and policy, that second policy doesn't get applied.

Will probs post a question when I get a chance to create a clean set of repro scripts.

Sean Gallardy

Look at this guy, making a repro and scripts and being super helpful

#1 customer right there

J.D.

20:19

lol I try when I can.

Sean Gallardy

I think it's more you can when you try ;)

Charlieface

@J.D. I don't use it, but seen enough to know it isn't really a security feature, more of a convenience for querying the correct rows without having to add extra code.

Don't expect it to stand up to someone with permission to write raw SQL queries, or to able to accurately time query performance. There are loads of side-channel attacks.

@J.D. Sounds interesting, what was it roughly?

Sean Gallardy

@Charlieface 100% this.

Charlieface

20:37

@SeanGallardy The real bugbear is that it has side-channel attacks even with indirect webserver calls to parameterized queries. Then again, I often see login pages with no side-channel protection for index lookups on usernames and password hashes, so it's the least of our worries.

J.D.

20:50

@Charlieface Fo' sho'. Even worse, I've worked with databases that stored logins with plaintext passwords in a field called Password lol. And there were some higher level credentials that really shouldn't've been stored in there. (Fortunately not where I'm at these days.)

Charlieface

@J.D. And here I am today trying to make head or tail of AWS authentication procedure, which uses SHA256 hashing no less than 6 times one after the other, to no apparent reason.

J.D.

@Charlieface To try to be succinct: 1. Create an RLS predicate on a TVF on a Table. 2. Create a View on that Table. 3. Create a separate RLS policy and predicate on the View using a separate TVF that selects from that Table with a WHERE clause. 4. When selecting from the View, I noticed that WHERE clause in that second RLS predicate's TVF isn't being applied.

J.D.

21:07

On a completely unrelated topic, is there a way to actually stop automatic restarts from Windows updates in Windows 11? I legit had it restart 5 days in 1 week for updates lol.

Charlieface

CMD window for /L %n in (1,0,10) do (timeout /t 55 & shutdown -a) cancels the shutdown every 55 seconds

J.D.

hah, outside the box solution

Charlieface

I actually use that often because our endpoint management forces a restart once a week, and I'm often running stuff overnight.

0

Q: Can we use SQL Server STRING_AGG() in queries using GROUP BY ROLLUP ()

TL;DR: Can we use STRING_AGG aggregate function in a query using GROUP BY ROLLUP(...)? I am converting old code that used the STUFF( (SELECT...FOR XML) ) approach, so that it uses STRING_AGG instead. I cannot seem to use STRING_AGG when using ROLLUP, as I get the error Aggregate functions that ...

@SeanGallardy Why on earth can't STRING_AGG merge sub-aggregates?

Erik Darling

21:50

Sean doesn’t not dilly nor dally with query business

You’re better off asking Ol’ Debuggy

Sean Gallardy

@Charlieface Haha what Erik said, unless it's super interesting and has more to do with the OS side. I would venture a guess of "someone didn't program it in"

Paul White

@ErikDarling he's not here tho

Erik Darling

@PaulWhite au revoir

Charlieface

@ErikDarling In what accent does Debuggy rhyme with White

Erik Darling

22:09

Maybe something Caribbean

Anyway, his real name is Pablo Blanco. Don’t tell anyone outside chat though.

Sean Gallardy

I think there was some mod interferences in the star elections, I no longer see my Tabs > Spaces item in the list.

J.D.

@ErikDarling In SHA256 for Charlie: bc4a63fcc4ab5731d3906e2debdd4fcd4ec698037d535ecf905dd1ee989f350e

@SeanGallardy Btw I starred you on that, since it was a very sensible remark, that somehow survived removal.

Erik Darling

@SeanGallardy mods aren’t always wrong

Sean Gallardy

@ErikDarling Mods were a mistake

@J.D. This man gets it.

J.D.

@SeanGallardy I knew I liked ya for a reason.

Hannah Vernon

22:23

@ErikDarling I believe the word is "ever"

Sean Gallardy

I seriously don't understand why someone would press the spacebar 4 times when they could press tab once

Hannah Vernon

who presses 4 spaces? not i. I simply hit tab et voila. 4 spaces.

2

Sean Gallardy

then why use spaces if you already press tab!?

Hannah Vernon

because spaces aren't fucked

Sean Gallardy

"I press tab because I prefer spaces"

lol

Hannah Vernon

22:24

no seriously though, spaces never show up weirdly in any editor

tabs make stuff look very weird all the time

Sean Gallardy

I use two editors, notepad and VS. Never have any issues with tabs. I could see if you're using 50 different editors but also then you're doing it wrong :D

Hannah Vernon

a pet peeve is opening project code and seeing stuff that just doesn't line up

Sean Gallardy

I hear you there

Hannah Vernon

what if you have 50 different devs using 50 different editors?

then what, huh?

Sean Gallardy

Just wait until I open the can of worms that is curly braces

Clearly you have no standards so your devs probably suck

Hannah Vernon

22:26

I love me some curly braces in all the right places

2

Sean Gallardy

L O L

Hannah Vernon

@SeanGallardy don't tell them that

J.D.

OoO curly braces and parenthesis being inlined drives me crazy

Hannah Vernon

they are so important they deserve their own line

Sean Gallardy

I'm all about:

MyFunc(stuff)
{
}

Hannah Vernon

22:27

with a CRLF at the end

yah

Sean Gallardy

None of this:

JFCWTF(stuff){
}

J.D.

A la the latest C# style I've been seeing where they're unaligned.

Hannah Vernon

@SeanGallardy 100000% true

Sean Gallardy

We have some common ground, curly braces

Hannah Vernon

devs hate pressing any extra ~~extra~~ keys

Sean Gallardy

22:28

lol

Hannah Vernon

god I hate chat markdown

6

it's almost as bad as tabs

J.D.

@HannahVernon I don't think I saw a consensus on where Replication falls into the mix though.

Paul White

22:47

@HannahVernon Three hyphens/dashes

It puzzles me Sean can be so smart about some things while being monumentally dumb about others e.g. tabs

Like it's intellectually impossible to see the difference between pressing the tab key and having tab characters embedded

How would that even be possible. It's just madness

Maybe this is what working with Availability Groups does to a person

Erik Darling

tabs are landmines

just hanging out, formatting some code

delete three spaces, delete the fourth and your cursor jumps back a full tab stop

now you're an odd number out and you have to hit space again

Paul White

Worse than replication

Erik Darling

set yourselves free, sheeple

J.D.

@PaulWhite cool, I respect it, even though I disagree.

Paul White

Ok they're equally bad

J.D.

23:01

Thanks for the cheese tonight btw

Paul White

Looks Photoshopped

@CommunityNotes

Erik Darling

what is this about

the point really comes across

Paul White

Looks like a line feed carriage return problem

Erik Darling

twitter.com/erikdarlingdata/status/1641216198477225984?s=20

Paul White

v good

J.D.

23:13

@PaulWhite actually legit went on some grilled burgers tonight. P solid.

Hannah Vernon

@ErikDarling omg I use that all the time. Plus I use this for tabs\spaces at the end of lines: (\t| )+\r\n and replace with \r\n

I should do a blog post with all the regex replaces I use

Erik Darling

@HannahVernon you should. I do a lot of cleanup like that.

Transcript for

The Heap™ – Consultancy ©®