The Heap™ – Consultancy ©®

J.D.

01:07

Wordle 653 3/6

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

mustaccio

Why is it that these "dba"s here are so obsessed with human words? You've got the SQL standard, stick to it

Paul White

01:46

@ErikDarling Why log changes to an indexed view when instead of redoing a small committed change during recovery or rolling back a small uncommitted change due to an error or other transaction failure, one could simply rebuild the entire indexed view 🤣

Erik Darling

02:02

I talk to people who feel that way about entire databases

Paul White

Well

> Besides the default durable memory-optimized tables, SQL Server also supports non-durable memory-optimized tables, which are not logged and their data is not persisted on disk.

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

Paul White

02:34

And you know, maintaining secondary indexes all the time seems logically redundant

J.D.

@mustaccio I've always been a Scrabble beast. So Wordle fits the genre.

@PaulWhite exactly.

Josh Darnell

03:20

@HannahVernon I was going to just suggest host bindings in IIS directly, if traffic is low enough that you can serve all the sites from one web server. But yeah, reverse proxy would work if you want the sites truly split to different machines.

@J.D. why would licensing costs be the same?

I don't really know anything about licensing.

Except that it's expensive

Hannah Vernon

@JoshDarnell I serve 95% of my sites from a single HyperV IIS server. Works well, except for sites that run only from Linux services

@JoshDarnell licensing for SQL Server is s minimum of 4 cores, typically

There are always local factors, of course. Such as licensing the entire hypervisor

Josh Darnell

03:49

@HannahVernon weird.

J.D.

04:27

@HannahVernon Yea this. Even if you're only using 2 cores, technically Micro$oft wants you to still pay for 4. @JoshDarnell

Zikato

06:10

Morning,

Wordle 654 3/6*

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

John K. N.

Memory Recommendations

Morning

Wordle 654 3/6

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

got lucky

Zikato

07:22

I see that Josh hasn't fixed the typo yet joshthecoder.com/2020/09/11/…

Someday, somehow, he'll make it alright. But not right now.

Paul White

shouldiblamecaching.com

Paul White

11:01

Non durable Hekaton tables are not logged. — Paul White ♦ 2 hours ago

Unlogged is different IMO, that's more about keeping an object completely out of the transactional logging system, and possibly truncating on restart. This is more about automatic rebuilding of NCIs and indexed views after a restore, which doesn't necessarily mean they need to be unlogged. — Charlieface 43 mins ago

@Charlieface I realise that, in the context of the question. I left the comment because the answer currently says, "However, as of this writing, you can't mark an object of any variety as unlogged in SQL Server.". Erik may or may not wish to rephrase or clarify.

On the other hand, the question says:

> My understanding is that changes to view indexes must be logged / backed up just like changes to any other table/index. However, it seems to me that such logging is redundant since SQL could easily rebuild the view index from the view definition in the event of a crash.

I read that as saying changes to views should not be logged and rebuilt instead when recovery runs (not necessarily as part of a restore).

Hannah Vernon

11:21

Wordle 654 4/6*

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

Erik Darling

11:53

@PaulWhite the distinction for me is whether the user has any say in if they’re logged or not, hopefully that makes sense

Paul White

@ErikDarling Yes, it does cheers

Erik Darling

mom get the camera

Paul White

I was pretty sure you couldn't create an indexed view on a non-durable table. I had to try it to check.

The error message isn't as helpful as might be. Something about in-memory tables not supporting CREATE INDEX.

Which is true, but I was trying to create an index on a view.

Erik Darling

i went through seeing what actually gets logged for indexed view changes because i had an irrational and paranoid vision in my head of answering the question and you saying that we're all muppets because they're not actually logged in some weird way

muppet amateur hour

Paul White

ha ha

Erik Darling

12:03

gist.github.com/erikdarlingdata/…

Paul White

cool

Erik Darling

groundbreaking

not at all recycled from another demo

p. good formatting though

Paul White

yes

