The Heap™ – Consultancy ©® - 2024-03-28

« first day (4832 days earlier) ← previous day next day → last day (30 days later) »

12:06 AM

The great Itzik. I'd forgotten that article, but flip-flopping two tables is a classic solution to this problem. It makes it much mroe complex though. I was hoping to avoid that.

5 hours later…

5:14 AM

Wordle 1,013 3/6*

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

1 hour later…

6:30 AM

Wordle 1,013 3/6*

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

@Zikato Does I understand that right, that just eliminating the PRIMARY KEY keyword resulted in the SPOOL being removed?

1 hour later…

7:45 AM

@JohnK.N. Yes. If the goal was to remove the spool then instead of seek and spool I gave scan and sort. You still gotta have Halloween protection, no way around it.

@Charlieface You're so picky

8:04 AM

Wordle 1,013 2/6*

⬜⬜🟨🟨🟨
🟩🟩🟩🟩🟩

8:44 AM

@PaulWhite congrats

Luck may have played a role today

No way!!

8:59 AM

25

Q: New Focus Styles & Updated Styling for Button Groups

New Focus Styles We’ve released a design update to focus styles across the many components within our design system and as well as a new design for our button group component. Why are we updating the focus style? Prior to these recent changes, we would commonly apply a custom focus ring around el...

discussion featured design accessibility focus

2 hours later…

10:40 AM

Should've got it right first time

Is that referring to your Wordle?

UI design, but yeah it works there too

10:58 AM

@PaulWhite focused on changing the right things I see

These days, I'm just happy they're keeping busy without stuffing anything up too badly

Having low expectations really is the key

More good blog posts, @Erik

@PaulWhite That applies to every aspect of life

No, I wouldn't agree with that. And neither would your wife or children

My expectations are uniquely low for Stack Overflow Inc.

I have moderately low expectations of other tech companies, ofc

Successfully acquired some chocolate fish today, btw

My mood has improved accordingly

11:22 AM

cokolandia.cz/en/cokoladove-sady/cokoladova-sada-ryby

Was trying to find if the NZ variant is available in Czechia

They're pretty

Much more exciting than a chocolate-coated raspberry mallow fish shape

Be a shame to eat them

Chuck 'em back

12:28 PM

user image

fine start to the day

12:40 PM

Those statements weren't meant to be connected 😄

😤

I don't have low expectations of your blog posts

I suppose I could try, if that would make you happier somehow

no, i prefer to be put through the ringer when i'm wrong or the information is incomplete in some way

wringer

anyway

having high expectations means I am more likely to bother reading carefully

there's plenty of crap out there not worth spending time or effort on

12:59 PM

yes, wringer

i do not expect you to phone me about my posts

good or bad

oof

> Build="10.50.1600.1"

0

Q: optimize performance of reading from sql view

I created view based on complex select query (takes 2 second to retrieve the data). When I'm trying to select data from this view based on a predicate, it takes more time (8 second). this is the query plan. what should I do to reduce this long time. thank you. execution plan https://www.brentozar...

sql-server query-performance view

EJECT, EJECT

1 hour later…

2:17 PM

@PaulWhite Did you have any bright ideas on that Spool? I was looking over your article but it doesn't fit the MERGE optimizations, not sure what else to try.

1 hour later…

3:41 PM

Nov 27, 2023 at 15:56, by Josh Darnell

I'm looking forward to this classic challenge where people provide solutions to the original challenge, and @Charlieface adds rules each time to make them not valid (e.g., "This is a non-default setup").

3

😉

timeless

chuck e. face is asking for an update both without a spool and without a phase separating operator or "adding complexity"

so you know

we're already run into a nice wall

Happy Halloween

Could anyone help me with an English problem? I don't know what to google.

I'm building a program in Python that shows letters in computer and handwritten font so my daughter can practice. I'd like to add top and bottom lines as you'd have them in a workbook.

My main problem is with the letters that go below the line (found out they are called "descenders"). But I don't know how are the top and bottom lines called

@SeanGallardy My favorite holiday. :D

3:54 PM

@Zikato Tall letters touch both, or are you asking what the top and bottom lines are called? If so I always was told "rule line" but that might be local factors.

@J.D. Because that's the one day you're above 0 coolness? :P

he's asking about letters that ascend above the top line

perhaps they're just called ascenders

We shouldn't have any letters that go above the top line

@SeanGallardy rule lines is what I'm after. Thank you

the only thing that goes above the top line would be superscript

@SeanGallardy It's the one day you can pretend I'm Josh and it's ok. :P

3:57 PM

@J.D. That's not what my fantasies say

Some diacritics could go above the top line

