The Heap™ – Consultancy ©®

Erik Darling

12:27 AM

😒

Hannah Vernon

1:04 AM

@PaulWhite lol

J.D.

1:22 AM

So I know we're not moderating these days, but I thought Dan's edit on this one was funny: dba.stackexchange.com/q/328088/150011

Michael Green

3:02 AM

@JoshDarnell I find these interesting. I've bookmarked the link. Recently I heard about bitslicing and bitweaving (pages.cs.wisc.edu/~jignesh/publ/BitWeaving.pdf) for the first time. I look forward to working my way through those, too.

John K. N.

5:58 AM

morning

Alberta Fires Political It's Fine Meme

Zikato

6:38 AM

Morning. Tricky Wordle today

John K. N.

6:59 AM

Very much so, yes.

Wordle 720 5/6*

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

ypercubeᵀᴹ

8:45 AM

@SeanGallardy surely they'll stop at 12 (or 24 or 60). 11 or 13 seasons would be blasphemous geometry

Yano_of_Queenscastle

8:59 AM

Hello, esteemed experts.

I have SQL Server question, that I don't think deserves being posted as question. (But if you disagree, I will do so.)

Is my understanding correct, that that
1. clustered index implicitly INCLUDEs every other column of that table
2. non-clustered index implicitly INCLUDEs columns of clustered index (if it exists)
?

ypercubeᵀᴹ

yeah, that sounds about right.

Paul White

Wordle 720 4/6*

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

@Yano_of_Queenscastle Broadly, yes. For #2, the b-tree clustered index keys (including any uniqueifier) are present as extra keys (not includes) in the NC index if the b-tree nonclustered index is not declared unique.

Gosh it's hard to summarize in one sentence. The clustered index keys are always present as keys at the leaf level of the NC index. They're also keys at the non-leaf levels if the NC index isn't declared unique.

7

A: Why do root and intermediate levels of NON-unique NON-clustered indexes additionally store row ID?

The SQL Server storage engine requires every entry in an index be unique at all levels at all times. For a unique nonclustered index, only the nonclustered keys are required above the leaf because they're unique by definition. The full bookmark (aka row locator, RID) to the associated heap or clu...

@Yano_of_Queenscastle There is also a subtlety to point #1. Depending how you define "includes" the clustered index only 'includes' columns stored in row. Any data stored off-row (row overflow or large object data) isn't included by some definitions.

Yano_of_Queenscastle

9:26 AM

@PaulWhite Interesting. I feel like I have more questions now than before :D Gotta read something about this.

Paul White

@Yano_of_Queenscastle There are quite a few Q & A on the main site already. Have a look at the one I linked above then look around more as necessary. Your understanding is broadly right, there are just always more details if one is interested in them.

Yano_of_Queenscastle

@PaulWhite I will.

Thank you both for your help.

Sean Gallardy

10:31 AM

@ypercubeᵀᴹ 13 is a sacred number so it's all good

Hannah Vernon

11:04 AM

Wordle 720 4/6*

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

What the h-e-double-hockey-sticks is that

I thought I had a good vocabulary, but I've never heard that before

Ok I'm an idiot, of course I've heard of it, you just have to say it the right way lol

Paul White

Wow

Well you got there in the end

Hannah Vernon

12:02 PM

Yah, felt kind silly when I realized what it was. Should have had it in three but I threw away the third guess by reusing a letter I knew wasn't the first letter.

I see the CMs have been doing a little cleanup

Quite surprised they've not removed the featured tag from the meta post

Seen on Discord:

> being confident matters more than being right

4

Paul White

Yep

J.D.

12:19 PM

@HannahVernon well we all know I didn't get my reputation by being right. 😉

Hannah Vernon

12:31 PM

FGITW?

J.D.

12:46 PM

Hmm?

Paul White

Fastest Gun In The West

J.D.

oOo. Yes, that helps lol

Paul White

