@Zikato

Morning

I've found a use for the weirdest TSQL construct I've ever come up with

MERGE Target t USING (VALUES(1)) s(dummy) ON 1=1

Chat markdown triumphs again

Yeah well

Can you think of one?

One what?

A use for that construct?

Um

No, not immediately

MERGE dbo.[Target] AS T
USING (VALUES(1)) AS S (dummy)
    ON 1 = 1
...
;

But I can use chat markdown

And terminators 😀

OK how did you do that?

That's not valid code, you need a WHEN something first

When you're typing the code, one of the orange boxes changes to 'fixed font'. Or you can hit CTRL+K. Or manually indent each line four spaces.

which orange boxes

The 'send' and 'upload' orange boxes

To the immediate right of the box you type text into

Never noticed it before, I feel like you're gaslighting me by mucking around with the chat buttons :-)

Anyway, to answer the question: if you want to conditionally update or delete from a table without scanning it twice, and there is nothing to join to.

Eg dbfiddle.uk/AwgkkXnL

It becomes available as soon as you enter a newline

(shift+enter)

yeah MERGE bugs, Aaron Bertrand, yadda yadda, whatever

@Charlieface Do you see it now?

@PaulWhite My eyes have been opened, my innocence will never be the same...

It's one of the least discoverable things about chat, apparently

Many such cases over the years

WYSINWYG

@Zikato WYDSIWYG

The Formatting Police have been notified

my eyes!

Do you have 5 minutes to knock up a script to fix all that

nah, just delete that

yeah

NULL or NOT NULL should be compulsory on table definitions

To NULL or NOT NULL, that is the question

Especially on the first version. I hate coming back to scripts and trying to figure it out and add it myself

@Charlieface A small improvement: dbfiddle.uk/u-AKWe_o

@PaulWhite Wasn't mine, it was copied from a question

@Charlieface Yeah, I know

That's why the Formatting Police haven't knocked at your door

@PaulWhite Avoids the extra Segement - Sequence Project, clever.

@Charlieface And for curiosity's sake (avoiding MERGE): dbfiddle.uk/QWret-N2

Little bit less efficient, and you get a new ID value but still

sql.kiwi/2020/07/a-bug-with-halloween-protection-and.html

Scares me, I don't want to think about whether that works or not

and yet you're all relaxed and meh about MERGE 😀

Yup

The bugs are predictable see?

Okidoki!

what am I saying, what am I saying, what am I saying

quietly makes quick exit

The product's full of bugs. Amazing anything works, really

Then again, the bug is full of products.

wouldn't mind getting an up-to-date list of unfixed bugs for MERGE I think most of the original ones have been fixed.

I find it hilarious the icon for Table Merge is still blue in SSMS

I tried ANDRY for the third word, but it said it wasn't in the word list 🤨

@Zikato It was QUERY

I know

@Zikato Oh? Did we have this conversation already?

no, I just make memes and know stuff

Last I recall, we just said the guess was very on-topic

I think there less than 5 words like __ERY and the others are not as common (apart from the solution)

@PaulWhite On Stack Overflow it's the custom to spell it "querry" for some reason

Goes with "the bellow code" I think

Thanks, I'll do the needful

I suck at this thing

@PaulWhite Reminds me of joshthecoder.com/2020/09/11/…

@JoshDarnell Indeed. I hadn't noticed the typo in, "As demonstrated in the db<>fiddle link above, the Id column remains nullable after dropping the constraint using T-SQL." last time I read it.

Should be "not nullable"

Missing word rather than a typo, I suppose

Josh darnit.

You're right.

Years-old typos.

I get anxiety about editing old posts. I always have to do a diff on the generated HTML versus what's live because I can never be sure I've never edited the source directly rather than generating everything afresh from the markdown source.

In the short lifetime of my blog, I don't think I've ever edited the generated HTML haha.

That would scare me too.

I don't generally on sql.kiwi but SQLperf has so many customizations and special syntax it's almost inevitable

