SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Always Abstract


Always Abstract

Author
Message
GoofyGuy
GoofyGuy
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 971
Steve,

Agreed!
Recurs1on
Recurs1on
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 431
Where I work, we have kind of a hybrid approach. We recently created views for all our tables, refactored the code, and then removed all access to those tables from the service level account our application runs under. It has read permissions on the views, so we can still write ad-hoc queries, but all inserts/updates/deletes have to be done through stored procedures.
RonKyle
RonKyle
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2818 Visits: 3505
I favour moving as much application business logic into stored procedures as is possible, thereby abstracting out the UI layer from the business logic


I think I can help with wondering why it's not in wider practice--this is not a good idea. The database is a chokepoint, and you want it processing as few business rules as possible. The enforcement of business rules really belongs in the middle tier, whatever that might be for the application. Let the code in the intermediate levels do their thing and then send the result to the database.



GoofyGuy
GoofyGuy
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 971
@RonKyle wrote:

The database is a chokepoint ...

I can't imagine it being a chokepoint any more so than anything else. Network bandwidth can become a chokepoint. So can application servers. So can an application. If a component isn't configured correctly, isn't sized right for the job, isn't maintained properly - well, sure, it'll become a chokepoint.
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12568 Visits: 10693
In addition to implementing views as a means of rrestricting user access to columns and rows, I've also used them as a wrapper for tables that contain symmetric key encrypted columns, having computed columns perform all the tedious function calls. In the example below, all the application has to do is properly open the symmetric key, and then it can select from the view, returning data unencrypted and re-cast as needed. Before writing views to do this, I was getting way too many questions from developers and analysts about how to decrypt columns. Now it's transparent.

create view ABC.vMembership
as
select
Patient_ID,
Group_Nbr,
Begin_Date,
Term_Date,
cast(DecryptByKey(First_Name) as varchar(30))First_Name,
cast(DecryptByKey(Birth_Date) as datetime)Birth_Date,
cast(DecryptByKey(Subscriber_ID) as varchar(30))Subscriber_ID,
cast(DecryptByKey(Phone_Nbr) as varchar(30))Phone_Nbr
from ABC.Membership;




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
LightVader
LightVader
SSC Eights!
SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)SSC Eights! (943 reputation)

Group: General Forum Members
Points: 943 Visits: 2987
Craig-315134 (8/21/2013)
@RonKyle wrote:

The database is a chokepoint ...

I can't imagine it being a chokepoint any more so than anything else. Network bandwidth can become a chokepoint. So can application servers. So can an application. If a component isn't configured correctly, isn't sized right for the job, isn't maintained properly - well, sure, it'll become a chokepoint.


I have to agree with Craig here. The database can be a choke point but it isn't always. We have one application here where the application itself is the choke point. And we've finally gotten the vendor to acknowledge it!



The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
eccentricDBA
eccentricDBA
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1728 Visits: 1339
I find this editorial interesting because over a decade ago I the development team I was working on had this exact discussion.

I was a developer at that time and I was pushing everything to stored procedures. The limitation we had the time though was that I was unable to do queries via stored procedures because the database I was working with and the application deveopment tool would not allow results to be returned and consumed. This was also help lead us into the discussion of using views.

The number one reason for the failure of using stored procedures was that we where a RAD/JAD, Rapid Application Develpoment / Joint Applicaiton Development, shop. Because of this we constantly making changes midstream and between knowledge of the development staff of being able to write procedures and the turn arround time of getting changes to the stored procedur applied to the database. Slowly the code shifted back to the application tier.

Fast forward to today and the new champion is Entity Framework. It has become the choice to create the abstraction from code to database tier.

For the most part abstration is a good thing. I really don't want to have to remember how to write the code to add an item to a list array. I just want to be able to call $list_items += "New Item" and be done with it. Just like a developer doesn't want to rewrite the code everytime to loop through results and add result paging or to do work around all the plumbing to do a simple insert and update of a record.
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12568 Visits: 10693
eccentricDBA (8/21/2013)
I find this editorial interesting because over a decade ago I the development team I was working on had this exact discussion.

I was a developer at that time and I was pushing everything to stored procedures. The limitation we had the time though was that I was unable to do queries via stored procedures because the database I was working with and the application deveopment tool would not allow results to be returned and consumed. This was also help lead us into the discussion of using views.

The number one reason for the failure of using stored procedures was that we where a RAD/JAD, Rapid Application Develpoment / Joint Applicaiton Development, shop. Because of this we constantly making changes midstream and between knowledge of the development staff of being able to write procedures and the turn arround time of getting changes to the stored procedur applied to the database. Slowly the code shifted back to the application tier.

Fast forward to today and the new champion is Entity Framework. It has become the choice to create the abstraction from code to database tier.

For the most part abstration is a good thing. I really don't want to have to remember how to write the code to add an item to a list array. I just want to be able to call $list_items += "New Item" and be done with it. Just like a developer doesn't want to rewrite the code everytime to loop through results and add result paging or to do work around all the plumbing to do a simple insert and update of a record.

Entity Framework is OK, so long as you're using it for a case usage like bookmark lookup / edit / save.

However, there is a "code first" feature where app developers use EF to design a logical model for their application and then EF auto generates DDL scripts. Using EF as a database design tool leads very poor database implementations.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
pdanes
pdanes
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 1354
I do everything myself, application side and database side. I do not allow (myself) access to tables directly. Views, read-only, even if it's just a "SELECT everything FROM table" statement. Updates and deletes are done through stored procedures only.

Allows me to change a table and all I have to do is change the view to accomodate the change - often zero change necessary to the application. And if there is a problem with changes to the data, it all funnels through procedures, which I can modify to include traces, metrics, error checking, whatever is necessary, ALL with NO impact on the application.

Didn't think of this myself, saw the advice somewhere when I started learning SQL Server, maybe even on this site. Thought about it, decided it was a good idea and have done it that way exclusively.

I'll always do it that way, unless something even better comes along. I can think of no reason to ever do it any other way. Snivels about performance don't fly. This system, used properly, will perform just as well as allowing direct access to tables, often much better. If the performance isn't adequate, look at the design. Unlocking access to tables for performance reasons is cop-out, and dangerous to boot.
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7913 Visits: 3290
I read about Simonyi and why he prefixed stuff and Hungarian notation is a misinterpretation of his work.

The prefix was to denote purpose but has been misinterpretted as denoting type.


I've also seen the argument about whether or not to put business logic in the DB layer come around full circle and no doubt it will do a few more loops over the next decade.

I am sceptical about the argument that putting the SQL in the app makes it more readable and maintainable. I could understand it if the tools available or the favoured tool was not particularly good at dealing with database objects. To me stored procedures are conceptually the same as refactoring out code down into smaller and smaller methods. Yes it becomes a pain in the arse to drill down deeper and deeper into the code to find its like a Russian doll but ultimately you end up with a single simple method call.

I think where there is an unhealthy separation of roles between application developers and SQL developers then you get friction points and misunderstandings. I was an application developer long before I dealt with SQL Server so when I started out the idea that I could encapsulate a query to GetCustomerByID into a single stored proc was like manna from heaven!

From a production DBA perspective I think a large part of the worry is that there will be a 3am phone call complaining about a DB problem and all you can do is point to a particular query but have no way of fixing or mitigating the problem. Word gets around that "its a DB problem" and you end up whipping boy. I've been on the wrong side of a blamestorming session and no facts were allowed to contradict the "its a DB problem". It was actually an unclosed transaction in a loop that instantiated command objects to execute in RBAR fashion.

LinkedIn Profile

Newbie on www.simple-talk.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search