254

A: The Many Memes of Meta

Meme: Fastest Gun in the West (FGITW) Originator: Omer van Kloeten First Heard: September 11th, 2008 Definition: A problem identified as a side-effect of sorting votes by descending score where first-posted answers are quickly up-voted, trumping vote opportunities for people who sit down and ...

J.D.

Too many acronyms in this world.

Paul White

TMAITW

2

Hannah Vernon

1:03 PM

When I started working for the govt insurer here back in 2014, they gave me a list of the acronyms they use. It was well over 2,000 items and they stored it in a database with specific tooling just for looking up acronyms and their definitions.

J.D.

@HannahVernon Lol sounds about right.

Gotta appreciate the organization at least.

Hannah Vernon

I like ponies

J.D.

I like turtles

Josh Darnell

@MichaelGreen Oh wow, that's cool too. Thanks for sharing.

Erik Darling

1:21 PM

4 hours ago, by Yano_of_Queenscastle

Thank you both for your help.

Paul is now so helpful people think he’s two people

Josh Darnell

> ypercube's feelings

Paul White

4 hours ago, by ypercubeᵀᴹ

yeah, that sounds about right.

I just repeated ypercubeᵀᴹ more verbosely

@JoshDarnell That's trademarked you know

Josh Darnell

@HannahVernon I briefly joined that Discord. It was a little overwhelming haha.

I departed the server after like an hour and a thousand messages.

Hannah Vernon

Just a little lol

Josh Darnell

@PaulWhite Oops!

Paul White

1:25 PM

About to get SUED

I always thought "discord" was a strange choice of name

discord

Noun: discord (countable and uncountable, plural discords)

Lack of concord, agreement or harmony.
A false witness that speaketh lies, and he that soweth discord among brethren.
1775, Edmund Burke, Conciliation with America
Peace to arise out of universal discord fomented in all parts of the empire.
Tension or strife resulting from a lack of agreement; dissension.

(2 more not shown…)

Sean Gallardy

Should name the "AI" chatbots that

Zikato

> The name Discord was chosen because it "sounds cool and has to do with talking", was easy to say, spell, remember, and was available for trademark and website. In addition, "Discord in the gaming community" was the problem they wished to solve

Hannah Vernon

@PaulWhite No, you got it right. More widely, this is exactly right:

> A false witness that speaketh lies, and he that soweth discord among brethren.

Paul White

@Zikato I see

Zikato

Finally ended our discord when choosing between Mumble, Ventrilo or TeamSpeak

Sean Gallardy

1:27 PM

I used TeamSpeak for years, wrote many auto programs to integrate with its API

First one I ever used was Roger Wilco

Zikato

It was a tough choice in times of high latency and low bandwith

Sean Gallardy

Also someone posted an "AI" answer to a question they had and asked if it was correct. The question was how to lower SQL Server memory usage. The "AI" response was to exit the application.

Paul White

Anyway, it's not a name that has aged well

Sean Gallardy

Not wrong, that'll definitely lower the memory usage of the application...

Josh Darnell

@SeanGallardy Amazing.

Sean Gallardy

1:34 PM

@JoshDarnell Especially when you figure in these are MS support people

Josh Darnell

I remember using Ventrilo back in the days of heavily playing Starcraft 2.

Sean Gallardy

Imagine getting a response of "To use less memory, exit the application"

@JoshDarnell and Red Alert 2

Josh Darnell

@SeanGallardy Oof.

Paul White

Perhaps AI is acquiring a sense of humour

Sean Gallardy

Must be trained on your humor then

Paul White

1:37 PM

No u

Sean Gallardy

💘

Josh Darnell

@HannahVernon Ah, is that who cleared all the spam from the front page? I wasn't sure if regular community folks had flagged things to death or not.

Paul White

I looked once and "Charlotte" had handled some flags.

I think some or all of the spam was sufficiently flagged to death by ordinary users.

