is unanchored-like really that much different from like ? At the end of the day, is that really necessary... I know we had discussions on here with Evan about it, I'm just not sure I see the utility in having such subtle variations that are unlikely to be known by the OP.

maybe I could understand like-unanchored and like-left-anchored etc... since the "like" part comes first, and will be displayed to the OP as they type in "like"

@PaulWhite Nice edit, thanks for that

@sp_BlitzErik Where's my downvote?

link

At the very least I was expecting snarky comments

I pinged you last night

You wouldn't ignore my ping, would you?

tl;dr

(too late; didn't read)

I think that a scalar UDF is a wonderful solution

oh wait, i did see that

eat it, obbish

dynamic SQL. My Friend.

@sp_BlitzErik right

but the guy is claiming that it has to be a view

and he can't do anything with local variables or dynamic SQL or anything

which is why I said that he needs to change his framework

> 2017

> believing OP

also you didn't ping me

> mfw

you don't believe the EF guys?

Innocent, downtrodden question asker: "I'm using the EF and it sucks"

Erik: "No you aren't!"

"i'm using EF and i suck at it" would be more believable

not to absolve EF of its problems, but...

You could be right

I wouldn't touch it with ten foot pole

You could be right

i won't either, but it's easy to tell the difference between people who do and don't suck at it

@sp_BlitzErik I suck at EF but it usually works well for me. It's pretty easy to beat into submission.

just what i'd want to hear from a developer

There's this Russian guy who uses it and asks pretty good questions

I write T-SQL directly. Ain't lettin' no stinkin' code generator at my database. But that's just me.

Can't remember a positive impression of anyone else

@MaxVernon Preach!

@MaxVernon Amen, reverend

@MaxVernon I mostly use EF because I'm lazy, for doing anything complex I try to write SQL as directly as possible.

I'll write my own dumpster-fire code, I don't need an ORM to do that for me

@KrisGruttemeyer YES!

EF is fine up to a point. i don't appreciate hardline stances on "always EF" or "always stored procs".

of course, SQL Server any decent DBMS rewrites whatever you throw at it anyway, so there is that subtle point.

@sp_BlitzErik EF does pretty good at generating tables and basic IO with SQL.

i'm not sure i trust people who suck at EF to write sql that's any better

I'm working with a client that uses nHibernate almost exclusively. EF, IMO, is far superior if you're going to go with an ORM.

Especially the newer versions

@EBrown is a perfect example

The thing that bothers me is how it writes queries at times, but as @MaxVernon points out SQL Server also knows what it's doing.

@sp_BlitzErik My SQL doesn't suck entirely, FYI.

query hints exist for a reason

any framework that doesn't support them is stupid

And my problems with EF are more specific.

especially because it shouldn't even be that hard

add an OPTION clause to the end of the query

why is that so difficult?

Like it using datetime instead of datetime2, and using a NVARCHAR(128) to store GUID PK's in.

at least it's not using an nvarchar(max)

@JoeObbish Entity Framework is supposed to be broad and support any underlying DB, not all of them have query hints (I assume).

@MaxVernon No, thank god.

that's even worse

now I hate it even more

it should support hints that the database supports

that's daffy

imagine if I actually had to use it

@sp_BlitzErik It doesn't know what the DB supports.

so then the query fails

or do people using EF not test their code?

@JoeObbish you'd have your own bakery

@JoeObbish No, because EF doesn't write the query.

wtf does this thing do

what writes the query?

@JoeObbish Sends it to a driver that writes the query.

The dev team here is pushing to use EF because it manages the database "stuff" for them. Apparently, they don't like waiting for DBAs to approve DDL changes.

it doesn't have to know what the the db supports, just the mechanism to supply the hint

so what? you can still test that

if you run your query against the RDBMS and it fails then you know that the hint isn't valid

@JoeObbish I'm not saying it shouldn't support them, just telling you why it doesn't.

maybe the better argument is that hints can be done differently

in SQL Server you just need a code block at the end

won't work for Oracle

@JoeObbish compile-time options should allow exactly that kind of thing to just work

EF has several "layers", and most people only actually get involved with the top-most layer (the object mapper).

If you know how to use ORMs and understand how to work them so they perform more efficiently, more power to you. I just, IMO, think it's easier to write good old-fashioned, fun loving T-SQL

The interesting thing is that the layer that writes the queries is specific to a DBMS.

@KrisGruttemeyer 100%

how does the EF do string concat? does anyone know?

@JoeObbish carefully

someone wrote a driver to translate the output to SQL Server code?

@KrisGruttemeyer It sometimes is, but when you have to write a CREATE TABLE for 100 tables, and then write the object to map to the table, then the commands to go in and out of the table, it gets exhausting.

@JoeObbish In what context?

and that driver does different stuff depending on the rdbms version?

@JoeObbish Yeah.

@JoeObbish Yes.

There's an EF SQL Driver, an EF MySQL Driver, etc.

so hints could be supported then

Not on the broader level.

Because the part you interact with as a dev is the part that is server architecture agnostic.

But developers would still want to test their code against a live database?

Maybe I'm missing something obvious because I've never used it

You can pretty easily change the database, it's just a regular connection string.

what I'm talking about is say I add a FAST 1 hint to my query through the top level

and I test that query against sql server and it works

if I add a GOTTA_GO_FAST query hint that fails when I test it

you don't get access to the query like that.

@JoeObbish There's no interface for that in EF.

if you want to do stuff like that, you need to use plan guides

dies

@sp_BlitzErik an angel just lost it's wings.

For EF it's usually something like databaseContext.ObjectTable.Find(pkValue) which will issue a SELECT * FROM ObjectTable WHERE KeyColumn = pkValue command.

why can't they add that?

So as a dev you don't ever get the actual SQL being applied.

> mfw

@EBrown and ef converts that into Klingon before sending it to SQL Server.

EF could certainly benefit from a query design tool that's not just pointing arrows around

I'm going to stop asking questions so I also don't die

thanks for answering all of my dumb questions

So when I say context.People.Where(x => x.Name == 'Joe Obbish'), that issues a SELECT * FROM People WHERE Name = 'Joe Obbish' command (to an extent, it's actually rewritten pretty shittily) on the server and returns all my results.

But it doesn't actually do that immediately, first it forms the result set, then it issues the query and gathers them when you request them.

@EBrown if you have just a single table involved, and there is never just a single table involved.

@MaxVernon If you JOIN then god help us all.

[Extent1]

@EBrown that's what I'm talking about

@sp_BlitzErik Yep, then [Extent1].[Column1], [Extent1].[Column2].

@MaxVernon You can actually write RAW SQL queries in EF with some trickery.

@sp_BlitzErik Beat it back with a stick!

@EBrown that's what they make SQLDataReader for.

Out of curiosity, can't you call SPs from EF?

@KrisGruttemeyer Yes, with more trickery.

@KrisGruttemeyer yes, and that's the way you should do it.

TIL.....

@MaxVernon You can do it natively without needing to reference that stuff specifically.

How is this easier than writing SQL?

@MaxVernon Seems to be the best of both worlds, get the reusability and portability of SPs but the developers still get their handy-dandy entity....stuff

@JoeObbish ahhh the million dollar question

SQL is easy to write for these types of queries

oh man, SELECT * FROM TABLE

@JoeObbish How do you expect to translate the SQL to a domain object?

better abstract that

I don't?

it gets even better

brentozar.com/archive/2016/09/…

brentozar.com/archive/2017/05/…

One of the most frequent complaints that I hear when presenting to DBAs about Entity Framework is that “developers should be endlessly tortured for using it”.

aren't they already being tortured?

sounds like no action necessary there

Let's say you just have People and Addresses, a basic SQL query might be SELECT * FROM People INNER JOIN Addresses ON People.AddressId = Addresses.Id, right?

@sp_BlitzErik #1 issue for me. EF is 'SELECT ALL THE THINGS!', when it only actually needs one thing..... Or nothing at all

@KrisGruttemeyer That's more a problem with the developers using EF than EF itself.

isn't it weird about it not letting you choose between UNION and UNION ALL, too?

i seem to recall seeing a bunch of EF queries that were like DISTINCT on top of UNION and whatnot

maybe even a GROUP BY

it was all unpleasant

all three

just to be safe

@EBrown I was under the impression that selecting only a few columns was only available in newer versions (like 5+)? Or has that ability been present throughout?

LOOK THIS HAS TO BE REALLY DISTINCT

TAKE NO CHANCES

@KrisGruttemeyer I have no idea, I use it when necessary and it's always worked.

I see the attraction to all ORMs in that they insulate you from needing to know about the actual data. For some devs (present company excluded, of course!) needing to know about that pesky old data is far too time consuming. And T-SQL is notoriously difficult to re-use in your code. Plus if they change something in the code, they also have to change the database, and that is such a drag.

"Take the time to gain a deeper understanding of the ORM’s inner workings and you’ll feel much less hate and a bit more love. There’s very little not to love remaining in the big name ORMs like Entity Framework. EF has been around for many years now and if you’re still having problems with it, it’s likely your own fault, not the ORM’s."

at that point why not just learn SQL?

Changing the model in Visual Studio should just make the change in SQL Server on your behalf. That's what they believe anyway.

@MaxVernon That what it's boiled down for me. Entity Framework allows me to get really far in a project without needing to redo the entire thing three times just to get data to and from the database.

@JoeObbish And circle gets the square...

really though

@EBrown don't you think that the amount of abstraction involved contributes heavily to a lot of your misconceptions about databases, though? at some point that's going to be harmful.

if it's that complicated

maybe we need another abstraction layer on top of it

EFF??

@JoeObbish that's what nosql is for

@sp_BlitzErik damn straight

@sp_BlitzErik It's possible, I try to learn more about the database stuff but it doesn't help when I get sarcastic responses. ;)

@EBrown ooops we let out the DBAs again.

I've very rarely had an honest-to-god performance problem because of what EF did with the query. And to be honest, even then it probably wasn't EF's fault.

@EBrown i can read BOL to you if you dislike my ambience~

@EBrown I'd venture to guess the problem just hasn't surfaced yet.

Usually my performance issues are related to things that I did wrong (like join across 8-10 unrelated tables for a piece of data).

@EBrown Sarcasm is all that we have left at this point. Without that, we are nothing.

@sp_BlitzErik BOL? I don't particularly care, I'm just poking fun. ;)