I suppose the question is mostly nerd-sniping at this point

Erik Darling

Anyone wanna get in on this with me? US To Build $300 Million Database To Fuel Alzheimer's Research

Hannah Vernon

Someone's annoyed at IBM lol

Erik Darling

12:10

reasonably so

jeez, i went to edit the link to add in the title and it's already past editing

Zikato

@ErikDarling That barely covers the licensing costs

Paul White

@ErikDarling Like that?

Erik Darling

@Zikato wait until you meet postgres

Mar 29 at 12:44, by Paul White

but nailed it in the end

Paul White

It's amazing the things you start to doubt. I just had to check you can insert into an indexed view

Zikato

Have you tried trying it?

Paul White

12:14

Yes, I checked by trying it

Zikato

missed opportunity

Paul White

There's something cute about inserting to view that ends up inserting into the base table, then updating the view with the same insert

Erik Darling

how many spools were involved

Paul White

two, but one

The assert is there because I added WITH CHECK OPTION to the view

Because why not

Hm. I wonder if and when metadata versioning sees the light of day that will mean the end of sLog

Nerd sniping

Erik Darling

the light of data

what a podcast name

Paul White

12:27

crap

fixed

Erik Darling

it wasn't broken

also chuckling at creating a view with check

@HannahVernon rise and shine: dba.stackexchange.com/q/325579/32281

Paul White

@ErikDarling How else would you see a glorious message like:

> Msg 550, Level 16, State 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

spans a view

Erik Darling

how elegant

back when error messages were poetry

Paul White

That person should write KBs

@J.D. He's just using TOP to show at which point the error starts happening

"how can we successfully SELECT all ~91k records" - But in your example query, you're using a TOP to return only a subset of the rows anyway. Why would you bring all the data back across the Linked Server, only to immediately discard most of it anyway? (And even worse, your query doesn't use an ORDER BY clause, so it's a random TOP 100 rows that you're keeping.) — J.D. 10 mins ago

The data doesn't all come back from OPENQUERY anyway - it's a streaming interface as usual

Hannah Vernon

@ErikDarling thx

And undoubtedly I'm wrong lol.

Paul White

12:42

Linked servers to non-SQL Servers need more dark incantations to work than replication does

Erik Darling

streaming interface is a nice phrase for row by row

Paul White

Is there any question or answer Jay Dee or Mr Face won't comment on

Erik Darling

immediately

Paul White

@ErikDarling Everyone on the Light of Data uses it

On poetry day anyway

spanning views with the light of data

Erik Darling

i should start listening to improve my data poetry

Paul White

12:49

Come along on Vogon day

Erik Darling

that seems like a precarious first day

Paul White

Best to weed out the weak ones early

Erik Darling

jessy is a shoe-in

dropping the parallely right off the bat

Hannah Vernon

I feel like Jessy is in for a world of hurt based on that code

Erik Darling

i wonder if the top 10 query should have with ties based on the sort column

Sean Gallardy

13:04

Our internal customers are the worst. Sends me a screenshot of SSMS connected to their database, running queries, and says "The database is down".

Erik Darling

maybe they meant it's down with the sickness

Sean Gallardy

I can't with these people anymore

Zikato

13:20

I've had people send me a screenshot of an excel file on their desktop and expect me to open it.

> Feature request has been added to the Development Backlog to add Per Operator Run Times in Plan Explorer.

Erik Darling

you're welcome

Josh Darnell

They're adding stuff to Plan Explorer?!

Paul White

They're adding stuff to the backlog

Josh Darnell

Ah.

An important distinction.

Erik Darling

sounds redundant

you could just rebuild it from what's missing from the software

J.D.

13:33

@Zikato I've had people tell me their Excel spreadsheet is wrong without sending me a copy or letting me see which one they're talking about. Cool story, it was.

@PaulWhite Got it, comment deleted. Learned something new too. 🙃

Paul White

Another one bites the dust

If we need it back, we can always rebuild it from information in the question

