Many of the better ones are on by default or not needed in 2016.

@TomV I don't get it? I may be out of the loop

jeagl

@PaulWhite It was difficult not to feel vindicated after the 2016 changes

@JoeObbish Absolutely.

Mr handle loves him a trace flag.

And fair enough too.

@PaulWhite yep, already emailed him about TF 253 to see if he tested it already

@AaronBertrand that's good material, right there.

@TomV one of the best shows ever made

@PaulWhite I have trouble remembering the 2016 SP1 use hint syntax

the strings are all so long

much easier to remember four numbers imo

@JoeObbish I cheat. SQL Prompt pops them up.

But yeah four numbers feels more geek.

You've thought about a database per client I suppose.

With views/synonyms etc.

@PaulWhite Is the idea that the additional databases would contain no data

but that cached plans are database specific?

Yes. That's the idea.

FWIW I ran through the sample here mssqltips.com/sqlservertip/4004/… and added 4,000,000 accounts associated with rep 2. No matter which rep executes the query, estimates are always 4MM, even though one has an actual of 3. Even after adding an index on dbo.Accounts(RepID) INCLUDE(AnnualFees) which should have been able to coerce the optimizer to perform a seek for the smaller case (and it didn't matter which one I ran first).

I hadn't thought of that idea, partially because it seems like a bit of an extreme departure. thinking about it now

I think the security predicate is simply bound way too late to have an impact on estimates. OPTION (RECOMPILE) didn't help.

@AaronBertrand Thanks Aaron, that's very helpful. The impression that I got about row level security (which may not be fair) is that you can get okay performance with querying a single table

but when you start joining a bunch of tables together I don't see how it could work out well

It's possible. Again for brevity and laziness I used the example I used there

@JoeObbish It's a tricky requirement; I think all your options are going to be a bit of a departure, in one way or another.

I would like to see the comment on the 8k literal in the view :)

@PaulWhite I found the code but don't think there's a comment.

ever write code after which you aren't sure if you should be proud or ashamed?

pastebin.com/VBbXtPHn

@JoeObbish Oh yes.

You should see some of the nonsense Adam M and I wrote together.

@JoeObbish Frame it and hang it on your wall :)

@PaulWhite I'm sad I missed the falling of the chair

@Lamak Well that's what you get for not being in The Heap 24/7/365.25

@PaulWhite Can't help but ask this. Is it obvious to you why I wrote it that way?

there's probably a better way of doing it, but well...

@JoeObbish No it's not. But it is late/early here and I'm off soon. I left the tab open for later. And Aaron's article too.

I may not be alone in that.

@PaulWhite that one has an expiration time of an hour but I can repost

@Lamak You can watch the rerun on America's funniest home videos

@JoeObbish yeah, but it's not my fault, I had to travel :/. You should try falling when everyone is here

have a good evening

@Lamak I could fall right now if you like?

@JoeObbish It's 8:30 am :)

@TomV I think you meant "Australia's funniest videos"

@Lamak I was thinking about a funny followup but that'll do

Don't make my last action of the day a kick-mute.

have a good morning then

Ta.

@JoeObbish that would be awesome

@PaulWhite kick-the-chair then

@JoeObbish Ok so you have me curious. As a first guess I would say it's for the start-up filter. Is there more to it?

@PaulWhite wanted something that wouldn't affect cardinality estimates

so adding 1 more row at most

the other parts

needed to avoid constant folding

Ok.

and wanted to be sure that simple parameterization didn't happen

@AaronBertrand - I fixed my "login" mistake. :-)

@MaxVernon ha ha though honestly that was not for you. Just a very common swap

@AaronBertrand I figured. It's a subtle difference, but nonetheless an important one.

How does OPENQUERY work?

Does the code inside the OPENQUERY has to be compliant with the syntax of the remote DBMS?

@ypercubeᵀᴹ It would have to be, right?

@JoeObbish I guess so. I'm not sure, that's why I ask.

it would have to be OleDB compliant, and match the capabilities of the linked server.

@ypercubeᵀᴹ Out of curiosity, is the code in that question not compliant with postgresql syntax?

It is

I asked beacuse I want to suggest he moves the whole code inside the call.

So the brackets for example would have to be removed

@MaxVernon you also need to perform a sacrifice before

@Lamak and any number of other things the Linked Server makes you do; and of course, it's not going to actually tell you ahead of time.

of course not, if it did, it would be helpful

:-D

@ypercubeᵀᴹ I think if he has QUOTED_IDENTIFIER turned on replacing with double quotes should be no issue

I had similar thoughts on the question but passed because it required guessing in its current state

@JoeObbish It's the only answer so it must be correct ..

@JackDouglas have you changed dbfiddle output result?

@JackDouglas previous format allow to easily copy and paste the result to the answers.

MS Access's need to put parentheses around everything it possibly can is totally retarded. Sheesh.

MS Access is evil

This is just an absolute fact, is all, given how screwed our databases in Access are.

@ThomasWard Someone at worked forwarded me a query from Access, and I can't even see it without spending 10 minutes formatting it.

@ErikE SQL the heck out of it. Just pure raw SQL.

Because somehow that crap works xD

(I still hate Access with a passion though)

@ThomasWard You can see that I care about formatting.

fun fact I don't like either of those for accessing databases

DataGrip is adequately fast for what I need

or I'm in Python using direct SQL interaction with the DB server :P

sorry, i'm partial to third-party things :P

if only SQL could make me coffee. I could use some to make it through this lecture...

@ThomasWard I don't have much experience with Oracle. Just was what I thought of when trying to remember a long query I've written.

heheh

I know what those're like

though I once ended up with a short query that had to do a statistical calculation on one column in a 3-column table, but had over two million records that took 20 minutes.

Until indexes :p

(thank you to @ypercubeᵀᴹ and others for that tidbit and helping get that working several months ago)

You know you're in the wrong job when you are forced to install SMSS on a Linux system just so someone can access SQL Server management stuffs