Even so, it's rare. Which doesn't mean I can remember which ones were rarely edited that way. Hence diff.

Another shortcut that ends up being less efficient than doing it properly in the first place

Yeah, that's smart.

Does anyone here know about google sitemap stuff or SEO that I can bounce some questions off?

@PaulWhite I've done similar things in production with code 😬 Just, you know, not with my personal blog. Because that makes a lot of sense.

@HannahVernon I might be swapping to hosting my own stuff, do you use individual VMs or how do you do your setup?

@JoshDarnell ha ha ha

@SeanGallardy Erik is the SEO master

That stuff is a mystery to me.

I got that alert over the weekend. I don't remember ever seeing that error.

sqlerrorcodes.com/error/17189

For @SeanGallardy

That's a recurring theme with people that have posted about it in the past.

Other things like large file copies competing with SQL Server for memory etc.

I take it you're unable to use lock pages in memory?

@PaulWhite That's good to know.

@PaulWhite Actually, we probably could. Although I need to confirm that max memory is actually set on this server, and whether or not SSRS is also running on the box.

@JoshDarnell Probably an application that is creating new connections and failing to close/dispose the old ones

@JoshDarnell whoa, that's special for sure... anything happening at that time? General system issues?

@Zikato 😂

@JoshDarnell exec sp_blitz @checkserverinfo = 1;

@JoshDarnell I should probably read first :D Yeah, that's a weird NSTATUS - it doesn't have any information in the error code...

@ErikDarling Thanks!

Max memory is not set :/

Feels like something changed in March.

Something's bullying SQL Server for memory I guess.

"This is clearly a SQL Server problem and product bug", right Josh?

just add memory

@SeanGallardy Of course! It's too bad I don't have a support contract, or I would totally open a troll ticket.

@JoshDarnell 😂😂😂

my rates are reasonable

@SeanGallardy I did that stuff well over a decade ago. Might know some generic answers, but I'm sure my knowledge is dated.

is there any way to see your reputation as a graph over time?

dba.stackexchange.com/users/32281/erik-darling?tab=reputation

that shows per-day, but i'm looking to see how long it took me to get to various markers

@ErikDarling stackexchange.com/users/3794805/sp-blitzerik?tab=reputation

thank you

No problem.

@ErikDarling You wouldn't want to work on these servers 😅

@JoshDarnell stackexchange.com/users/current?tab=reputation

@Zikato showoff!

I just read the transcript

@SeanGallardy NSTATUS? or NTSTATUS?

@JoshDarnell if there are invoices involved i will work on any server(s)

He will, you know

@ErikDarling 8 GB of RAM, 2 cores, Standard Edition, 100% ORM queries, hosted in cloud VM.

If I could get that thing in an AG, we could have all the worst things at once.

Sounds like a rich source of invoices

Does it run Service Broker

@PaulWhite typo for sure, ntstatus

But who gives a SQL Server 8GB

@SeanGallardy 0xc0000000 is the least helpful thing I've ever seen

> That's an error

That's what I'm saying, it has no facility or error code bits set

Exactly

It's an error

Not even an unspecified error

Just an error

yup

> Something bad happened

Might as well log that

Mind you, if things are bad enough, there's nothing to do to get more context or return anything helpful. A bad thing happened, but goodness only knows what. The world is on fire.

Like when there's a stack dump but the dump can't be written so all you know is that a dump has dumped dumping

Yeah

The sort of thing where code debug prints, "This shouldn't happen"

Or, "We should never end up here"

Or, "Just give up at this point - it's hopeless"

"this error is an error"

That's all we know

Deal With It

sunglasses

that was really going well there for a while

databases are over

Annoying gif: i.kym-cdn.com/photos/images/newsfeed/000/052/812/…

this guy

giffing up chat

fixed in a jiffy

it's pronounced giffy

geeze

i bet you use tabs too

🥷💥💨😶‍🌫️

