i was thinking about writing about how people get very concerned about anomalies under rcsi that are also present under rc and even worse under ru but they seem to think would prevent them from using rcsi successfully

there was something else too

about rcsi

i don't think it's understood well enough that other major database platforms use mvcc in a similar way by default, and rcsi is the default in azure sql db. probably because no one uses azure sql db.

hot take: azure sql db is sql express for people who hate money

☁smoke bomb☁

@ErikDarling nine point nine times out of ten, RCSI (MVCC) is a concurrency silver bullet. It's that one proc in a hundred that causes anxiety.

@PaulWhite so much latch hotness. Wow I feel so dumb.

This is an interesting tidbit (2 me):

> To make the log easy to clear, I’ll set the recovery model to SIMPLE and then FULL without backing up the database. This will leave the database in a state where full logging occurs but a checkpoint can still truncate the log.

Yeah I don't know if that's well covered in the documentation anywhere. The closest I found quickly was:

> When you first create a database using the FULL recovery model, the transaction log will be reused as needed (similar to a SIMPLE recovery database), up until the time you create a full database backup.

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

It doesn't say you can get back to that state by switching to full then simple

I suppose some people will know the log auto-truncates in that situation, but not necessarily that full logging will be performed

@JoshDarnell If you want to talk about SSRS, then go on. We're here....

Morning

Morning

@ErikDarling Please do, I'm in the middle of convincing a team to do that

If you want to ask a question, then you could try DBA.SE or Stack Overflow. I heard they occasionally answer questions on those sites.

We use SSRS at our shop, but it's not much fun, since Microsoft restricted (cancelled) the use of multi-SSRS instances.

@JohnK.N. You can find the current detail here: twitter.com/josh_the_coder/status/1565043728561197057

> We have configured the CodeGroup elements for CAS in RSSrvPolicy.config, and that worked for the "web host" process, but the Windows Service (that runs the reports for subscriptions) doesn't seem to know about it.

I see you replied

😎

Last 4 (3 effectively) weeks of my notice

@PaulWhite I gave it a shot in the dark as a reply to Josh's first reply. Yes.

@PaulWhite I knew about the need to take a backup after switching recovery models, I had no idea about the specifics.

@HannahVernon sqlskills.com/blogs/paul/…

What a helpful little chat this is

HtH

@Zikato Moving to Stack Overflow? Or Microsoft?

I believe those are the only two options

Another blog bites the dust

@ErikDarling Oh no what happened

@PaulWhite Neither, I'll be working with @Forrest

Oh, good for you. He has a lot of fun with flags.

Smart man too.

Small world.

I've applied for SO before, but they've hired Aaron Bertrand instead. I didn't stand a chance

Serves 'em right if you ask me

@ErikDarling Fewer blogs mean more potential traffic for you. But if you meant me, I hope to continue

@PaulWhite it was a joke about @Zikato moving to Microsoft, Destroyer Of Blogs©

Ah right

Though they hide them more than destroy, for the most part anyway

They're certainly infuriatingly hard to search for these days

Total lack of SEO

Internal blogs sure

But it seems everyone who starts workings there stops blogging independently

And you feel that is a net negative?

No one at Microsoft has time to maintain a blog. They spend all day fighting Teams.

I can see that

My relationship with Teams soured when I tried to join the interview with my personal account and it kept switching to the current company's account. And none of the tricks (cookies, private mode, etc.) helped

Switching accounts is no doubt the worst part of it. I lost track of the number of times I had to have support reset things their end, or mess around with the Microsoft Authenticator app. I wouldn't have minded so much, but once successfully in Teams, I regretted overcoming the obstacles every time. The bare-bones video meeting facility often worked reasonably well. But that really ought to be a given.

It's a miserable piece of software in all other respects, except the uninstaller as Erik pointed out.

@PaulWhite I feel it’s part of a general internet trend that I dislike. Too many walled gardens, fewer and fewer independent creators.

Oh you're making a serious point

Ok well I agree with that

By independent I mean they don’t rely on a platform or service to reach their audience.

I guess. It didn’t start that way. Then you made me think about it.

You know how I detest thinking about things.

We all do really

Pains the brains.

No worries, that's what I'm paid for

@Zikato when’s the last time you saw a post from Forrest? 😂

darn, wasn't fast enough

tbf it's hard to see the posts for all the b-trees

@ErikDarling I'd rather not throw any stones, living in the glass house and all

hm?

misclick

It's not easy to proofread the referenced message ID

@Zikato hey, me too ;D

@Zikato oh right gotcha

i am literally sending an invoice rn

living the dream

so many invoices, so little time

sounds like you have an impressive hourly rate

Morning

again

@PeterVandivier quite reasonable

@ErikDarling :(

@Forrest nice blog

🐝🌳

@PeterVandivier That's awesome!

SQL Server never combines a self-join into a single lookup, right? Apparently it does sometimes dbfiddle.uk/…

Relevant to this question

@Charlieface Which one do you think is an example of a self-join?

That said, the UPDATE FROM extension has some horrible behaviours somewhat maintained for backward compatibility.

And there are all sorts of things you can do e.g.

WITH U AS
(
    SELECT TOP (1) Flag
    FROM [Table]
    WHERE Name = 'DEV'
    ORDER BY ID DESC
)
UPDATE U
SET Flag = 1;

Even an inline function or view if you feel so inclined

dbfiddle.uk/…

@PaulWhite The first query uses a self-join (via the subquery) and this is being optimized into a TOP 1, which is the same as the second query

My favorite place to use weird UPDATE syntax is on window functions. The amount of times I've seen self-joins to a CTE with a window function and I've said "did you know you can just update the CTE" and they're like "no way"

Another interesting UPDATE syntax is SET @newValue = Column = SomeNewVal, @oldValue = Column which looks weird as hell when you look at it.

@Charlieface looks ambiguous ;)

@Charlieface I’ve used variable updates like that for processing queue tables.

It’s really handy

Change a column, change a variable, everyone’s happy

@ypercubeᵀᴹ Looks it, but it's not (as long as it's on a single row!) It's guaranteed to set @newValue to the new value and @oldValue to the old value, no matter what order you type them.

@Charlieface I thought that the order of columns did not matter when assigning values with an UPDATE. Unless this is different for variables

@ypercubeᵀᴹ That's exactly what I just said: it doesn't matter whether you put the first line of the SET second.

Unless you mean within one line: @variable = Column = SomeValue is guaranteed to place the new value into the variable, and @variable = Column is guaranteed to place the old value in. Which is pretty confusing.

No, I understand that.

My original comment was about a possible ambiguity when (and if) MS implements boolean datatype. Then - imagine @newValue, Column and SomeNewVal were all boolean. Would

SET @newValue = Column = SomeNewVal

change both @newValue and Column to SomeNewVal? Or act like

SET @newValue = (Column = SomeNewVal)

changing only @newValue to True or False, depending on whether Column and SomeNewVal are equal or not?