The Heap™ – Consultancy ©®

12:34 AM

0

If the column is declared as varbinary(max) then you can use the little known .WRITE syntax to do this. I have used a Nums function to generate the correct amount of rows. There are a number of different versions of this function out there, see https://sqlperformance.com/2021/01/t-sql-queries/num...

Have you ever used .WRITE in anger? Send in geolocated selfie to...

I honestly can't think of a single good reason to ever be doing this, but the customer is always right.

J.D.

@Charlieface ...the customer is always .WRITE 😎

3

Peter Vandivier

2:10 AM

i'd be a lot more tickled to discover this emoji exists if windows had like... flag emoji support emojipedia.org/dino-cat

Zikato

6:28 AM

Morning

John K. N.

8:38 AM

@PaulWhite yes

morning

Wordle 633 4/6

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

Zikato

9:36 AM

Wordle 633 4/6

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

Hannah Vernon

11:46 AM

Wordle 633 3/6*

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

Erik Darling

12:35 PM

🟩🟩🟩🟩🟩
🟩🟩🟩🟩🟩
🟩🟩🟩🟩🟩
🟩🟩🟩🟩🟩

Paul White

dork

Erik Darling

@PaulWhite donk

Paul White

@ErikDarling DOINK

Erik Darling

dink

Paul White

🤾‍♂️

Erik Darling

12:46 PM

are you calling me the white artist formerly known as prince?

Paul White

Well that works just great on Windows lol

@ErikDarling It's a basketball player on iOS

🧺🏀

I forget which character you used to use for "dunk"

Erik Darling

oh

🤾‍♂️

Paul White

That's the fella

Erik Darling

sort of looks like sanskrit

Paul White

fixed

Apparently, it is emojipedia.org/man-playing-handball

"man playing handball"

Erik Darling

12:50 PM

"before being beheaded for losing at handball"

Paul White

It's a lot to pack into a few pixels, isn't it

I can sort of see how Chinese is such a compact language

Z is bound to have some sort of appropriate simplified/traditional meme for that

Erik Darling

who, @Zikato?

i remember when @Zikato had time for blogs and memes

now @Zikato is lost in the @Forrest

Paul White

Oh yeah, I forgot his full handle for moment, yes, @Zikato

Forrest must be a really tough boss

Erik Darling

you know his bark is worse than actually no i'm not doing this

Paul White

There's this sort of trend where people start to prefer paid work

I appreciated the bark pun btw

Erik Darling

12:59 PM

🙏

Paul White

I'm glad you decided to leaf it be

Oh no

Erik Darling

here we go again

has anyone noticed a weird question

Paul White

hah

Do you know, I almost considered installing my first ever AG today to look into that

And as if by magic, a Sean appears

Erik Darling

did he ever return, no he never returned, and his fate is still unknown

John K. N.

So the regulars in here would be @ErikDarling, @HannahVernon, @PaulWhite, @Zikato, @J.D., @JoshDarnell, @SeanGallardy, @mustaccio, @Forrest, @Charlieface, @CadeRoux, @TimStone, @Lamak, @mustaccio and @billinkc according to the listing at the top right.

Paul White

1:02 PM

@ErikDarling That user has never left a comment

Ever

Erik Darling

you may not like it, but this is what peak user looks like

Paul White

There should be some sort of award for that

Gosh yes, a Peak User badge

John K. N.

meta is your friend

Sean Gallardy

So this chat has peaked?

Erik Darling

years ago

Paul White

1:03 PM

@SeanGallardy Does that pique your interest?

Erik Darling

it's all decline now

Sean Gallardy

@PaulWhite only if it's high school

Paul White

@ErikDarling and then fall

John K. N.

So we should change the name of the room to The Peak™ – Consultancy ©®

Paul White

Though we call it autumn

Sean Gallardy

1:04 PM

It's time for spring cleaning, though

Erik Darling

when rome falls, so falls the world

Paul White

Time to buy a 🎻

