EDIT: This is a textwall, I know, sorry for that but I got on a roll. Peter, a response to you starts about 1/3 of the way down. /EDIT

I fear there's a bit of semantisicm in there but I get your drift. What I wrote five years ago I would consider horrible today. However, there may be a locality difference here. DBAs are just another title for SQL Devs here in the states, and they're 'run of the mill' for me. App devs tend to write multiple nested loops and the like because of their familiarity with that form of processing and data manipulation.

Some of this was definately from the misunderstanding of how you intend to use views. I'll get more into that below, but most of my contracts lately have been incredibly sensitive data sets... it's part of why I avoid the discussions completely on 'how easy it is to find people' from a few bits of data. I don't need the temptation. 😉

Definately misunderstood, but I think we're coming to the point where our approaches were radically different to deal with the same problem.

*facepalm* Oh, lords yes. That was definately NOT what I meant! 😛

Okay, I apparently had no idea of what the definition of a 'narrow modular boundary' was... and possibly still don't.

Now, how did I deal with the multiple app front ends being 'updated' in pieces for a 24/7 five nines system? We used schemas bound to logins and made sure the front end code simply called for procs, not dbo.procs. Any proc that could work between two versions stayed as 'dbo'. Any proc that was a version output/input change (parameters or output columns/datatypes) was dbo for the old version, and schema'd to the new version. The schema would come in, get its own procs in its schema first, and then default to dbo if there wasn't one for its default schema name. Same way tables work. The next upgrade shuffled that schema down to the DBO level.

More than a single version being supported except during switchovers is where I was reacting to, not the logistics behind a five nines system upgrades, which is a different scenario. I've never had to try to support four+ versions off the same schema, and it's not something I've ever really deeply thought about.

@peter-2:

Development is eased with direct binding but performance is rarely assisted. Also, you expose yourself to a number of dangers that dynamic SQL poses. I have worked in very small shops where they made practically six calls to 4 tables in a single database... and that's all they ever really needed. The tables could have been denormalized, but no reason, really. In this case... the proc is STILL the better choice. You've encapsulated the call for troubleshooting, removed injection concerns, and have better security separation between front and back end.

Additionally, tight binding and flexibility rarely go hand in hand. If you mean the flexibility for the devs to work in only their programming language to send code to SQL, then I'd agree. If you mean future programming flexibility, I'd disagree. Any change to schema would require you to re-write multiple code locations instead of a single proc acting as a function call for the front end.

And yet if the procs are already written you don't even need to do that.

You're correct, and that's the majority of my work. Now, are the front ends public or private depends on what we're doing. Most companies these days have some form of portal for their clientelle, and thus you must allow for eventual exposure of some kind of data to them. Now, is the majority of the work being done in the back? Sure.

Out of curiousity, have you ever worked with DB2? DB2 required the kind of tight binding you're speaking of. There's a reason it's basically out of the market except in legacy systems.

You'll have to define 'richer state management' to me. However, I agree with there being rules that are more efficient in the app layer than in the database layer. The reverse is also true. I'm more than happy to discuss with devs on the teams I assist which I feel would be best where and why. Particularly formatting/string manipulation, display, and row to row computations are certainly best done in an iterative environment instead of a set based one. Aggregations, data organization and associations, filtering, and usually mathmatics are normally best done at the DB level.

However, there is a significant drawback if you just want to look at time to process a call to the DB. A proper string scraper in the front end to remove all possibilities of injection to your particular system can take anywhere from 15 to 20 milliseconds. No, that's not long. Every parameter for every call however has to be ran through that process. It's just one place where you're easing the time for development and placing it on the time to process.

Another thing is every dynamic call to the DB has to be uniquely compiled. It can't re-use cache plans. Now, cached plans have some of their own quirks but under 99.9% of the situations they're just fine.

Thirdly if the app is 'overhandling' data, you're dealing with a lot of unnecessary network traffic and waits.

Finally, and my most vehement argument, is eventually one of those little apps grows up. Your 'prototype' becomes the best thing since sliced bread and someone in accounting just dumped every spreadsheet for the last 30 years into it over a weekend. Now, since you've cut so many corners, you're looking at re-designing the entire thing into optimal calls. Wouldn't it have made sense just to have done it 'right' in the first place, and then tweak for volume?

Catch All Searches, I agree. And they can be done under the hood without significant problems. Real Time reporting you'll have to be a bit more explicit to me about. If you mean real time from the data, I hook SSRS reports to procs all day long off our OLTP system and they're used quite effeciently. Prior to SSRS ASP.NET served similar purposes for us, and so did MS Access with passthrough queries. Now, if you're talking dynamically built user reports, we're in a whole different conversation.

A) That's not common. Most firms expect Tech to build them for them. Part of why SSRS 2k8 went more dev driven than user driven, like SSRS 2k5 was.

B) If you've got power users at that level invest some time in SSAS and PowerPivot. It's a lot easier.

If you have the proper level of SQL, putting it in a proc is no more difficult than writing the query and wrapping it in CREATE PROC @param1 INT, @param2 VARCHAR(50) AS ... GO

I couldn't agree with this specific statement more. Thus why I get 'enforced' rules of using things like CI for database scripts in storage. Ever try to CI an index column order modification, and not have it rebuild the index EVERY time the CI is ran?

Does it help you if you knew that I started as a programmer? That it helps me understand what my devs need? That I still give them bloody havoc when they start sending dynamic trash down the pipe that ignores the indexes in place and they're scanning a 400 gig audit table?

That last is mostly because they didn't bother to wrap it in sql_execute with parameters properly and didn't have me do a code review than because they wrote crap SQL. Sometimes you need to scan. Sometimes there's ways to do it without shared locking the entire table and thus locking the triggers that are trying to write to it from the main table.

I agree, there is not only a single right way... if your machines can handle an unoptimized load on your database(s). Those also aren't the times when you need a SQL Dev professional like the majority of us who hang around here.

There are cases where I allow my front ends to send me down dynamic SQL. Particular cases where there is an exact scenario that makes for difficult or non-optimal T-SQL coding and it won't break security or expose the company awkwardly if the front end login information is compromised. However, data storage and retrieval logic will never fit directly into object/instance logic, you'll always require widgets of some form. Procs and their ilk are generally the most secure method and allow for both logic reusage and portability as well as separation of components during troubleshooting issues at 3 in the morning when some client 8 hours east of you on the planet is screaming holy rebellions because they can't check their cat's color for today.

If you want to ease development by avoiding them, that's your shop's call. Just hope the powers that be will give you time to rebuild from scratch once it gets out of scope.