Morning all

Morning

Morning distinct

@AndriyM jó reggelt <- now that's distinct

@dezso That IS DISTINCT FROM anything I've ever heard about mornings.

@TomV it's equally hilarious and horrifying :p

@McNets glad you like it :)

@JackDouglas you know, I use to zoom web pages (yes, just to avoid glasses), and this new feature is really useful.

morning

hummmm, well morning then

Anyone here a fan of the APPLY operator?

@JoeObbish Define "fan". I do like it, anyway.

@AndriyM Good question. Here I suppose "fan" means "using it to avoid repeating yourself". So just calculating scalars without a table expression.

I used to be a fan of that

@JoeObbish I think I still am.

Am I about to hear something that will stop my being its fan?

@AndriyM Maybe but not necessarily. I tend to be pretty strict about these things.

So when using APPLY for that I usually see a nested loop join. Previously I would have described it as a trivial join that didn't really mean much in terms of overall performance.

But what if you're using CCIs in the query? Nested loop joins can't be implemented in batch mode. You end up with adapters that change the query execution from batch mode to row mode (and possibly back).

Also the work that you're doing in the APPLY statement will be done in row mode, I think

Paul has said that switches from batch to row mode can have big performance consequences in some queries. I haven't observed that myself yet, but I don't like taking any chances when I can help it.

So APPLY for that purpose is no longer in my bag of tricks :(

Answer 2 on this stackoverflow.com/questions/10901299/… is dangerous.

@JoeObbish Not convinced, but only because I know too little about this "batch mode vs row mode" kind of thing, and we don't use CCIs at all :)

Thanks for sharing that, though

@JamesAnderson Answer 2 when sorted by what exactly?

Some queries will experience performance benefits even without proper CCIs. But you need to hack in it. But yeah, if not using CCIs no need to be concerned.

Well, other than the occasional developer-unfriendliness of query execution performance, but that's not news to anyone.

@AndriyM Sorry, the 2nd highest in votes

Shouldn't the last step be ALTER TABLE [TableName] WITH CHECK CHECK CONSTRAINT ALL;? Anyway, the approach does look potentially dangerous. I wouldn't try it in a highly concurrent system.

@JoeObbish Sure.

@JoeObbish Depends how you write it. If the SQL is a simple project it is always OK in my experience. CROSS APPLY syntax is very useful to simplify a query with many repetitions, deeply nested expressions, etc. Unnested apply is done during simplification, before trivial plan even, and it doesn't have limits, so successful unnesting should have no impact on the final plan quality.

e.g. A T-SQL Tip: Working calculations

@PaulWhite Any thoughts on the row mode switch caused by it?

@JoeObbish If it is more complex than a simple project, and ends up being only implementable with loops join then sure, row mode it is.

@PaulWhite I feel like I'm not grasping something. In one example the APPLY looked like this:

@JoeObbish That's an OUTER APPLY.

That was implemented as a nested loop join and the query switched from batch mode to row mode. That's a bad thing, right?

Sure that's a bad thing. Try CROSS APPLY.

will do

oh, wow

with CROSS APPLY it stays in batch mode

I need to read your article on APPLY more carefully I guess

thanks

CROSS APPLY is easy for simplification to rewrite "inline".

@PaulWhite sure, everything's easy for you

@JoeObbish The link above was written by my friend Rob Farley. He's Australian, but I have a long-term project to reform him.

@Lamak I so wish that were true.

ok, now I have a really stupid issue

I have tried googling but I failed

the "query" menu disappeared in SSMS

imgur.com/REViOoh

tried restarting

tried looking in options

I really need it back :(

Anyone have any ideas?

Do you have any query windows open?

@PaulWhite ;)

I feel so stupid right now

Lamak please make fun of me

I deserve it

I was just about to do that, but I took pity

show no mercy

What an amateur!

@JoeObbish but anything I say would just pale against that noobishnes

what ended up happening was I clicked somewhere unexpectedly

and both the query window and the query menu option disappeared

and with all of the SSMS issues I have I figured I triggered some stupid customization that I had to now undo

PEBCAK

Thank you Forrest

@JoeObbish :D

@Lamak So in other words you're not up to the task

(I'm sure that I can turn this around)

@JoeObbish basically :)

i really like the question about using TABLOCKX

That's not about a TABLOCKX but about some new lock they haven't yet thought of a name for. So far they are calling it a TBA lock.

Dad joke?

@PaulWhite Did Christmas come early this year?