@EBrown this doesn't necessarily mean you did something wrong though

@Lamak I agree, but it's easier to fix it on my end than to try to figure out what EF is doing (though I can still see the raw queries) and guess at how to fix it.

There have only been a couple times that EF actually did something really bad that I had to fix, but that was the datetime (instead of datetime2) and nvarchar(128) (instead of uniqueidentifier) stuff.

@EBrown I first read that as "try to figure out what the EFF..."

sounded appropriate

Usually is.

@sp_BlitzErik - relevant to previous convo syracuse.com/politics/index.ssf/2017/08/…

@KrisGruttemeyer hahaha. i got everyone in the family on it. amex will cover the cost of enrollment every 5 years.

@sp_BlitzErik I used my United miles for me and the wife to get it. Seriously, best thing ever. Syracuse, though, doesn't always have it open. It's more for when I fly back from FLL, ORD or EWR.

ah, yeah, the boonies

we're either jfk or lga for everything

tried newark once and it was awful

EWR is a swirling vortex that light itself cannot escape

the jet blue terminal there is so depressing

people fight over a seat at that mexican cafeteria

@MaxVernon dba.stackexchange.com/questions/64535/…

comment on question or the answer above it?

@sp_BlitzErik I mean, is the food good? I personally don't want to eat anything 'volatile' before boarding a small, pressurized tube with 100 strangers

