Personally I don't understand why people write stored procedures that only perform CRUD. For me SQL is the interface to the database. Just write an update statement with placeholders for the column values and send that to the database -- along with the bound variables.
Someone will argue about SQ...
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?
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.
@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.
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.
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
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
@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
@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.
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.
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
@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.