That's a violin/fiddle

Sean Gallardy

Viola!

Erik Darling

a comment from @Charlieface has more upvotes than my great answer©

is it time to retire

Paul White

@SeanGallardy Did Nero viola while Rome burned?

Charlieface

1:07 PM

@JohnK.N. I'm an irregular regular :-) and I only found this place recently

Paul White

@ErikDarling He'll never be a peak user

Charlieface

@ErikDarling which one?

Erik Darling

1

Q: Why my clustered index causing deadlock (Keylocks and Pagelocks)

We are experiencing deadlock 2-3 times a day in SQL Server. Upon analysis, we have found that the deadlock is caused by a combination of keylock and pagelock occurring simultaneously. We are actually using EF to fetch data and the query involves more than 6 foreign key relations. But on some days...

Sean Gallardy

@PaulWhite I don't believe Nero was that with it

Paul White

@SeanGallardy It's hard to say, and he's not here to defend himself. Another one we'll never hear back from

Charlieface

1:09 PM

@ErikDarling Answering with a guess about what the query plan might look like, and me commenting please supply the plan? Yeah I can see why you might need to retire...

Sean Gallardy

Not sure he'd have much of a defence

Erik Darling

it's not a guess

Paul White

It's weird there's parallelism

Charlieface

You're probably right anyway, but Paul isn't going to forgive you.

Paul White

Eh? What would I need to forgive Erik for?

Erik Darling

1:10 PM

i pay my tithes

Charlieface

It's a guess in as much that we don't know which of the queries has the key lookup, or why there is parallelism (most likely those two are correlated).

Paul White

@ErikDarling Most prompt invoice payer ever

Charlieface

@PaulWhite For guessing an answer? We can guess all day long but "I never guess. It is a shocking habit,—destructive to the logical faculty."

sherlockholmesquotes.com

Erik Darling

well what else would cause an object-level shared lock in a select query

Paul White

Oh well 'guesses' based on experience and expertise are fine. Guessing at an answer in a comment is the thing

Unforgiveable

Erik Darling

1:13 PM

the deadlock xml is a disaster, parallel queries log an entry per thread or something

Charlieface

@PaulWhite That's an educated guess, for which one needs an education. Explains why I never do it :-)

Paul White

I have 3 votes left today

learn.microsoft.com/en-us/sql/relational-databases/xml/… Clearly those users are using the wrong SET options. Why is a different question, you would have to check the software they are using. There shouldn't be any good reason to use anything different than the docs anyway. — Charlieface 2 hours ago

Charlieface

@ErikDarling Could be a different statement in the same transaction, for a start. Could be an indexed view update, could be an ID column which isn't actually unique

Erik Darling

is betting allowed here?

Paul White

I think I'm going to flag that as Not A Comment

Charlieface

1:15 PM

@PaulWhite We've had this before: I'm happy to guess in a comment, because a guess is not an answer. I've no idea where else to put "Not An Answer"

And it doesn't get to the root cause "why?"

Sean Gallardy

The site needs unofficial not answer, answers

Charlieface

Or maybe a "community answer in progress" where people can contribute. Oh wait...

Paul White

@Charlieface Why do you persist with this. We've had this debate. Local policy is firmly in favour of not putting guesses at an answer in comments.

Erik Darling

only the op can choose the answer from what is submitted

comments can't be marked as answers

Paul White

14

Q: Is the comment-only answer acceptable?

I've just read a post that's really mundane. It's one of those "just read the error message" type questions. There's an obvious, correct, and incredibly trivial answer I can respond with; but I'm not sure the original post is high-quality enough to deserve a full post in reply. So... Is the comme...

discussion faq comments

I really don't get it. If the answer turns out to be wrong, just delete it

If there's clarification needed, ask for it

At the very least an answer gets its own comment section rather than cluttering up the question box

@ErikDarling On a separate note, I always think SqlWorldwide is your sockpuppet

Erik Darling

1:20 PM

i may need to start drinking earlier than usual today