@jcolebrand checking now

@jcolebrand I saw this part of the answer > "Is it the same for SQLNet message from DBLink..ie a session that came into the DB via a DBLink is now quiet and DB is waiting for another request from that session?" and thought "that looks like it should be a comment, not an answer". Is that what you're asking?

@KrisGruttemeyer I eat "volatile" stuff because I'm getting on a plane.

@MaxVernon Well, that's a different perspective. HA! I have no desire to get into that teeny tiny bathroom with a case of the skitters.

People will know, you will be stared at.

@KrisGruttemeyer who said anything about a bathroom?

I'm talking about getting some room around your seat

;-)

@MaxVernon And that's why they pay you the big bucks. You innovator, you.

I fly out next Thursday, I should do a vlog on the flight after destroying some awful mexican food.

@KrisGruttemeyer don't eat when I travel, cuts into booze I can expense

@sp_BlitzErik Also a very good point. I have to drive once I land in FLL so I can't get too sauced. Now, at the hotel, TOTALLY different story

Wake up in bed next to the minibar

@sp_BlitzErik Bathroom. Closer to toilet. That's important.

@MaxVernon yes, but point the comment at the answer above it, or at the question

because I'm probably mildly distracted, but agree that it looks like a comment

@jcolebrand I must be dense - I'm not understanding you. Is that a directive or a question to me?

