The Heap™ – Consultancy ©®

Q: There's an election going on. What's happening and how does it work?

> In "full" elections, if the number of nominees is less than or equal to the number of moderator slots, the nomination period will be extended by seven days, and if there are still not enough nominees to make the election competitive, the election will be cancelled entirely.

142

The community bulletin says that there is a moderator election occurring. How does the election work and how can I get involved? Return to FAQ index

> A nominee must be "in good standing". Any nominee who was suspended at any time during the past year on any site in the network will not be allowed to take part in the election (unless the suspension was in error or was reversed on appeal).

(from the same link)

John aka hot2use

11:06 AM

568

Q: We're switching to CommonMark

I’m Ham and I’m a developer on the Teams team here at Stack Overflow. Over the past few months, I’ve been heads down working on the way we turn Markdown into HTML when writing and editing posts across the network. I’d love to share what I’ve come up with. In a nutshell: We're planning to use Comm...

discussion status-completed markdown announcements

John aka hot2use

11:26 AM

A Theory of Moderation

Jeff Atwood on May 18, 2009

We believe deeply in community moderation. That’s why we appoint Pro Tempore Moderators and, ideally, democratically elected community moderators for every site in our network. But what do community moderators do? The short answer is, as little as possible! From the very first version of Stack Overflow faq way back in mid-2008, our goal has…

Master Database

1:23 PM

1

Q: Spam flag declined on strange answer

I reviewed late answers and got shown an answer that only had gibberish in it, no coherent answer whatsoever (first I thought ok a test maybe) and flagged it as spam. Now I got the flag declined, but now the answer has a correct answer, dated back 2018, with no edits. https://dba.stackexchange.c...

support flagging

1:50 PM

Good morning.

@bbaird If you mean this one, the question was bumped to the front page when Joe Obbish posted an answer.

@bbaird Morning!

@JoshDarnell That's the one. I mean, it was a bit rambly/off topic so I get it, but the point I was trying to make (just because you have a clustered index doesn't mean you've stored the data meaningfully) really is overlooked by a lot of people

@bbaird I thought that point you were making was interesting. It would probably make a good blog post.

I didn't vote either way on your answer, by the way.

@Paul probably downvoted purely based on you starting lines of T-SQL with commas.

@JoshDarnell I might have to do that, but I have a toddler so that really cuts down on the amount of quiet, contemplative time it would take to create a thoughtful post.

@JoshDarnell HE DID WHAT

2:05 PM

@PaulWhite I've got a lot more answers for you to downvote

Like, A LOT

@bbaird Seems like it would quicker to delete your account 😀😀😀

@bbaird I get it, mine is almost 3 😀

Anyway, I think I get the philosophical argument that an identity clustered PK column "is a heap." But it's really a B-tree. You can use it as an FK, it supports seeks, etc.

@PaulWhite don't tempt me!

@bbaird it was an offer

@bbaird I think he's threatening you.

With mod abuse.

2:11 PM

I'm not very good at threats

Typical of Paul.

I am told practice makes perfect

SELECT TOP 1 Name FROM Bullies ORDER BY Meanness DESC, Name returns Paul White

He's awesome in every way.

😮

2:12 PM

LOL

hahaha

I think Evan got the grump with me for editing his HALLO messages to add a 🤡

Poor Evan.

Well the network has been repressing him for years you know

Starting with the founder.

2:19 PM

@JoshDarnell All good things for me to address in my blog post

The surrogate key topic is long running and vexed.
I would just say having a narrow, unique row-identifier can be very handy.
The advantage of making it clustered is you can get the whole record with a singleton seek (no lookup for extra columns).

It's still two seeks - one on another index (or table), then the row. There are times when it is handy, but in general it's overused and should be a second (or third choice) when designing the database, not the first.

@bbaird By the way, if you don't have a blog already, you could write and post your blog post on Top Answers: topanswers.xyz/databases?room=2&search=%7BBlog%7D

@JoshDarnell Might have to take you up on that.

2:34 PM