@Zikato We don't have any in English

We do have plenty in Czech

Only other made up languages use those

Sep 18, 2022 at 16:32, by Zikato

Příliš žluťoučký kůň úpěl ďábelské ódy

3:58 PM

See, made up language

all languages are made up

Next you're going to show me an l with a slash through it

Which I can get behind, stabbing letters

@SeanGallardy That's Polish. Ł ł

Hmm, backwards N?

@SeanGallardy Remind me, what's the symbol for the dollar?

3:59 PM

@Zikato $

Looks like impaled S to me

S that's been put on a stripper pole

You barbarian

:D

Look at me, all Romanian

Stacking bodies on poles

Sean Țepeș

4:01 PM

user image

@SeanGallardy depends on how you dot your i, for example, a ❤️ might go above the line

Fail.ai

Vlad Țepeș (disambiguation)

Vlad Țepeș or Vlad the Impaler (1428/31 – 1476/77) was voivode (or prince) of Wallachia three times between 1448 and his death. Vlad Țepeș may also refer to: Vlad Tepes (band), a French black metal band Vlad Țepeș (film), a 1979 Romanian film Vlad Țepeș, Călărași, a commune Vlad Țepeș, Giurgiu, a village Vlad Țepeș League, a political party in interwar Romania 528th Reconnaissance Battalion "Vlad Țepeș", an element of the 2nd Infantry Division of the Romanian Land Forces == See also == Dracula vlad-tepes, a species of Dracula orchid

@Zikato I'm changing my name

Any Temporal Table gurus here, or am I still the residential "expert"? >.>

4:02 PM

Wonder if seantepes.com is available

@J.D. what a sad, sad day for the dba.stackexchange

@J.D. You and @PaulWhite since you know he loves them so much

@Zikato You're telling me. You guys gotta step it up. ;)

@J.D. When you start using features that more than 10 people use... we'll start thinking about it. Next you'll tell me you use the block chainz.

@SeanGallardy heh

4:04 PM

Hannah also likes to torture SQL Server

Someone's gotta be the first.

@SeanGallardy speaking of that:

theverge.com/2024/3/28/24112507/…

@Zikato Was gonna say, how many people does Hannah count for?

There's at least 2 of us. :D

@Lamak only 25 years?!

Much like my Friday nights he got off easy

yup, just 25, maximum was 110, suggested 40 to 50

4:08 PM

Conjoined twin Abby Hensel, of TLC’s ‘Abby & Brittany,’ is now married to an Army veteran

i have questions

@ErikDarling Hey I don't mind a more complex solution, but flip-flopping two temp tables is about three times as much code.

who knows how much code will be too much for you

it's entirely subjective

what would you find too much code to get that query to run non-blocking?

if you think it's reasonable go knock yourself out, was just saying it feels like it's not worth it.

well if it's code golf, call it that

isn't the whole of this site just some kind of game anyway?

@ErikDarling wait that's the RTM build???? or should that be the WTF build?

4:16 PM

azure sql database, now with extra AI

techcommunity.microsoft.com/t5/azure-sql-blog/…

trying to decide if the comment there is sarcastic or not

>

AI uncovers hidden knowledge, cutting through the wild data to find answers.

@Charlieface yep, fresh out the factory on that one

@Zikato waistline and baseline lettering-daily.com/calligraphy-guidelines/#:~:text=Waistline

4:34 PM

@Charlieface at first glance, i'd probably dump the child cte into a temp table and truncate it at the end of each loop, using manual phase separation instead, so that the update isn't self-referencing

@mustaccio thank you!

@ErikDarling What like this dbfiddle.uk/SpjhHNyS that's an option. But what does it gain you over the spool?

dbfiddle.uk/C3iEvb2L

buddy

you said you wanted to get rid of the spool

temp tables have many load and write optimizations that spools don't

so minimally, those

that's interesting like what? the spool still maintains the sort order so that's not an issue.

spools are loaded a row at a time, etc

not a big deal for this row count

4:44 PM

No for this rowcount I wouldn't get out of bed

was wondering how it would perform on much larger sets though

just getting rid of the spool doesn't help if it's something even worse (like a cursor), point is to make it more efficient.

I was also wondering if it was possible to prove to the compiler it doesn't need Halloween here

create a much larger data set and test it

mark it const

:D

(that was a joke)

Use the TRUST_ME_BRO query hint

that would be amazing

a whole new consulting world spawned

savings people from devs using (NOLOCK, TRUST_ME_BRO)

@ErikDarling By the way that fiddle needs to move the rowcount check dbfiddle.uk/c8jwsVxX otherwise results are wrong