Oh, Catija too

J.D.

1:53 PM

@SeanGallardy lol love it.

Erik Darling

I have been more useless than usual

It’s been easy since I’m been on mobile only all week

Maybe the strike will end Monday

Paul White

2:24 PM

Maybe

Erik Darling

2:43 PM

I forget what the terms of the strike are

But I am enjoying my vacation

Maybe I’ll blog answers to questions instead of answering them 😤

Paul White

Nothing wrong with asking or answering questions. We're just not moderating.

Up to you what you do or don't do ofc

Erik Darling

Oh I thought it was a general activity strike

69

A: June 2023 Data Dump is missing

DISCLAIMER: I was recently impacted by the Company's layoff. I am going to carefully respond in a way that ensures I don't reveal anything the Company may feel is confidential--particularly with regard to strategy, or future plans. Any knowledge I have on strategy or future plans is both dated a...

Paul White

> Until this matter is resolved satisfactorily, we will be pausing activities including, but not limited to:

* Raising and handling flags.
* Running SmokeDetector, the anti-spam bot.
* Closing or voting to close posts.
* Deleting or voting to delete posts.
* Reviewing tasks in the various review queues.
* Running various other bots designed to assist in moderation, such as detecting plagiarism, low-quality answers, and rude comments.

openletter.mousetail.nl

> Until Stack Overflow, Inc. retracts this policy change to a degree that addresses the concerns of the moderators, and allows moderators to effectively enforce established policies against AI-generated answers, we are calling for a general moderation strike, as a last-resort effort to protect the Stack Exchange platform and users from a total loss in value.

J.D.

Anyone know if SQL Server has a native function similar to IS_ROLEMEMBER() but for checking if a securable is mapped to a database role for a given permission?

Josh Darnell

2:59 PM

Ask ChatGPT.

😜

J.D.

@JoshDarnell It told me to exit the app.

Paul White

@J.D. I can't quite parse your question. Can you give a concrete example of the kind of test you want to perform?

Lamak

Wordle 720 5/6

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

yeah, I know I fucked up

J.D.

@PaulWhite I want to pass in the name of a Database Role and the name of an object, e.g. Table, and then get back a list of Permissions assigned to that Database Role for that Table.

Paul White

@Lamak You might like to try "hard" mode

J.D.

3:03 PM

Or furthermore, pass in the name of a permission as well, e.g. SELECT, and get back a value indicating that permission is provisioned or not.

Lamak

@PaulWhite it's already too hard for me

J.D.

It's easy enough to query for in the sys.database_permissions table. But I need to incorporate this check into some dynamic SQL, so hoping to simplify the code by calling a function that does the check.

Paul White

@Lamak I personally find "hard" mode easier because it prevents silly mistakes

Lamak

@PaulWhite ah, I thought you were messing with me...is there actually a hard mode?

Paul White

@Lamak Yes, it's one of the options you can select

Lamak

3:08 PM

what is different in hard mode?

Paul White

> Any revealed hints must be used in subsequent guesses

Lamak

@PaulWhite I see...that makes it seem like easier mode though

Paul White

I agree

But that's how it works

@J.D. You could probably use HAS_PERMS_BY_NAME or sys.fn_my_permissions but you'd need to impersonate a user who is a member of only the target role with no other explicit permissions.

docs.microsoft.com/en-us/sql/t-sql/functions/…

J.D.

@PaulWhite hmm I'll check them out. For now I created a scalar function to refactor it for me. It's a small use case, so I'm not too concerned about it being scalar.

Thanks!

Paul White

Why bother to ask if you don't care about the answer or have already solved it another way?

J.D.

3:16 PM

I do care, as I noted I'll check out what you offered. I'm time limited right now unfortunately, so was coding a solution in tandem that'll do for now.

Always room for change. Each day better than the last, eh? :)

Paul White

16 hours ago, by Erik Darling

