« first day (4459 days earlier)      last day (457 days later) » 

1:04 AM
Collaborate, with an AI - how I could I refuse lol
1:44 AM
A: What is the maximum number of countries in which one can simultaneously be tax resident?

user3490Sample answer: Be a US citizen - automatic US tax residency Own a UK property and live there for 91 days to gain UK tax residency Spend 60 days in India plus a total of 365 days over the previous 4 years Spend 62 days in Norway, having been tax resident there the previous year Spend 120 days in ...

@HannahVernon once i've farmed your blog through a language learning model then you cannot refuse
i joke of course: your blog was used as training data several iterations ago
2 hours later…
3:31 AM
@PeterVandivier I feel so lucky to have helped our robot overlords
3 hours later…
6:43 AM
@Charlieface gotta love that one
feckin aye amazed
Wordle 640 2/6

@JohnK.N. nice one!
Thank you
Wordle 640 3/6

5 hours later…
11:28 AM
Wordle 641 4/6*

12:07 PM
i made a tree
Q: Slow query due to passing string value in where clause

Naeem Sardar BhattiI wrote a query to retrieve data from SQL Server. SELECT m.invm_No, m.invm_Date, sup_Desc, m.invm_SupDocRef, m.invm_Narration, m.invm_Year , m.invm_Period, m.invm_No + cast(m.invm_Period as varchar(2)) as DocNoMonth from pos_InventoryMaster m INNER JOIN pos_Supplier on m.sup_Code = pos_Supplie...

i have so many questions
chiefly how 2023-2024 isn't an expression that results in -1
and returns the same data as '2023-2024'
Presumably, it doesn't. The query is faster because fewer rows match?
if it doesn't throw an implicit conversion error, sure
maybe that's why it's faster?
I imagine the question would be different if there were an error
Luckily, raw numbers are implicitly converted to datetimes, to avoid confusing people
But they're quite sensibly storing year ranges in text form
Goodness me, what a question
Jay Dee is on the case though
he does love a redundant comment
If a thing is worth saying, it's worth saying twice, slightly less well the second time
12:14 PM
we all need hobbies
I think you mean Hobbies
It's hard to take things seriously without an initial capital
@ErikDarling gotta get my comment fix for the day. 🙂
I think you mean, "Comment"
i used to date a girl who capitalized every word in texts
artists, you know
...but in all seriousness, I feel like more people than not don't read through the link you commented adequately, and are more likely to react accordingly when directly told what to do.
12:17 PM
Random Noun Capitalization for Extra Import
@PaulWhite Ah I Gotcha. 🙃
@ErikDarling Was she named Pascal by any chance?
Yea just another visual trick to draw attention to the Subject of the sentence. Maybe I'll break that habit one day too.
If there was another girl who used underscores between her words, I'm guessing her screen name was, "snake".
But have you noticed my answers are less sprinkled with random bolds, italics, and other oddly places styles, these days?
12:22 PM
ha ha ha
I hear the Swiss are considering changing their flag from a plus to a minus
what's going on with the swiss now
do i need to stockpile gruyere
Credit Suisse became Debit Suisse
there's a lot of that going around
12:28 PM
seems so
bank nationalization is always a good sign though right
one of the best, yeah
confidence is high
thankfully we learn a lot from these types of crises and tend not to repeat them
for instance, yesterday i thought i had gum cancer, but it was just a piece of spinach
2 days ago, by Paul White
Nothing will ever be that dumb again
HR will send you all to case sensitivity training
sounds like a culture problem
12:54 PM
@ErikDarling the basis of all hope indeed
1:13 PM
Wordle 640 5/6*