There's too many things there that say dblink

do you think

that the answer you commented

should be a comment

on the parent question

so I can move it using mod powers (they're like genie powers, but for monkeys)

ahhhh.. I got you. I'd say it should probably be a comment on the question itself, in my opinion.

Yah

It's just the 17800 occurrences of dblink in that post

combined with my friday-itis (I'm leaving the office in 20 minutes and going away for a long weekend)

no kidding. very confusing.

combined with having 120 conversations at work this morning

@jcolebrand have a great one!

Thanks :D

I also feel like the 0 point answer on that Q is a comment as well ...

It doesn't read like "do a thing"

@sp_BlitzErik important not to wake up from a nightmare and bashing your head

@jcolebrand agreed. I saw the answer/comment in the "first time" queue.

but, yes, the 2nd "answer" should likely also be a comment.

on the question.

I'm gonna leave it as an answer for now because of this line: Check if a data replication using materialized view

If you guys want it converted (discuss amongst yourselves please) just flag it for @PaulWhite to deal with later :p

But anyways... I'm gonna go potter off and find something to do for the next half hour because Fridayitis

@jcolebrand cool

@Lamak that's usually how I fall asleep, not wake up

@sp_BlitzErik can't find the comment I was trying to reference

I am now 7/10 the way to a gold tag badge!

Is it about the first table Dear Leader lost a fight with?

@sp_BlitzErik nice way of putting it

and yes, it is

Rolando has a gold tag badge in mysqldump.

That's really impressive on this site.

yeah, it is

@EvanCarroll if you were a man you'd be 10/10 of the way there

Smash the patriarchy. Download your copy for free today, postgresql.org/download Women welcome!

i have a copy that i run in a vm when i want to forget what query plans look like

@sp_BlitzErik we don't actually forget what query plans look like, we just don't require absurd pictures to figure it out.

PostgreSQL for when you need a database, and not a dynamic flow chart generator.

i get it, no true scotsman yada yada yada

All in good fun, like all games I win at. =P

my favorite game is market share

let's play that one

My cell phone literally runs on a more secure and now, popular, operating system than your server. So I suppose the game is now just the database?

i don't care at all about windows

or OS in general

it's hair color

Just the version of regedit.exe that they market to the corporate world?

i suppose

and again, i agree with you 100% that postgres is more developer friendly. worked with it a few times and loved it, but there are places it lags behind sql server.

brentozar.com/archive/2015/10/…

i suppose i could take BRIN indexes off that list since columnstore got a whole lot less sucky, but eh

@sp_BlitzErik materializing CTEs in Oracle is definitely a double-edged sword

I'm sure there is someone who can write a similar list for PostgreSQL (things we should have that SQL Server has). I just don't know of any.

Perhaps, you could write the list?

it wouldn't have an audience on our blog, tbh

anytime i talk about a different platform it's a ghost town, hahaha

i wrote a bunch of oracle posts and no one gave a care

I care!

and you got 53 comments

hm

HM

wut

that's some politician caring

you blogged about it two years ago

that's all posts

I wouldn't want comments on something that I wrote two years ago

ever

you left me one comment

that said "NULL"

you have like 5 blog posts

10

i've linked to 3-4 of them!

and your stack questions before that!

guess I wrote high quality posts

I linked to your author page once

golly gee

I'm new at this blogging stuff

that must have been the week we had to turn down sales calls :P

I've only linked to Pablo Blanco twice

part of the issue is I don't do research before blogging stuff

I want to see what I can discover on my own

I didn't even have RSS set up until last weekend

research is a +/-

it helps avoid writing a near-duplicate post, and it can help you take something in a different direction, or expand on particular points if you find your answer out there

but it can also dampen curiosity, and who knows, you might have found something different/new

anyway, i have to take my wife and kid who i support on sql server money to buy books before we go on a vacation that sql server paid for because it's not a real database

:D

TTFN~

have fun in alaska