@J.D. thanks for bringing it up 😃

J.D.

Anytime! 🙃

But seriously, I do appreciate you answering me. Will actually look into that.

Paul White

It's probably better to do it the way you're doing it. But, I do wonder why you're checking at runtime what permissions a ROLE you presumably have full control over has.

Normally, a role would encapsulate all the permissions a group of users need to perform some tasks. If you're implementing one of those tasks, it is sufficient to check if they are a member of that role, or even just assume things will be ok.

J.D.

We replicate a few tables from a vendor system. Sometimes those tables get dropped from and re-replicated to the Subscriber as a result of upgrading that vendor app. On the subscriber side we have a user-defined database role that those tables are a part of. And essentially Domain Users are mapped to that database role, so that our custom apps can work.

Paul White

Ah, replication

J.D.

3:24 PM

Not a big deal for me to manually re-add the dropped and re-synchronized tables to the database role, but I was sleeping when the call came in this morning so I rather have a SQL job check and automate it for me.

Shame on me, I do this already for one of our other replicated vendor systems. Should've setup this up already for this vendor's replicated database.

Paul White

> By default, replication does not execute GRANT statements on the subscription database. If you want the permissions on the subscription database to match those on the publication database, use one of the following methods:

* Execute GRANT statements at the subscription database directly.

* Use a post-snapshot script to execute the statements. For more information, see Execute Scripts Before and After the Snapshot Is Applied.

* Use the stored procedure sp_addscriptexec to execute the statements.

J.D.

Yup yup.

Sean Gallardy

@HannahVernon any 10 Gb Nics to recommend for home use?

Paul White

This all horribly reminds me of when I used to have to copy a complicated replication setup to dev and test environments.

Erik Darling

3:50 PM

🧊💊

Paul White

It was a long time ago, I'm over it now

Mostly

eye twitch

Erik Darling

Someone recently asked me to help on a Replication set up project that has a sensible use case. I’m waiting to hear back some more details.

J.D.

@ErikDarling Interesting. Are you able to generically share the kind of use case it is?

@PaulWhite Word. Yea it's not exactly replications fault per se in this case. We generally truncate the tables, but because of certain schema changes the vendor made, it was just simpler to drop the tables on the Subscriber side and let a re-initialization re-add them.

Erik Darling

Analytics people run shitty queries against transactional data and they want to get them off there, so the plan is to replicate tables over and add column store indexes to the subscriber side.

Paul White

Filtered nonclustered columnstore on the main tables not an option?

docs.microsoft.com/en-us/sql/relational-databases/indexes/…

Erik Darling

4:05 PM

It’s an option of course but the queries are resource intensive and step all over the normal workload

And of course they’re all ad hoc whim and fancy so there’s no hope of properly doing anything

Well, the normal workload and other reporting queries, so let them figure that out somewhere else

Something like 80 hours of resource semaphore waits in 120 hours of uptime

Average wait of two minutes

Paul White

Ah so they already have NCCS

Erik Darling

No

Paul White

Oh

Lamak

so, is SO really not going to provide the data dump anymore?

Paul White

seems like it

Lamak

4:12 PM

jeezus, that's far worse than the stupid not-moderate-AI-answers thing

Paul White

22

A: Is SE [going to be] selling our content for AI model training? And what exactly does "reinvest back into our communities" mean?

I'm here to respond to some of the points raised in this post. No doubt I'll miss some, so I appreciate your forgiveness and understanding that I'm working to answer as many questions as I can. Some things I'm not competent to opine on (legal interpretations for instance), and I've passed off to ...

you've reached my caring limit on this issue though

Erik Darling

Caring limits being what they are these days

Paul White

Quite

Lamak

mmmm not that I care more than that either...just reaaaaally don't like the decision

mustaccio

This is interesting:
> Chandrasekar [...] argues that [selling content to train LLMs] will also help future chatbots, which need "to be trained on something that's progressing knowledge forward. They need new knowledge to be created."