do you read these from top to bottom or bottom to top
Top to bottom
Five greens = five letters correct, in the correct position
depends if you're a top or a ... I'll see myself out
Yellow = letter present, but not there
Is that funny in your "culture"?
This is the link if you're so inclined
1:17 PM
i'm going to sue everyone who puts culture in quotes
@PaulWhite yeah lol
Mar 9 at 23:51, by mustaccio
Perhaps in your "culture" this kind of jokes is innocuous, but I wish you'd refrain from them
The quotes were essential for the reference
Your mind is a steel trap I swear
new york times eh
who do you people think you are
Yep, they stole it though
1:19 PM
appropriating my city's newpaper
Yeah, they paid the inventor $1M
Another famous Josh, Josh Wardle
Apparently, Wordle has around 3 million players worldwide, but the number is declining. Wordle has 2,308 possible answers and 12,545 allowed guesses.
From the googs
wait until the six and seven letter word expansion packs come out
So, probably very wrong
it's sort of like a crossword puzzle for dummies
you don't have to know the name of a single violin player
1:22 PM
It's just a bit of fun really
I bought Proust the other day so I'm going to be a lot smarter sooon
You missed an o
soo me
I've also blogged about it, if you like the SQL angle
1:25 PM
this guy, playing plurals
someone have a talk with @Zikato about the rules here
Mar 1 at 18:57, by Paul White
That's a terrible starter. Same vowel twice and plurals are excluded
@PaulWhite meme
Wordle 640 5/6

I get the feeling they like taunting their players
1:52 PM
Wordle 640 3/6

@JohnK.N. 👏👏
This one was hard. 👀
2:42 PM
@ErikDarling did you learn to not eat spinach?
love spinach bro
Popeye the SQL Man
Would never have happened with a manual toothbrush
it was shortly after eating
i was going to do my normal flossing routine
thank you for your concern
real men don't floss
2:47 PM
dental hygiene is for communists
there's a reason Eagles don't have teeth
because they suck?
i was going to say years of cocaine abuse
Cocaine Eagle is the next installment in the critically acclaimed Cocaine Bear franchise
can't wait for heroin sloth
it's a story space with lots of room for growth
3:00 PM
yes it's like when various animals were teamed up with tornados
@J.D. thanks
Cocaine Tornado will be the Alien vs Predator of this generation
3:27 PM
Fentanyl Funnel
meth monsoon
Is it still a disaster movie if it makes things better overall
3:42 PM
every movie pitched by Heap™ is a disaster
4:22 PM
Will that unsort a clustered index back to a heap? — John K. N. 27 mins ago
these trusted users
Hah, just saw that too. Good question.
4:34 PM
imagine if it were a question
You mean a PoSt?
5:03 PM
@JoshDarnell Thanks, didn't get a chance to update my answer on that one yet. I was trying to think of an alternative to re-word it. Maybe a Login level security principal that would prevent them from executing queries on the primary? But sounds potentially janky. Maybe a server level trigger. Idk.
5:21 PM
I'll answer later, maybe.
5:51 PM
@ErikDarling answer provided
6:11 PM
Why don't elephants use computers?
They're too scared of the mouse 🐭
@HannahVernon lol
@JohnK.N. "Removing a Clustered Index from a Heap (Table) will not return the now sorted data...into an unsorted state" and "Adding and removing a clustered index to a heap...may make a big difference on how your database reacts (execution plan-wise) to these changes." - Are you saying that the change in the order of the rows in the heap can affect the execution plan that's generated?
@J.D. removing the clustered index will also remove any automatically generated statistics associated with the clustered index. Removing stats will certainly have an effect on plan generation. Also, any modification to the schema of a table will invalidate all plans in the cache that touch the table, necessitating that they be recompiled. Whenever a recompilation occurs, you may get a different plan.
are you sure it will be a different plan?
what if you drop a clustered index and still have a nonclustered index that covers the query
6:26 PM
@ErikDarling local factors and edge cases are excluded from my blanket statements. Especially if Service Broker is involved.
but yes, you do get a plan recompile, which may or may not result in a different plant. As you know.
different plants are always a problem.
I'm about to do an AG failover, so I'm expecting different plants too
Well I guess my question is, if the data hasn't changed, regardless of the fact that the statistics will be dropped and recreated, won't they still be the same statistics for the same set of data (regardless of what order that data resides in in the heap)?
And hopefully the execution plan that's regenerated is the same (assuming its the same statistics and data from when it was originally generated)?
@J.D. It could be the same speed, but then again it may not. It depends.
not necesssarily, since stats generation uses a page-based sample
@HannahVernon ah that's fair. Only if it was a fullscan sample both times I suppose?
statistics cover only 200 steps
so having the data sorted could impact the statistics and hence the execution plan.
6:30 PM
so if the pages sampled for generation of the clustered index contain different rows than those sampled for the heap, then you may get a different plan because the stats may be different
@JohnK.N. got it, yea this and the point about sampling size makes sense. Thanks!
I didn't want to go into too much details in my answer, but wanted to generally answer OPs question.
Removing a clustered-index will not return the table into the/its original state.
also, depending on local factors
No doubt. I was honestly curious if the order of the data in the heap could affect the outcome, when the data is the same. And I understand now how that can be so based on how statistics work. Appreciate it!
if it's a single page table, then all bets are off as they say
6:33 PM
@HannahVernon Yea I find it's generally safe to say all bets are off whenever trying to predict what the SQL Engine will do lol
unless Paul White is your dealer
if he's dealing you take the cards he deals you and you shuts up
You talk when Paul is dealing?
and a word of warning if he's dealing, never make any comments
@HannahVernon hah
Probably explains why I'm never invited to poker night...
6:35 PM
I live so dangerously that I just failed over my two AGs using nothing but sqlcmd.exe
Other people's AGs fail over. Mine just fail
failover is my least favorite holiday
what's your favourite holiday?
I wrote a really nice little powershell script to failover my Distributed AG and automatically update the CNAME record. It uses a series of sqlcmd.exe scripts to confirm the LSNs match on both Availability Groups before effecting either a failover or a failback to the current primary.
nice blog
6:46 PM
good point, I really need to blog about it
1 hour later…
8:11 PM
> Update: The "In Recovery" indication disappeared. Why is it doing it? Only thing that I did was Stop and Start SQL Server.
I made it thrice into this weeks newsletter
8:29 PM
@ErikDarling yikes, that's one way to do it I guess.
@HannahVernon The pages will likely be the same though. In most cases, when a clustered index is dropped, the heap uses the old clustered pages. Only the b-tree of the clustered index is removed.
@PaulWhite yes, but the particular pages scanned may be different pages
I don't think so.
The 'random' sampling of pages is seeded by the partition id, and that doesn't change.
A: Strange behaviour with sample sizes for statistics updates