J.D.

Heh

Hannah Vernon

You missed one, but I took care of it

Sean Gallardy

@ErikDarling ORLY?

J.D.

@HannahVernon hah I almost posted another one to respond to you, but refrained.

Erik Darling

13:39

@SeanGallardy it would probably be easier with your hands

J.D.

I get their goal is to execute multipe procedures in parallel, but service broker isn't the only way to do that. Thought it was a fair question why OP thinks they need to use it.

Paul White

@SeanGallardy It's the new meme, based on dba.stackexchange.com/q/325559/1192

J.D.

Speaking of M$oft licensing. Does anyone know if an SSRS instance needs additional licensing if the SSRS databases (ReportServer and ReportServerTemp) for the instance are hosted on a separate server that's already licensed?

Erik Darling

yes

Paul White

sounds redundant

J.D.

13:43

@ErikDarling Even though a SQL Server instance isn't installed on the machine with the SSRS instance?...would it follow the same licensing model as a SQL Server instance?

Paul White

have you considered taking advantage of the Azure Hybrid Benefit

Sean Gallardy

@PaulWhite Oh I thought you were talking about plan explorer

J.D.

@PaulWhite not sure if serious or not... 👀

Paul White

@SeanGallardy Well, we are. Talking about many things. The standard response to any question or observation now is that it is redundant and can be rebuilt from x. Where 'x' is context dependent.

@J.D. Not serious.

J.D.

lol k

Paul White

13:45

But the licencing situation is different if you have Software Assurance

And it's a Thursday

And more than 2C above the seasonal norm

J.D.

@PaulWhite for sure-ance... Thanks

Paul White

There are about three people in the world that understand SQL Server licencing well and can give authoritative advice. You will never speak to any of them

They're all dead. Only exist as pale reflections in AI Bing

J.D.

@PaulWhite Heh, I'll let the auditors know that they're not qualified to be authoritative on the matter the next time they want to look at my system. 🙃

Erik Darling

you can usually count on the fact that you're gonna have to pay for everything

Paul White

Yes

J.D.

13:49

@ErikDarling Fair enough, lol

Paul White

Square up always involves at least five zeros

Oi m8 u got a loicence for that etc

Erik Darling

there we go

Sean Gallardy

@PaulWhite Thanks for the JEAGL!

@PaulWhite It's Tuesday, good sir.

Paul White

That'll be $5M then

I wonder why there are so many internet questions about hiding SQL Servers on the network

And so many scripts out there to automatically shut them down for a certain period of time

Sean Gallardy

One place I know wanted to "save money" by going to open source, Postgres. They ended up spending more money over the 5 years they used it, before deciding to go back to SQL, for new Devs, migration, etc., so that's always a fun time.

Erik Darling

13:54

what was the expense during that five years?

Paul White

I wonder how these big companies make money out of PG

(I do not wonder)

Sean Gallardy

@ErikDarling I believe it was around 13 million

J.D.

@SeanGallardy It's funny how many unrealized costs there are to changing technologies across stacks, that most people forget to consider.

Sean Gallardy

The SQL Licensing was something around 1M

@J.D. Exactly. I always harp on this. Sure things are free/paid/freemium. Then there's the people, then the other infrastructure, etc.

Erik Darling

@SeanGallardy yes but what cost money

Paul White

13:56

At least they could rebuild the system from the original so redundancy was avoided

Except for the new devs

Sean Gallardy

@ErikDarling They paid for migration help, Dev training, consulting for various items, new tooling and software to work with PG on Linux, Admin training for Linux, etc.

Paul White

2 mins ago, by Sean Gallardy

One place I know wanted to "save money" by going to open source, Postgres. They ended up spending more money over the 5 years they used it, before deciding to go back to SQL, for new Devs, migration, etc., so that's always a fun time.

Redundancy detected. Please remember this is a normalized chat room

Sean Gallardy

@PaulWhite I plead the 5th.... normal form.