@JoshDarnell And this is how you remind me?

⬜⬜⬜⬜⬜

> White Large Square was approved as part of Unicode 5.1 in 2008 and added to Emoji 1.0 in 2015.

▲►▼◄

▲▲▼▼◄►◄►🅱🅰

@Zikato i guess it's a nickleback day now

oh god

*canonical spelling s/b "jod"

this juy...

@SeanGallardy I use VMs in HyperV. I bought an awesome used server with 12 SAS hard disks from ServerMonkey for it.

The physical switch is a 16 port with support for vlans, which is how I have different subnets for my Distributed AG

Of course, you could just do that with HyperV networks

I'm happy to share more details if needed.

@Zikato Well played.

@JoshDarnell we have LPIM set and are running SSRS on the same box - I generally leave plenty of room above max server memory for SSRS, but also I setup LPIM to prevent SSRS from stealing SQL Server's memory. For a box with 64GB of memory, I have max server memory set to 48GB, which leaves 16 GB for both SSRS and the OS. So far, so good.

@HannahVernon Awesome, I'm looking to do something similar. Unsure of the networking side for hosting different sites w/same IP/port combo.

@SeanGallardy by sites, do you mean websites?

Yup

interesting

so I have a couple of software routers running in VMs that allow me to have one side of the router on my 192.168.0.0/24 network, and the other side of each router can have 192.168.1.0/24 with different hosts running on the same IP and port.

then my internet facing router has NAT that sorts out which private network is served onto the internet

NAT is powerful

my software routers run Mikrotik

> This question does not appear to belong here. Either it's not database-related or it otherwise conflicts with the scope of our site. See What topics can I ask about here?, What types of questions should I avoid asking? or this blog post for more info.

@HannahVernon Full-cone NAT is the term

with a flake stuck in it, hopefully

@HannahVernon Yes but it's also messy, especially when double-ended protocols such as FTP or RTP are involved. Favour routing and firewalling if you can, and use IPv6 if you don't have enough public IPs

^^^ full cone

:-)

with a flake

@Charlieface I don't have enough traffic to warrant caring about IPv6, but at some point I suppose I could figure it out. Per the rest of the world though, its hard.

@HannahVernon Oh you and your 64 GB server

@JoshDarnell oh, my two have 128GB. That 64gb one is at work, lol.

@JoshDarnell I bet that's a rocket ship of a server hey

@HannahVernon Yeah, but those are Canadian gigs.

@SeanGallardy Are you hosting the sites with IIS? Or intending to?

@JoshDarnell just curious, why 2 cores and not 4?...aren't licensing costs the same (assuming you're not doing CAL)?

@JoshDarnell I honestly don't know. 5GB fiber is available (I also have 1 GB and 2GB options) for pretty cheap ($70 for 1GB and $150 for 5 GB) which is symmetric, no cap. I was thinking of bringing my hosting internally because I don't get a ton of traffic (I'm no darling kiwi) and I could configure it much better (imo).

I already use Hyper-V extensively, so I'm very comfortable with that. I'm not sure how to setup domain resolution where multiple sites come to the same external IP but go to different VMs, internally, since I would assume they would all resolve to the same port and my external static address.

I'm ok with using Apache or IIS, IIS does integrate better with Visual Studio which I do sort of know (not great though) and C# - but I'm pretty new to things like Blazor. It would allow me to use SQL Express which is nice and I could run more local service automation.

I'm equally comfortable with MySQL and PHP as well, though.

I'm looking at building my own NAS solution in an ITX or MicroATX format anyway, so I figured get a cpu that can handle some light VM work and make a storage spaces array for some JBOD for long term backups then keep the VMs on some 2-4TB nvme sticks I am not currently using at the moment so that I have speed and a place to compress backups quickly.

@SeanGallardy you could use a reverse proxy in IIS to serve multiple sites from different VMs via a single static ip address.

See this

Hmmm

@JoshDarnell true.