4:50 PM

oh yeah sorry about that

i tried adding in a not exists that would reinforce the check constraint logic but it didn't seem to help out

at any rate, i'd use the temp table approach if the goal is just to not have a spool in the update plan

i'm not sure there's a way to get rid of HP spools in self-referencing updates

without introducing another blocking operator, anyway

you seemed to disfavor that earlier

@SeanGallardy I've never encountered a 3rd party (SQL Server) database system that doesn't use the NOLOCK hint. It's pretty amazing TBH.

Yeah it's weird, I see them a bunch on update and delete statements too

they're ignored on the table that's getting modified, but if you're relating a table (even the table being modified) in some way elsewhere in the query, you can certainly make incorrect results permanent in the database

Yea makes sense that DEVs who don't know what they're doing also don't know where they're doing it.

@ErikDarling AFAIR they aren't completely ignored, they're still used in the read phase, which then makes deadlocks more likely because missing U-locks.

4:59 PM

Thank you for subscribing to Temporal Table facts! Fact #37 - Only tables with Primary Keys on them can be made Temporal. Though you can UPDATE the value of said Primary Key, disconnecting it from its history in the history table anyway.

@J.D. sounds like a good way to avoid foia requests

heh

I'm sure Microsoft was like "eh, you shouldn't update your primary keys anyway, ship it".

@J.D. Logically, updating a PK isn't really updating, it's a delete and insert, and is looked at as such by the compiler.

@Charlieface Isn't that technically true for all updates?

check out jacuzzi dolphin, not using an identity column for a pk

5:05 PM

:D

No only for primary keys, which is the "primary" method used to identify a row.

i get that it's probably inherited from the other system, but you know

Also, I don't think Temporal tables respect it correctly as both a DELETE and INSERT in the history table anyway lol.

@Charlieface If I create an AFTER UPDATE trigger on a table, and do an update to that table, isn't the old row version in the virtualized inserted table with the new row version in the virtualized deleted table, reflecting that an UPDATE is really just a DELETE and INSERT under the hood?

You can prove that by looking at the physical page being modified

@HannahVernon Prove it which way? >.> :)

5:09 PM

If the PK changes it's not the same row anymore, makes no sense to correlate the history.

@J.D. not sure what you mean, it doesn't actually tell you what happened anyway, that's Change Tracking.

@J.D. Don't think so, that's probably just an artifact of the way triggers are set up. For a normal update the page is modified to replace the existing row, if it's a PK change then the whole row is deleted and reinserted.

See also this article on Split/Sort/Collapse red-gate.com/simple-talk/databases/sql-server/learn/…

@Charlieface I guess my point is it seems a little silly of a requirement for Temporal Tables to need a PK if said PK is mutable. But I get it.

@Charlieface Cool, I'll check it out, and will take your word about triggers and what I mentioned being just a design artifact of them. I don't really know, just always assumed. Seems like I'm learning a lot in this 5 minute conversation heh.

don't have hard facts, I remember reading something a while back on it don't recall where

@PaulWhite will probably come along and embarass me now...

@Charlieface nah, I'm here, he'll dunk on me first. You're safe ;)

Actually pleasantly surprised he had no comment other than "odd transcript today" the other day when I forgot how window functions worked. >.<

@Charlieface To this point, Temporal Tables log DELETEs normally.

But if you change the primary key in the source table, the DELETE isn't logged in the history table.

So to your point of a PK UPDATE really being a DELETE and INSERT, if Temporal Tables functioned properly, they should log that DELETE.

it's funny to me that the rows are only associated via the primary key

like there's no internal linker

@ErikDarling Yea true this too, back to the silliness of Temporal Tables requiring a PK on the source table to begin with.

5:20 PM

this would be fun to blog about if it didn't require so much setup

are you using any of the specialized query syntax to hit the temporal tables?

@ErikDarling Nah not yet. I don't love it syntactically. I actually found this nugget of knowledge that utilizes some of that syntax and almost had a use case for it.

there's a lot of almost useful in the docs huh

By the title (and given the example just preceding it) I thought Microsoft was about to tell me how I can manually update the temporal datetime2 columns in the source table. Which is the problem I'm currently stuck on. But was quickly disappointed.

@ErikDarling wat? that took about 1 minute dbfiddle.uk/3TSBjD-r

@ErikDarling Yea. Honestly I think Microsoft's docs are relatively good. But the example portions of them have me scratching my head sometimes.

5:23 PM

@Charlieface i meant in writing about it

crafting the blog, etc

Well one needs a blog to start with...

They'll be like "hey here's an example on how to do ABC" and then the example will do "ABC but also not explain XYZ and 123 that had to happen to get to ABC".