2

Paul White

Have a pity star

Sean Gallardy

@HannahVernon I looked at the link you sent last night - got me on the right path. Looks like I can do a proxy setup with host-headers.

@PaulWhite There's a your mom joke in there somewhere...

Erik Darling

13:58

maybe i should learn postgres if the developer training and consulting is that lucrative

Hannah Vernon

@SeanGallardy glad to help!

Sean Gallardy

I was thinking the same thing, tbh

Paul White

@ErikDarling You could intern for Adam?

Sean Gallardy

Is that what Adam did, change to PG?

Erik Darling

i'll bring it up to him when he's in town later this month

Paul White

14:01

@SeanGallardy yep, 'bout 100 years ago now

I wonder what @Zikato is up to

Erik Darling

moved to postgres and then found postgres too expensive in the cloud

@PaulWhite @Zikato is missing opportunities

Paul White

hosting will always be a rort

Erik Darling

last we spoke in person he was talking about developing (not on his own obviously) a query language to interrogate files directly

no database needed

just cheap storage©

Paul White

will never see the light of data

working title: dbase III

Erik Darling

pql, poetic query language

Paul White

14:05

DBase

dBase (also stylized dBASE) was one of the first database management systems for microcomputers and the most successful in its day. The dBase system includes the core database engine, a query system, a forms engine, and a programming language that ties all of these components together. dBase's underlying file format, the .dbf file, is widely used in applications needing a simple format to store structured data.Originally released as Vulcan for PTDOS in 1978, the CP/M port caught the attention of Ashton-Tate in 1980. They licensed it and re-released it as dBASE II, and later ported it to IBM PC...

Erik Darling

that always made me think of this wrestler

Ted DiBiase

Theodore Marvin DiBiase Sr. (born January 18, 1954) is an American retired professional wrestler, manager, ordained minister and color commentator. He is currently signed to WWE working in their Legends program. DiBiase achieved championship success in a number of wrestling promotions, holding thirty titles during his professional wrestling career. He is best recalled by mainstream audiences for his time in the World Wrestling Federation (WWF), where he wrestled as "The Million Dollar Man" Ted DiBiase. He has been named as one of the best technical wrestlers, and greatest villains, in pro wrestling...

Paul White

> The resulting code worked, but was essentially undocumented and inhuman in syntax, a problem that would prove to be serious in the future.

What on GGE is a "color commentator"

Zikato

@PaulWhite Figuring out the stupid tab replace for Erik

Erik Darling

you add color to the commentary

spice things up

Paul White

nerd-sniping.xkcd

@ErikDarling I see. And that's a career is it

Erik Darling

14:08

@Zikato if it's annoying, don't do it. time is precious.

Paul White

sounds like he's having fun 2 me

Zikato

@PaulWhite An "influencer" is a career nowadays

Paul White

true

Erik Darling

@PaulWhite it's usually a career for a retired athlete of some variety who can add insights that people who have never competed professionally can provide

Paul White

well, false, but I get you

Zikato

14:09

@ErikDarling I've already done it, I just have to test it in the pipeline

Paul White

I see the theme of today is earnestly answering my wry observations

Hannah Vernon

@J.D. the comment deletion gods commend you

Erik Darling

look i don't want to be accused of some issue with my "culture"

Paul White

ha ha ha ha

Zikato

What culture is that? 1033?

Charlieface

14:12

@SeanGallardy Not much of an argument if you're building from scratch anyway. And Postgres has a Windows build if you want.

Erik Darling

i finally got a postgres instance on a linux vm up and running, and even managed to get the latest dba.se data dump loaded to a database on it

but that was so tiresome i haven't done anything with it since

Charlieface

PG is the only realistic alternative to SQL Server, something the SQL Server dev team don't always seem to realize. MySQL, MariaDB and SQLite are basically a toy database, and Oracle is so exorbitant, that those who pay for it will do so anyway because legacy, vendor, etc.

Erik Darling