Paul White

Oh, there's the problem: I have no drink

Erik Darling

no excuse for that

J.D.

I'd make a guess if comments were downvote-able like answers, there would be a percentage of less comment-answers.

Erik Darling

comments should cost reputation to leave

2

Sean Gallardy

Don't give them ideas

J.D.

1:24 PM

@ErikDarling mm even better hah

John K. N.

Could we rename comments to something like sub-answers?

Paul White

@J.D. Yeah, that's been suggested many times on main meta. If you go that route, comments become ever more like answers. Just leave an answer and be done with it.

J.D.

@JohnK.N. and what if a sub-answer needs clarification?...we should add a comments feature to it. 🙂

Paul White

Threaded comments are also a popular request.

John K. N.

@J.D. Good idea...

Paul White

1:25 PM

(not with me)

John K. N.

@PaulWhite I remember seeing them in the wild somewhere. Can't remember where though. Some old Q&A site....

Paul White

@JohnK.N. Codidact has them, I think

J.D.

@PaulWhite That's surprising to me. I'd figure the opposite for 2 reasons. 1. Comments feel like less of a commitment than an answer because they are more simplified and hidden from general view, and can't be downvoted. So it's ok (from the commenter's perspective) to be incomplete, partially inaccurate, or straight up incorrect - without risk of repercussions.

Erik Darling

that would require a reworking of the comments table to have a parentcommentid and then aaron would have to write 17 posts about recursive ctes and adding columns with zero downtime

Paul White

There's also a certain arrogance attached to answering in a question comment. Like your thoughts are so important they need to be un-downvotable and positioned above even an accepted answer.

John K. N.

1:27 PM

ROFL

J.D.

2. People tend to clean up their own mess when they see it's gathered a general negative response, which downvotes represent. Also the fact it hurts some people's internet points too.

Paul White

@ErikDarling You're talking about a company with questions and answers in the same table

Sean Gallardy

@PaulWhite That's one of many ways of looking at it, if you're specifically looking for it

Paul White

Well, everyone has bias, it's true

Erik Darling

not moderators

Paul White

1:32 PM

They sort of fudged the issue by only having the top x comments visible. I've said many times over the years, having an off-screen or side area for comment-chat on the question would be acceptable. But we don't have that. Or self-expiring comments.

Erik Darling

impartial to the core

Paul White

It's our motto for sure

Erik Darling

> DECLARE @SQLDate varchar(8000)

Sean Gallardy

@ErikDarling Is there any other way?

Paul White

@ErikDarling It's wrong to punch people isn't it

Erik Darling

1:38 PM

not that i'm aware of

J.D.

Could've been worse, at least not VARCHAR(MAX)...

Paul White

Sean's never had a date that long

DECLARE @SeanDate tinyint;

Sean Gallardy

womp womp :(

J.D.

@ErikDarling Sounds like the guy who was writing his own ORM and wanted to use VARCHAR for everything.

Erik Darling

i wonder how he's doing

Paul White

1:42 PM

Still being strung along

J.D.

I couldn't CHAR less?

Paul White

Oh, here we go with the var-car thing again

Erik Darling

flagged as abuse

Charlieface

@PaulWhite I guess that requires commitment to the answer being right, because downvotes. If I have a guess at an answer, I'm going to tell OP "by the way maybe try this". Also requires commitment to writing up a full answer, rather than just a one-line half-baked sentence. Fact remains, there's no way currently to send the OP on the right track unless you do either a comment or an answer, and personally I find half-baked answers far more irritating than half-baked comments.

TBH I think the culture on Stack Overflow is a bit different also, where I'm more used to comments.

Paul White

I don't find that persuasive. If comments could be downvoted, you'd have exactly the same concerns. If an answer gets heavily downvoted, sure, you might choose to delete it to rescue your score from those beastly -2s, but in my experience decent guesses don't tend to attract DVs much because they might help someone else in future (and answer DVs cost 1 rep).

If you don't want to write half-baked answers, great! Just don't do it in comments either.

And yes, we don't look to SO as the gold standard to emulate.

We don't apply the rules without consideration either. If someone has voted to close a question and left a helpful 'pointer' sort of comment too, well, fine.

Erik Darling

1:58 PM

what level of inactivity does a stack site have to hit before it gets closed down

Paul White

Oh really astonishingly low

It's only ever happened a few times

Erik Darling

interesting

Paul White

Like no non-spam activity of any kind, and no moderator team for six months or so

Erik Darling

so i can't vote to close dba.se

Paul White

One problem with the slack approach is that once a question disappears off the front page, people forget about it. They don't follow up on old comments, and the site just gets cluttered up with old, unanswered questions with a few comments that went nowhere.

Erik Darling

2:01 PM

which comes back to the comment expiration date feature

Paul White

Would've been worth a shot, but the idea didn't get a lot of general support

People were worried about broken comment chains or something as I recall

But mainly the wider network is quite resistant to change or experimentation

And the company has been too, unless it clearly benefits SO (the site) or Teams revenue or whatever

Fair enough, to some extent, seeing as they are a for-profit enterprise

Erik Darling

think about all the money they could save on disk space by deleting comments!

3

Paul White

For anyone wanting to follow the meta rabbit hole: meta.stackexchange.com/questions/linked/204402?sort=active

@J.D. How is any of that a good thing for the site?

Erik Darling

did you get a drink yet?

Paul White

No, hence a bit grumpy

Erik Darling

2:14 PM

sort yourself

Paul White

I also need to clean the kitchen

But someone is wrong on the internet

Erik Darling

always

wait are you talking about me

Paul White

No, you're always correct, and a prompt payer

Erik Darling

tiger woods y'all

Paul White

Ok, I'm going to clean the kitchen

Erik Darling

2:18 PM

expecto sandwichum

Erik Darling

2:33 PM

this guy really had me going

-1

Q: How to ORDER BY after two tables have been joind using INNER JOIN ? ? Please answer without aliasses

Hello Database Admins. I have the database and within it one table is called dbo.tbl_Cities and it looks like this: CREATE TABLE dbo.tbl_Cities (City_ID INT IDENTITY(1, 1) NOT NULL, City_Name VARCHAR(70) NOT NULL, City_State_Region VARCHAR(100), ID_Country TINYINT NOT NULL CON...

sql-server join select order-by

Charlieface

Does anyone know how to get sp_executesql to return non-zero without it throwing an exception?

Docs say

> Return Code Values
>
> 0 (success) or non-zero (failure)

Erik Darling

does using an output parameter count?

Charlieface

No an actual return value. As in EXEC @i = sp_executesql ...

You can't use RETURN 42; you get A RETURN statement with a return value cannot be used in this context.

Erik Darling

oh, no

never really tried though

Charlieface

2:55 PM

@ErikDarling Apparently any non-batch aborting error will return the error number dbfiddle.uk/DAHHju82

Bit difficult to see in db<>fiddle because it only shows the error message if there is one.

Erik Darling

> CREATE TABLE Regyptian (Strut int NOT NULL CHECK (Strut > 0))

wat

Charlieface

Don't ask, I was copying straight out of Erland's article

sommarskog.se/error_handling/Part2.html#statementtermination

He's got some whacky names in there and I thought you were the funny one.

This one is batch aborting and has no result dbfiddle.uk/u3Iy0Y_i

Erik Darling

i thought i was funny until i met you

J.D.

3:58 PM

@PaulWhite That's my point. That's the current state of comments. If they were downvote-able, then they'd be more of a commitment, similar to answers, to follow the purpose of them.

Paul White

Well ok, but comments are different from answers for good reasons

J.D.

For sure, I'm not debating how comments should be used. Just the mere fact that a way for the community to police them the same way as answers, would seem beneficial to me.

Paul White

Here's some background information: blog.stackoverflow.com/2009/04/… ... blog.stackoverflow.com/2009/04/comments-top-n-shown - a lot has changed since '09, but that should give you a rough idea of the reasoning behind this system. Read those, think about them a bit, look over a bunch of different posts with comments on The Site Of Your Choice, and then work through a few different scenarios in your head... Then come back with an idea for how down-votes might actually affect the behavior of the system and examples of specific problems solved. — Shog9 Mar 13, 2013 at 16:29

Plenty of rabbit hole available for the interested observer, or those who feel they have a new and important point to make on meta

Paul White

4:22 PM

On the specific point, regular users can already police comments through flagging. Just three users flagging an ordinary disposable comment is enough for it to be deleted without a moderator becoming involved, for example.

Zikato

4

For @SeanGallardy who doesn't use Twitter

Sean Gallardy

Nice, we can expect weekly memes again?

Zikato

no

@Forrest keeps me too busy

Paul White

twitter.com/Zikato25/status/1635677678152871936 for people who do

Erik Darling

if you think about it all of chat is a comment section

Paul White

4:27 PM

without downvotes

J.D.

4:48 PM

@PaulWhite Fair, I never noticed the Flag icon before. But also no tangible repercussion to the user, since all it does is just undo what they did.

Sean Gallardy

Don't forget the changing buttons in chat

Paul White

@J.D. Having a comment deleted is not a tangible result? I mean, having your question or answer deleted also "just undoes what you did" but...

Zikato

5:09 PM

have you ever saw someone use centiseconds? Like instead of 10ms to say 1 cs

Paul White

Yep, just now

About 6000 cs ago

Otherwise, no

Zikato

hmm, too bad

Paul White

In the US, they probably prefer fahrenseconds

3

Zikato

how many eagles per football field is that?

Paul White

Four, rounded up

J.D.

5:27 PM

@PaulWhite Sure there's a result, but nothing that would make most people think twice about posting crappy comments again in the future. Same thing for answers, get rid of downvoting and only allow flagging and removal without explanation. I would bet you'd see the same people willing to continue to post crappy answers, even more so than in the current system.

Paul White

Maybe. Luckily, we're still small enough that we can recognise crappy posters and commenters by name or avatar most of the time

And eventually, someone would raise a custom mod flag, or we would see one of the comment flags, see the pattern, and take an action

J.D.

No doubt.

Paul White

People with low quality contributions don't tend to last more than a few hundred fahrenseconds in general

Couple of thousand, tops

J.D.

That's a lot of Eagles..

Zikato

5:42 PM

No dogfood for Victor tonight

Zikato

5:54 PM

@SeanGallardy I was rereading your post and found a typo
https://www.seangallardy.com/cluster-resource-dll-connectivity-in-availability-groups/

"Yes, but the best thing is to not shoot yourself in the foot for not reason. Leave the default ports."

For **not** reason instead of **no** reason

Erik Darling

6:06 PM

@PaulWhite don't talk about me like i'm not here

Paul White

@Zikato He'll be devastated. Nothing Sean hates more than spelling errors

mustaccio

6:19 PM

@Zikato I think Oracle trace reports timing in centiseconds

Zikato

@mustaccio You've convinced me

I'll never mention centiseconds ever again

Paul White

Wordle 634 5/6*

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

Really nailing those 5s at the moment

mustaccio

Spoken like a true oracle

J.D.

Anyone have any ideas why the wizziwig editor is bugging out on the T-SQL code I put in on this one? - dba.stackexchange.com/a/324761/150011

Erik Darling

⬛️⬛️🟩⬛️⬛️
⬛️⬛️🟩⬛️⬛️
⬛️⬛️🟩⬛️⬛️
🟩🟩🟩🟩🟩
🟩🟩🟩🟩🟩
🟩🟩🟩🟩🟩

J.D.

6:29 PM

@ErikDarling 👏

Sean Gallardy

@Zikato Good catch! Thank you :)

J.D.

Seems to be due to the ordered list that comes before it. Had to put a silly HR between the list and the code block.

ypercubeᵀᴹ

6:48 PM

Human Resource?

Josh Darnell

HR = horizontal rule = <hr />

I think that's a long-standing bug, but I'm too lazy to look for it 😁

I think it works if you use code fences rather than indentation to format the code.

Erik Darling

does anyone have a good template for using a cursor with error handling to keep going when errors are hit?

like lets say i have a list of views to create, and some views have dependencies on other views

i want to try to create all of them, but if i hit a dependency error just keep going to the next one and then retry others later after the other views are successfully created

J.D.

7:41 PM

@JoshDarnell Thanks, I was wondering if it was a bug. Are code fences the backticks symbol before and after each line?

Paul White

60 centiseconds from disaster

Erik Darling

sir please use valid freedomheit units

v. unwelcoming to the best country in the world

Josh Darnell

8:11 PM

@J.D. Backticks on a separate line before and after each code block, yeah.

512

Q: Implement ```-style (fenced) Markdown code blocks

Currently, Stack Exchange’s Markdown parser only allows four-space indents to represent code blocks: // some code // another line of code GitHub Flavored Markdown and other Markdown implementations allow for an alternative syntax, that doesn’t require indenting each code line: ``` // ...

feature-request status-completed markdown markdown-rendering

And since I was on Meta already...

168

Q: Code block is not properly formatted when placed immediately after a list item

Consider the following piece of Markdown code: This is some regular text. >>> def factorial(n): ... return 1 if n < 2 else n * factorial(n - 1) ... * This is a list item. >>> def factorial(n): ... return 1 if n < 2 else n * factorial(n - 1) ... Notice that ...

bug status-bydesign markdown code list

Zikato

8:31 PM

@ErikDarling the views are created with dynamic sql?

Erik Darling

@Zikato yes

Zikato

9:08 PM

@ErikDarling I don't know how to run only one batch in fiddle, but try this

https://dbfiddle.uk/qE64JcwE

Erik Darling

that doesn't create the view with the dependency after it creates the view it needs though

i may have to do it in two steps

Zikato

it does if you keep running it in loop

ah, I see what you mean

Zikato

9:25 PM

@ErikDarling dbfiddle.uk/8SteNESp

It had to be a temp table though, dynamic doesn't work with table variables it seems

Erik Darling

oh that's a great idea

thanks

Zikato

I'll take that win

Erik Darling

9:59 PM

more docker news: twitter.com/pimterry/status/1635757748229922817

mustaccio

Shocking: A for-profit company to stop offering terabytes of disk space for free

Next in the news: the earth is not as flat as previously thought

J.D.

@JoshDarnell That worked, cheers! 🤙

Josh Darnell

10:20 PM

No problem!

Erik Darling

10:32 PM

@mustaccio i keep hearing that disk is cheap

J.D.

In other news, anyone looking for a new job? My company's hiring for fully remote positions now. lol

Hannah Vernon

10:52 PM

@ErikDarling that's got to be more about making people pay, no?

Sean Gallardy

Who's ready for some CUs?

Oh, another open source thing that is doing something people don't like and will break all their stuff with 30 days notice. I was told be reputable sources open source was the best in everything for everything of everything 🤷‍♂️

Erik Darling

@J.D. if they are data related let me know and i can ask around

@SeanGallardy for what?

J.D.

11:28 PM

@ErikDarling oh word, I appreciate that! We're mostly looking for full stack or more application layer geared, but if you know anyone that's more proficient on the database side and has some level of application layer experience, that could work.

mustaccio

> keep hearing that disk is cheap

probably from the same people who want others to pay for it

they may not realise you need some expensive boxes where to put those cheap disks, in some expensive buildings, with expensive security, fire protection, and air conditioning

Erik Darling

11:46 PM

@HannahVernon the best way to get people to pay is to send them invoices

@mustaccio you mean the cloud?!

Transcript for

The Heap™ – Consultancy ©®