"this is obviously much more difficult when using identity or sequence objects, but if your temporal table is used with data pulled from a separate source system, or the application is assigning identity values, you could run into problems..."

it's a whole thing

screenshots, humorous section headings

humorous section headings

@J.D. What do you means? temporal tables just log the previous state of the row, there is no action : deleted column unless I'm mistaken.

@ErikDarling I take this back, looks like U-locks are still applied dbfiddle.uk/aYvLzRu1

1

A: Update TimeStamps in "normal" Table in a Temp-Table scenario

From the comments, it appears the real question is not how to UPDATE an ALWAYS GENERATED column, but how to migrate data from a temporal table into a new temporal table. If you need to copy data from a different (temporal) table to a new one, and retain the history dates, then create the new tabl...

@Charlieface oh yeah i forgot to respond to that. i think your fiddle is incomplete though.

> Msg 208 Level 16 State 1 Line 3
> Invalid object name 't'.

5:30 PM

didn't bother with the rest of the repro because dbfiddle won't allow it anyway, but that's what you need on your own machine

gotcha, thanks

5:49 PM

> what is our strategy for potentially modernize our C++ code to Rust to improve security, maintenance and developer agility?

Yes, moving to Rust just magically fixes everything.

MAGICALLY

i hear many good things about rust from rust developers

and people who have never used rust, but who have spoken to rust developers

That's crazy! I hear great things about GO from GO developers

and people who have never used rust, but who have read things about rust written by rust developers

I know nothing about rust, I just love the bandwagon folks where <insert new item> will fix everything

@Charlieface Technically true. I might be getting confused in the examples I was just trying to create - to Erik's point about "so much setup" lol. I have to re-test when I'm back in front of a computer but I feel I saw different behavior between updating the PK vs actually deleting the row, than expected.

6:54 PM

@Charlieface Just saw you sent this now, thanks! Not sure if it's the same solution as this one but this got me across the finish line. I don't understand it fully yet, seems kinda buggy-ish, but it works.

7:07 PM

I didn't see this shared here.

lwn.net/Articles/966868

itv.com/news/anglia/2024-03-27/…

2 hours later…

8:40 PM

> A pilot who died in an aircraft crash at a war museum

Jeez.

Well it just goes to show you

Never retire

1 hour later…

9:47 PM

@SeanGallardy To be fair, it makes little difference which language you use, anything is better than C and C++ (as long as it isn't Perl or PHP!). The amount of footguns that even an expert can give themselves is far beyond modern memory- and type-safe languages. And the libraries and tooling are generally far better in them than in C++.

I'd love to see what SQL Server would look like if it was written in high-perf C#. There's lots of new memory management and Span stuff going in now, and it would be nice to be able to handle tables as real objects. We can but dream.

You’re more likely to see any new database engine written in Rust as an alternative to the Cs

10:07 PM

Fine whatever. Point is I disagree with Sean, moving to a modern language does indeed MAGICALLY fix at least one third of all bugs. I have almost never had an app written purely in a managed language crash and dump on me with an access violation or, and where it did it was a runtime bug, not a bug in my code.

Out of curiosity, do you code anything apart from SQL?

One might ask if I even code SQL

10:24 PM

People say SQL isn't a programming language, it is. It just has a specific use-case, and isn't a general-purpose language. Matlab also isn't, for eg.

Arguably C and C++ aren't either, they are high-performance safety-catch-off languages, designed for things like kernels, device drivers and embedded systems.

10:46 PM

@Charlieface I have, it's fun, you should try it. Probably why I don't see all this stuff as magical, because while it may have some items that help, there's a trade off. I guarantee changing to the everything is magically safe language of Rust will still result in AVs and other such items, whether or not they are as bad as they are today may be another matter.

Don't blame poor programming on the language (unless it truly is a language issue), blame it on the programmer.

@Charlieface You mean SQL like T-SQL or do you mean SQL like the SQL Server Engine?

The answer to both is yes

How do you get an AV in a managed language? I guess if you redefine Index Out of Range and Null Ref exceptions as AVs then yes, but it's not going to cause undefuned behaviour.

You use other people's libraries which may or may not be safe

which you may or may not have a choice in doing

even though those libraries are in managed code, but you can turn safety off for those too

Fair enough that's a point but unsafe and similar is less and less likely these days.

@SeanGallardy I was actually responding to Erik there

Oh

I thought you were still talking to me

I'm fairly even-handed in my drivel TBH

10:50 PM

Sometimes I find the transcript hard to follow

plus I don't go back further than whatever is on my screen

Paul is fond of telling me to consult the transcript

@SeanGallardy But C and C++ really do seem to have more badness than others, so much cruft and backwards-compat etc. And the amount of incomprehensible boiler-plate seems insane sometimes.

It's all very well saying "blame the bad worker" but if you give a diamond cutter a sledgehammer you might not get the right results

From what I've seen (which isn't a ton) all of the languages I've worked with have that to some extent or another