Paul WhiteBackground Data for the statistics object are gathered using a statement of the form: SELECT StatMan([SC0], [SC1], [SB0000]) FROM ( SELECT TOP 100 PERCENT [SC0], [SC1], STEP_DIRECTION([SC0]) OVER (ORDER BY NULL) AS [SB0000] FROM ( SELECT [TextVa...

And STATMAN always generates a REPEATABLE (1) clause.
8:36 PM
so random but not really
Random but repeatable maybe?
Anyway, I say "in most cases" because there is at least one exception. If Accelerated Database Recovery is on, the clustered pages are rewritten into new pages.
if there is one thing SQL Server has taught me it's that there are always weird edge cases
Though that wasn't around when the wiki people often refer to was written social.technet.microsoft.com/wiki/contents/articles/…
@PaulWhite why does that cause it to happen that way?
@ErikDarling I don't know for sure, but it does
8:38 PM
that sounds uncomfortable
It's an interesting exception, isn't it
Maybe something around how the PVS works
Though why that would be different to ordinary versioning, I don't know
I suppose now would be a good time to read the Q & A that started all this
@HannahVernon "It depends" is such an excellent phrase when talking about SQL Server.
Oh, it was that question
was it weird?
Also, thanks Paul for dealing out the cards on what actually goes on behind the curtain.
8:44 PM
@ErikDarling No, because AGs weren't involved
that just means you can repro it
Still no unexplained double-length characters
That's the determining factor for weirdness IIRC
Ah, maybe there are some changes with in-row diffs for ADR between clustered and heap
it still seems odd that they would exist for adr but not for other forms of row versioning
They? The in-row diffs? They're specific to ADR
maybe @Forrest knows. he is the red gate expert on adr.
8:51 PM
Oh well if you're going to escalate to a Red Gate level of expertise, I'll bow out
ha ha ha
I mean like wow, way to turn it up to 11
he never replied to my native tongue compliment
what a scoundrel
A lot of the MS people seem to have a write-only relationship with the tweeter
managed service accounts
i wonder why he followed me
summer intern, perhaps
8:53 PM
I wonder at what stage of testing they discovered ADR wouldn't work properly with dropping a clustered index
@ErikDarling For the moderate influence
@PaulWhite Probably in the Community Testing Program
Perhaps. I don't recall it coming up before CTP
i wonder if it was someone doing the drop existing trick with row store > column store
@ErikDarling ?
Oh the MAXDOP 1 thing
8:56 PM
create a row store clustered index, create clustered column store index with drop existing to order things
So long ago before we had the ORDER clause
functioning, even
well, soft functioning
anyway, I see the conceptual relationship but going clustered -> heap is different from going row store clustered -> columnstore heap
not much chance of reusing data pages there
i'm just trying to think of processes that might require a clustered row store index to be dropped from a table large enough to notice an issue
admittedly i don't know what the conversion process would look like using drop existing there
It's interesting because I'd like to know if reusing pages would have caused an assert or something in the ADR code (and so be very noticeable) or just affect performance in some way (which might not be so noticeable, as you say).
Most people notice their SQL Server connection being terminated abruptly.
9:03 PM
yeah, i would assume there was some corruption issue
Maybe an sLog issue. It's interesting to think about
I haven't thought about the mechanics of ADR/CTR recently
i don't remember you saying much about adr other than it being a+ aside from when it caused issues with scan performance or something
Oh well yeah if you have lots of versions to traverse
You lot bring a whole new meaning to being in the weeds of an issue
but that's row versioning in general, not just adr
figuring out what color all those marbles are
9:10 PM
All this because someone said you might get a different execution plan
well, someone was wrong on the internet
@PaulWhite might
that wasn't the original statement though
> Adding and removing a clustered index to a heap may have a major impact on performance and may make a big difference on how your database reacts (execution plan-wise) to these changes.
Was that the orginal statement we're referring to?
3 hours ago, by Hannah Vernon
@J.D. removing the clustered index will also remove any automatically generated statistics associated with the clustered index. Removing stats will certainly have an effect on plan generation. Also, any modification to the schema of a table will invalidate all plans in the cache that touch the table, necessitating that they be recompiled. Whenever a recompilation occurs, you may get a different plan.
Or that one?
second one
9:16 PM
I have a headache now
I'm sorry I brought it up
You'd definitely get a different plan if the original used the clustered index 😀
also, I have to write three nonsensical queries on a DB2 database that is being moved into a Mongo instance
you'll notice I'd rather be here
even with a headache
No one has a sensible job these days it seems
Why would you get a SQL Server person to work on DB2/Mongo
that's my thoughts exactly
Do you not have interns
9:19 PM
especially when I have no idea what the structure of the database is
I'd rather do HR sensitivity training
even though I'm ostensibly the target of said training
@ErikDarling It is, but ADR adds a few wrinkles like in-row versus PVS versions, and the whole reverted transactions thing
can't we just put everything in the cloud and make it someone else's problem
I remember Erland had a neat demo of ADR being very much slower than row versioning for a metadata change (nullability of a column IIRC) but only on heaps.
i wonder what ChatGPT has to say about the effect on plans for clustered indexes being converted to heaps
Depends whether Reddit ever covered the topic
Imagine if they'd called ChatGPT AskReddit instead
9:23 PM
> In summary, changing a table from a clustered index to a heap can have a significant impact on SQL Server execution plans. Depending on the queries and operations performed on the table, the performance may be negatively affected. Therefore, it's important to carefully consider the trade-offs before making such a change.
so there you have it. it's dangerous.
Imma ask it about Execution Plan Plus
> "Execution Plan Plus" is not a better name for actual execution plans in SQL Server. An execution plan is a graphical or textual representation of the SQL Server query optimizer's chosen plan for executing a query, and it shows the flow of operations and the order in which they are executed.

> The name "Execution Plan Plus" could be confusing because it may suggest that it is an enhanced or improved version of an execution plan, when in fact, it refers to the actual execution plan.

> Moreover, the term "Execution Plan Plus" is not a standard or widely used term for execution plans in SQ
fucking plagiarizer
on the plus side, ChatGPT agrees with us
Did you ask it for a source
attribution required
> As an AI language model, my answers are generated based on my training data, which includes a vast collection of texts and documents from various sources. In the case of the answer to your previous question about "Execution Plan Plus," I used my knowledge of SQL Server and database terminology to provide a response based on common industry practices and standards.

To clarify, I want to emphasize that "Execution Plan Plus" is not a commonly used term for actual execution plans in SQL Server. It's important to use accurate and standard terminology to ensure clear communication within the S
Well, I meant about the dropped clustered index but sure
context is important you know
not to me
I'll star anything funny out of context
9:33 PM
me tooo
I wonder what Jay Dee thinks about it
Curiously, even when ADR prevents the clustered data pages being reused, the newly-allocated pages have previous- and next-page pointers just like the original clustered index.
in adjunct to forward and reverse record pointers? Is that what you're saying? I'm having a hard time groking what you're on about. But then, that's par for the course for me, probably.
I'm only slightly more sentient than ChatGPT apparently
and perhaps that's even arguable
@HannahVernon Heap pages aren't linked together
right but rows are, though?
it's just a list of pages
Not really. Where they are on the page is in the offset array at the end of the page, but the rows themselves aren't linked directly
Index pages have a previous-page and next-page pointers so you can do ordered forward and backward scans
9:44 PM
Those are the pointers I'm talking about, the page-level ones
so a heap converted from a clustered index retains those pointers under ADR?
Well they're retained in both cases. Expected when the pages aren't rewritten because, well, they weren't rewritten. When ADR is on, new pages are written. It's unexpected (to me) that the rewritten pages are linked forward and back.
it's unexpected to me too, because I have only a limited depth of knowledge on page physicals
i.e. I can run DBCC PAGE
Is there any quicker way to get Erik to leave the discussion than posting an xkcd
9:47 PM
apparently not
@HannahVernon Next and previous pages are shown by DBCC IND as well
@HannahVernon Well, like all internals stuff, it doesn't really matter - until it does, I guess.
for sure. most people don't know how to build a house even though they live in one
That's true, though I don't think I could build a database engine either
Some people know more about housing internals (plumbing, electricals and so on) than others
My typing is really off today
9:54 PM
that's like my ability to write stupid queries in DB2 is really off, for like, well, forever
Who's our resident DB2 expert
not me, that's for sure
Is it mustaccio?
I wouldn't doubt it
May 26, 2022 at 19:44, by mustaccio
Somehow my score on the [postgres] tag has grown higher than the one on [db2]. What's happening to me?
9:57 PM
wow you're good
@HannahVernon I've been summoned...
I think it's cool you can say you've helped develop ChatGPT.
Chat JPD
@J.D. Court got you again?
@HannahVernon very possible in my near future since I'm driving right now...
oh, wait, that's summonsed
10:01 PM
@HannahVernon I have a headache too, so I knew what you meant heh
@PaulWhite What's the P stand for in that context though?
@J.D. I took a guess at your middle initial
The comparison works best if it's Peter or something
@PaulWhite There's a childish joke somewhere in there about putting a P next to a D, but I'm too tired too think of it...
No doubt it's Xavier or sthg
10:05 PM
Close, William.
Chat JBD then
You seem billable to me
Hah nice. That's moreso my father.
how is that Bill is short form of William, I mean what wonky person thought that up.
or Dick, for Richard
Or Jack for John 🙃
yah, I mean why
these are the important questions
10:07 PM
Jim for Jimothy (for any The Office fans in here.)
US version, sorry Hannah.
no worries, we have the US version here, too.
I mean Canadians are actually just Americans who've had therapy anyway
@HannahVernon Hah I like that.
Tried the British version, wasn't a fan. But love Ricky Gervais otherwise.
yah me too... just heard it on "Sort Of"
10:33 PM
I found the British version unwatchable. Just made me cringe.
Never experienced the US version.
@PaulWhite I was forced to take the family out to dinner
in sql server, does there exist a trace flag or somesuch to identify indexes that were considered during optimization?
i'm in a disagreement with a vp of software engineering who just wants to add another nearly identical index to satisfy a single query and i it upon myself the quixotic endeavor of quantifying the impact of an over-indexed table
11:06 PM
@ErikDarling They don't like xkcd either?
@swasheck Not really. You can look at the final memo contents to get an idea, but early pruning and discarding of alternatives can mess that up. In short: no, at least not as simply as one might hope
thanks @PaulWhite ... how are you?
about average
you're not mean. you're quite nice, as i recall
11:25 PM
@PaulWhite no one actually likes it. everyone just pretends to so they fit in.
Certainly true for me. Nothing I take notice of more than social pressures

« first day (4459 days earlier)      last day (457 days later) »