don't sleep on db2

Zikato

Or memedb

Erik Darling

everyone is already using memedb whether they know it or not

Hannah Vernon

14:16

I use MangoDB when I feel the need for speed or never consistency

Paul White

I also use MemeDB daily

Sunil Agarwal ended up on Azure Postgres didn't he?

Erik Darling

no idea

Sean Gallardy

@Charlieface Just like SQL on Linux, I wouldn't trust Postgres on Windows

Erik Darling

you can trust it, it's open source

Sean Gallardy

@Charlieface They realize it, why do you think SQL is all of a sudden getting all the same features as postgres?

Paul White

14:18

Erik Darling

look at you, all linked in

Paul White

I don't use it except to stalk

@HannahVernon Remind me again what it is we're not supposed to migrate

-1

Q: Deleted a table manually and MySQL Server isn't running anymore

I had a bug in my code which kept writing data to a table for gigabytes, tried DELETE FROM TABLENAME but it was taking forever, so I went to the Data directory and manually deleted TABLENAME.ibd Now if I start MySQL server, I get this error: 2023-03-31T09:48:13.913658Z 1 [Warning] [MY-013541] [In...

Erik Darling

@PaulWhite i like being stalked: linkedin.com/company/darling-data

Paul White

I already know where you live

No challenge

Erik Darling

> I googled your question and i fond this:

Paul White

14:23

instant classic

somebody fkd around and fond out

Erik Darling

the fond light of data

Paul White

needs moar span

seriously why would people migrate that horseshit

Erik Darling

just to be rid of it

Hannah Vernon

@PaulWhite I believe the technical term is "crap"

Paul White

closing and deletion works better

migration stubs stick around for ages

@HannahVernon oh yes ty

Anyway

Returned to SO with love and kisses

Erik Darling

14:30

lol

Hannah Vernon

when I looked at the answer, I never even contemplated that the question would be that bad

Erik Darling

how does an answer get that many downvotes with no delete votes

Paul White

explains all the flies buzzing around that page anyway

Erik Darling

or are delete votes not migrated

Paul White

honestly no clue, didn't even check if the "answer" was posted here or SO

I had to disinfect my keyboard after hitting 'delete'

I'll never quite get the smell out

Hannah Vernon

14:33

@ErikDarling I considered unilaterally deleting it on first sight but just wanted to give the user a small chance to redeem themselves lol

stupid me

Paul White

sometimes a flamethrower is the best tool available

if shog were still around I'd ask him to hard-delete it from the database and purge the logs

Hannah Vernon

ohh CEO incoming via Teams lol

Paul White

pay rises for all?

Erik Darling

if there's one thing users are known for around here it's redeeming themselves

Hannah Vernon

@PaulWhite how'd you guess

Paul White

14:36

I googled your question and fond this: 💩

Hannah Vernon

HAH

uh oh budget talk

Paul White

massive budget increase

huge pay rises for all

Hannah Vernon

YUUUUGE

Paul White

We haven't had a massive round of SO layoffs for a while now

Erik Darling

service broker pay raise multiplier

Paul White

14:40

paralilly

Erik Darling

the unbearable light of data

Paul White

wandering lonely as a span

Hannah Vernon

we're in a down market, and revenue is well below expectations

Paul White

only solution is to invest heavily in your people

Hannah Vernon

they're looking at containing costs

Paul White

14:41

with docker?

Josh Darnell

By switching to open source databases?

Hannah Vernon

my money is on Bing

Paul White

in the cloud

Josh Darnell

I hear those are free.

Paul White

many hybrid benefits

Hannah Vernon

14:42

we're looking at vacancies

Paul White

that's a nice phrasing

Erik Darling

well they're much cheaper to hire

Hannah Vernon

and strategic initiatives

Josh Darnell

@PaulWhite I saw Andy Mallon post on Twitter recently that he was looking for a job and wondered about SO.

Erik Darling

what kind of scrub ceo doesn't call that "SI"

Hannah Vernon

14:43

did I mention we're in contract negotiations with the union

Paul White

define: strategic initiatives

balloon

Noun: balloon (plural balloons)

An inflatable buoyant object, often (but not necessarily) round and flexible.
Such an object as a child’s toy or party decoration.
Such an object designed to transport people or equipment through the air.
(medicine) A sac inserted into part of the body for therapeutic reasons; such as angioplasty.
A speech bubble.
A type of glass...

Erik Darling

@HannahVernon burn the labor laws

Paul White

no u

🤣

that's a spelling joke

classic

Hannah Vernon

apparently their preference is to not layoff folks

Paul White

@JoshDarnell yeah

Charlieface

14:45

@SeanGallardy "all" is quite a stretch. array and jsonb look on in interest, and json_agg and json_object_agg say "yeah whatever". Just a couple simple examples.

Paul White

He meant it was 5-10% slower now

@HannahVernon they sound like v nice ppl

only your best interests at heart etc

Hannah Vernon

for sure

Paul White

prolly taking huge pay cuts themselves

Hannah Vernon

obvs

Paul White

solidarity, brothers!

Hannah Vernon

14:50

and they're giving the shareholders big haircuts lol

Paul White

I heard the adjective of choice for haircuts was "aggressive"

Erik Darling

short and curlies

Hannah Vernon

doesn't everyone want an aggressive haircut

Paul White

not me, I have a mop

Hannah Vernon

I buy my hair lol

Paul White

14:53

might give that a shot too

heaps of horses around

Hannah Vernon

I found the new you

Paul White

v good

Erik Darling

is there another lost boys movie getting made

Hannah Vernon

Erik Darling

someone just responded to a tweet of mine from january of 2021

Hannah Vernon

14:59

that's a deep dive

he mentioned databases

Lamak

Wordle 654 3/6

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

\o/

Hannah Vernon

nice!

Lamak

good thing, because I didn't know any other word that could fit

Paul White

Wordle 654 4/6*

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

Erik Darling

you should have picked more green ones and fewer black ones

so to recap, we have:
* a downvote with no comment
* a comment from op saying it worked
* no upvote or mark as correct from op

🤙🤙🤙

Josh Darnell

15:32

Wordle 654 4/6*

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

That was really messing with my head.

J.D.

15:49

@ErikDarling not to mention, still no tag of whichever database system we're talking about here.

Zikato

16:00

@PaulWhite Found you!

Hannah Vernon

Paul's secret life

Josh Darnell

@PaulWhite I just noticed you're posting "yesterday's" Wordle.

How thoughtful.

The whole group being together in Wordle harmony.

2

Best gymnastics group indeed.

Charlieface

@ErikDarling Sounds about right. I guess the DV was because too easy?

Charlieface

16:31

@SeanGallardy While we're at it:

1

A: Is there a way to disable logging / backups for view indexes?

There would be nothing to stop the SQL Server team from implementing a WITH NOINDEXES option for backups, and then rebuilding them from the clustered indexes. In other words: the same arguments you use for indexed views also apply to non-clustered indexes. But the fact remains that they have not ...

Third-highest request on the feedback site: backup and restore a single table. Don't remember exactly, but pretty sure it was on the old Connect Feedback site (RIP) many years ago. And still no movement.

It took absolutely years to get STRING_AGG and it still doesn't have a DISTINCT. And GENERATE_SERIES made me laugh how easy it should have been to implement vs how long it took.

Erik Darling

17:09

@Charlieface not sure, maybe someone mad you answered first

Josh Darnell

18:32

@Charlieface what kind of development do you do in your day job?

Erik Darling

18:49

@JoshDarnell did you get my email or did it get spammed

Josh Darnell

@ErikDarling Crap, I see it now! Last night was a trainwreck so I must have missed it.

Erik Darling

No worries, I just wanted to make sure the code fragment didn’t trigger some phishing thing

Sean Gallardy