@Charlieface Right, that's why I said sometimes it is the language

Ah DBAs, always "it depends"

well it does depend yadda yadda I know I know don't hit me

Now you're getting it!

Anyway what do you code, you being Sean this time?

10:56 PM

For work? Mostly C++/C#, for my own little project, whatever comes up but mostly C#.

I'm not great at any of them

Last big thing, if you even want to call it that, was automated sql dump analysis

I grew up as a kid mucking around with BASIC (hacking x86 machine code into it obviously), moved on to VB, then VB.NET (again some MSIL bytecode), finally got a real job and dumped it all for C# and SQL. But dabbled in Powershell and PHP along the way.

@SeanGallardy Is anyone? Sometimes I think the mark of an actually great coder is Imposter Syndrome. If you don't have it then you probably aren't great.

Yeah I started in BASIC, then VB4, then C, C++, had to do intel and motorola assembly in college, then Java (god I hated Java), switched to PHP for some random projects, then to .net 1/2, asp.net, back to VB6, then C#.

@Charlieface Every time I think I have something down, someone shows me a new way to break it and I feel dumb. I'm not a purist, I don't go around quoting the specs, I'm not in academia where everything is always perfect and works perfect, never any network loss.

back to VB6?? I feel your pain. It was a really fun language, but missing so much in modern stuff and lots of weird edge cases.

Yeah I worked at a hospital system and they had all this legacy stuff in VB6 which controlled... uh... critical care things.

It all ran on a computer, at an empty desk, that had a sign on the monitor, "do not turn off" and ran Windows XP

It was upgraded to that because the Windows 95 version of it finally blew up

For fun I managed to get multi-threading working. The IDE ran the whole code inside the same process, and I crashed it a million times. It was entertaining watching one thread being debugged while the other merrily went on its way. You had to make sure all your extra threads ended before main, otherwise it could garbage collect the code and crash.

11:06 PM

yeah I don't miss it, but I had some good times

@SeanGallardy Sounds like the NHS.

Yeah I worked on some stuff for NHIN... that was so bad... and all Java based.

Josh will tell you I'm a better DBA than a programmer

Except for query stuff, which is the devil

Healthcare is crazy when it comes to these things. Not realizing "it worked, don't fix it" doesn't cut it these days, look at WannaCry

That hit a ton of hospitals

There was a big hospital system in the UK it hit

And cash machines

Yeah it's called the NHS. And it's not one system unfortunately, much as it likes to imagine.

11:09 PM

Oh, I see, kind of makes sense, all large systems seem to be smaller systems garbaged together

I've never worked on a real time OS, but that's recently (OS) where my interest has been

Kinda. Like a hundred little publicly owned fiefdoms where some idiots who know nothing about healthcare (or IT clearly) make decisions affecting millions

@Charlieface Oh you work at MS?

:P

That's not publicly owned......

Yeah, I hear government is extremely fast and efficient

Singularity and Midori projects looked so interesting. Not RTOS but managed. Why did MS drop it?

11:12 PM

Oh man that takes me back, yeah Midori was interesting

I believe the biggest reason was no one was going to port code to a new OS

Completely managed kernels and drivers, everything running in Ring0 (twice as fast), C# running directly on the kernel, what's not to like?

Yeah I was pretty blown away with what they were able to do

It's like Temporal Tables

or Service Broker

Everyone been writing WPF for years anyway. And with everything going online it doesn't make much difference. Apple didn't give a toss, they broke everyone twice

Apple is Apple

Apple will Teams you hard any day

?

11:15 PM

Teams, to many in our org, is a synonym for any favorite 4 letter word

because it's a 4 letter word

Wasn't there something about Teams completely changing recently?

@Charlieface oh I mostly meant that the majority of my work is tuning queries. Some of it is writing processes in SQL that are a bit more procedural and requires stuff that looks more like programming.

But the plain answer to your question is that I don’t use anything else

Like I said, SQL is programming as well. Programming is telling a computer what to do, SQL does that.

11:55 PM

@ErikDarling Those procs you've written / worked on, especially sp_HumanEvents, I would definitely consider programming.

Those things are epic.

« first day (4832 days earlier) ← previous day next day → last day (30 days later) »

Transcript for

Mar28

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile