Morning

Morning

Morning

Am I the only one who thinks it's perfectly fine for apps to send SQL statements to the database and not have to wrap every single little CRUD operation in a stored procecdure?

it's fine either way I suppose

I think it's a hangover from the ancient days when SQL Server didn't cache plans for ad-hoc batches. If you wanted plan reuse, you had to use a procedure.

I guess there's an argument against not allowing arbitrary SQL from outside the database

Having a procedure also means you can change the implementation behind the interface without breaking (or recompiling) applications.

But as the cube said, it can be fine either way.

@Colin'tHart As a programmer I'd rather use stored procedures. You can solve some situations within the SP w/o change your code. About granularity, well it depends. It's opinion-based to me.

Oh, it's just what Paul said.

I would tend to lean towards SPs as well, by instinct.

It's a valid question, but one that I would have expected see on Programmers rather than here perhaps.

Sorry it changed its name and I always forget:

The old URL, with "programmers", still works, just redirects to the new one.

I wonder if it resolves as a shortcut...

Software Engineering – does it?

Apparently it does, but also substitutes "softwareengineering" for "programmers" before resolving into full URL

Same when posting https://programmers.stackexchange.com/ on its own

@PaulWhite yes, a good point.

Decoupling applications from the database implementation.

right

@Colin'tHart Stored procedures are now a philosophy?

Allowing the application to arbitrarily issue insert/update/delete statements goes against open architecture standards and sort of defeats the purpose of the database. Could you use a middle tier instead of stored procedures and achieve the same thing? Sure, but stored procedures have other benefits you wouldn't have to rework into that tier.

What standard is this exactly that forbids issuing SQL statements from an application?

Want me to go back to Codd or dig up a NIST document?

I can also list off a lot of reasons from personal experience why you would want to follow that standard as well.

A citation would be nice, yes.

Tying to dig it up in Codd - he tends to make the really important points one or two sentences long then people are still oblivious 40 years later.

Was there a syntax in SQL Server to specify that a newly added non-nullable column should be populated with a given value, without having to create a default for it?

I thought there was but can't seem to find it in the manual

I mean, at the time of declaration. Something like ... ADD MyNewColumn int NOT NULL POPULATE AS ..., the POPULATE AS part being something I'm not sure about

I mean the keywords would probably be different if the syntax for that exists all

@AndriyM I don't think so? I always had to set a DEFAULT then drop that from the column definition later.

It's been a while

Yes, that's what I've always been doing as well. I just thought I remembered seeing it somewhere mentioned that in newer (2012+? not sure) versions new syntax was introduced that would allow you to do this in a simpler way

Must have confused that with something

Thanks @bbaird

@mustaccio Three Tier Architecture is what I was looking for - I think it was codified in an IEEE paper but I can't find it.

@mustaccio I'd also add the disregard of a data layer can and does end up in folly - at a prior employer, four applications could access/update central DB2 database - 2 sales, 2 service, one internet only. This is fine. HOWEVER, they each had free reign of the database which resulted in the data becoming inconsistent. In a highly regulated industry that was frowned upon. Spent 6 months of my life identifying customers impacted, issuing refunds/data fixes.

That was just for my area of expertise, it was a 2+ year effort enterprise wise.

Three-tier architecture does not in itself prevent or forbid "Allowing the application to arbitrarily issue insert/update/delete statements"; what you're describing is simply incorrectly set up authorizations and missing constraints (in-database or otherwise)

Three-tier architecture is about separating presentation from business logic; it doesn't tell you to wrap every SQL statement in a stored proc

and you can wreak as much havoc with your SPs

@mustaccio I mean, it goes against the whole concept of a data layer to have the application directly interact with the database

But I guess one can make any justifications for doing things that are risky

And it's not stored procedures per se, rather the running of unvetted code or allowing the same process to be performed differently depending on the caller.

"data layer" is yet another one of those concepts that everyone and their uncle defines at it suits them

If you're daft and ignore the impetus behind it, yes.

Like, "modify the database directly from the application" would imply there is no data layer.

Like, yes, people can ignore whatever they want but it ends up with real world implications and one ignores things at their own peril.

Excellent argument. "If you don't see the world I see it, you're daft and ignorant". Thank you for the enlightening discussion.

Lol

I was speaking to "everyone and their uncle" but thanks for not being self aware

I think you want a very specific outcome from this and maybe haven't considered that I really didn't want a epistemological debate.

Impersonal you, or generic you, something that my native language has too but nevertheless something that still makes me stumble sometimes when I come across it in English.

@AndriyM No I don't think so. Using WITH VALUES requires a default constraint.

@bbaird I'm offended on behalf of the uncle

@mustaccio Fair, he's really just caught up on the periphery of this - blame his brother and/or sister's kids.

An argument for procedure based database access can be made for long lived code bases. "We think we can get rid of column X but we don't know that we can" If the domain of where that code could live is just the database, that makes a much smaller footprint to search.

Whereas with the client I'm thinking of, maybe it's Java, maybe it's cobol, or linked servers, and oh and we gave access to the guy in purchasing so we see Access/Excel querying data

Or we see things like none of the regularly scheduled cobol jobs access this field so we can smoke it. Except, we just sold this special deal to Very Important Client for their Easter program which causes the whatever to pull that inactive job out of the archive scheduler and now column NeverPopulated is actually populated and used

Sadly, not a hypothetical there. Anyways, if there was a consistent interface to working with the data, it would make my consultant life easier. Or at least, more repeatable

Anyone want to throw me a pity +1 so they can't delete the question stackoverflow.com/questions/65126044/…

Ack Ristley at his best.

@billinkc done

@billinkc I'm not sure there's a good solution to the Very Important Client Easter Program Problem yet, at least not that I'm aware of.

@PaulWhite Ah, WITH VALUES, yes, and I probably got that backwards. It was to have the default constraint populate the column without having to define the column as NOT NULL.

It seems to be coming back to me

Thanks

good

Can I post this every time I read someone's question three times and still have no idea what they're asking?