@bbaird Huh? Typically you have the ID already, then do a single clustered seek.

In any case, the single clustered seek is often 'better' than nonclustered index + key or rid lookup.

Not always.

The other main benefit of clustered tables is automatic space management.

I've seen too many heaps over the years with hundreds of thousands of empty pages.

I am well-disposed towards heaps in general, but the use cases are quite separate in my mind.

More generally, I am more persuaded that columnstore-organized tables are closer to heaps than clustered tables.

Feel free to disagree but I'll replace everything you write with clown emoji.

3:06 PM

@PaulWhite So this isn't about using a heap, it's more the actual utility of having a row pointer as a clustered index. I'll take an example from my industry - if I have a claim, there is an alphanumeric ClaimNbr that identifies it. We also have claimants for each claim with a PartyId, as well as coverages, etc. So the hierarchy is Claim (ClaimNbr) -> Claimant (ClaimNbr, PartyId) -> ClaimFeature (ClaimNbr, PartyId, CoverageCd)

@bbaird Yes I understand your point of view on that. As I say, the whole natural vs surrogate thing has a long history with passionate arguments on all sides.

@PaulWhite SOMEONE IS WRONG ON THE INTERNET xkcd.com/386

Q: Should every table have a single-field surrogate/artificial primary key?

35

I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are 'artificial'. However, it sometimes seems to be a matter of policy to have an auto-incrementing inte...

database-design primary-key surrogate-key

27

Q: Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

I'm a fan of surrogate keys. There is a risk my findings are confirmation biased. Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys based on IDENTITY() values. My background in computer systems tells me performing any comparative op...

sql-server sql-server-2012 performance surrogate-key natural-key performance-testing

(two examples of many)

@PaulWhite I've read them all. But missing (or maybe only mentioned in passing) is how the choice of key impacts the physical storage of rows and overall storage via indexes. It's really, really easy to look at this from a "can you join table A to table B and C and so on" but not so simple to look at this from a "how many additional indexes are required/what is the I/O impact of having logically related rows physically separated"

There really isn't a single right answer that applies to all circumstances.

One can always focus on a particular type of problem and 'prove' one is better than the other.

3:18 PM

Sure, but one should be well aware and cognizant of the implications of the decisions they make. It's never "never use surrogates/row ids" or "never use composite keys" or "never use business keys", it's when to use each and why.

Agreed. I still wouldn't call a clustered table a heap though.

Well unless I wanted to upset it perhaps.

Tables are very sensitive. I agree that within SQL Server there are specific features of a clustered index that make it nowhere near a heap (as SQL Server implements it). But if we were to compare the auto-incrementing approach to say, Postgres (or DB2/Oracle if the table isn't index organized). The data in that instance is stored in "the heap" and the b-trees contain references to pages.

Yes but they're not real databases.

@PaulWhite now you're itching for a fight with the Postgres gang

Pretty much.

Postgres is just another name for a spreadsheet, I think.

My next meta post will be to propose a "Closed As Not A Real Database" option.

4

3:36 PM

I submit this as a peace offering to the Postgres folks on the Heap(tm)

Peace in our time
*robot explosions*

chat markdown ~~strikes~~ again

4:01 PM

Missed opportunity for strikethrough text there.

Who can remember its syntax

4:31 PM

Does anyone else not respond immediately to meeting requests as not to appear "too needy" or is it just me?

please, what is a meeting requests

Accept/Tentative/Decline. Maybe we should call them meeting demands.

Google sometimes reminds me about an event I forgot to delete on receipt

That's about all I have to add

Except the whole thing worked better when people sent Formal Invitations with RSVP, and one's butler presented each one individually on a silver tray for consideration.

billinkc

4:57 PM

You used silver trays? Cheapsy little hobbits. Nothing but gold trays here in fryerville

5:08 PM

Gauche

Typical nouveau riche riff-raff

Tom V

Only way I ever seen to have friends is if I am the one sending the invites

Try wearing a less scary mask

cool cool COVID infecting senior military leadership just because someone couldn't be bothered to wear a mask.