And at the same time they allow bot-generated content on the site, ultimately training bots on the content generated by bots.

I'm sure that will progress knowledge way faster than before

soon we'll be able to say: "knowledge being what it is these days"

Erik Darling

4:31 PM

Maybe they’ll pay us for content now!

Sean Gallardy

I can only imagine the circular downward spiral as these "AI" technologies don't actually create anything new. They don't actually think (as in problem solve in an abstract way) so I'm unsure how that's going to helpful to train bots on bot actions. In fact, taking a page from video games, it's quite terrible what ends up happening.

Erik Darling

It’ll be like those old videos of Siri and Alexa talking to each other for three days.

Sean Gallardy

Exactly.

J.D.

@ErikDarling Yea custom indexing on the replicated data is a big + over AGs for example. :)

Sean Gallardy

One might say.... the only plus

J.D.

4:42 PM

Heh well it falls under the bigger umbrella of being able to customize the schema of the replicated data (related to what we discussed the other day).

I go as far as creating indexed views in our replicated databases, but I'm a madman.

Custom Row-Level Security, custom database security, etc.

Erik Darling

5:01 PM

@J.D. Yep, threw out all of the usual options pretty quickly

J.D.

@ErikDarling You mean they weren't jumping out of their seats to implement SSIS? 🙃

Erik Darling

Thankfully they’d never heard of it

J.D.

Worddd

Yea I raked the coals on the options a few years ago when I started here at Haugland. Potential data synchronization "solutions" I recall could be 1. Replication (inclusive of the various kinds), 2. AGs, 3. SSIS, 4. Triggers + Linked Server, 5. SQL Agent Job + Custom SQL. 6. Application code. Did I miss any? 👀

Erik Darling

5:29 PM

An ocean of third party tools

J.D.

True, fair enough.

Erik Darling

If we’re counting AGs and snapshot replication then log shipping is in the picture

Hannah Vernon

5:56 PM

@SeanGallardy I've only got 1GB lol

J.D.

6:37 PM

@ErikDarling ah yes, I looked into that too at the time.

Paul White

8:03 PM

@ErikDarling Based on Change Data Capture/Change Tracking?

Erik Darling

8:30 PM

@PaulWhite usually yes

Some claim to work off base tables with just a primary key available but who knows

Josh Darnell

8:41 PM

Hey, welcome to SO! You're new here, but someone should have told you: traditionally we used the Reddit dumpster fires as examples of what not to do. — Shog9 29 mins ago

Hannah Vernon

8:54 PM

Hahahah good old Shog9

Erik Darling

Jody is not any more popular internally

Though I suppose as people “leave” his popularity will rise

At least until more people need to leave who are diminishing his popularity

Josh Darnell

Big yikes.

I actually just got on Twitter for the first time in a week or so and I saw Kendra has parted ways with SO.

Erik Darling

9:20 PM

Yeah, directly related to a lot of the nonsense you’re seeing publicly and a whole lot more

Josh Darnell

Jeez. That really sucks.

Erik Darling

Might as well

176

Q: June 2023 Data Dump is missing

The data dump usually get refreshed the first weekend of the month, every 3 months. The current Data dump is still from March. Is there just a problem and it's delayed like in the past?

Oops

176

Q: June 2023 Data Dump is missing

The data dump usually get refreshed the first weekend of the month, every 3 months. The current Data dump is still from March. Is there just a problem and it's delayed like in the past?

support status-planned data-dump

Why isn’t my comment link working

Josh Darnell

“No downstream restrictions. You may not offer or impose any additional or different terms or conditions on, or apply any Effective Technological Measures to, the Licensed Material if doing so restricts exercise of the Licensed Rights by any recipient of the Licensed Material.” — Erik Darling 3 mins ago

Who knows.

Erik Darling

Mobile was a mistake etc.