Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Using Views Expand / Collapse
Author
Message
Posted Sunday, August 3, 2014 6:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 12:47 AM
Points: 37, Visits: 177
@Tom - You answered your question: "if triggers are properly written".
I had to deal with nested loops instead of set-oriented implementation in triggers
Post #1599032
Posted Sunday, August 3, 2014 7:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 6,842, Visits: 13,373
I've been in a project until recently where we had to develop a new database interacting with several applications.

At the beginning it was just me who advocated against the plan "just do it like we did a similar db project 10 years ago" (started with SQL7 and then moved to SS2k; the code has been modified just minimally to reflect the deprecated features like ORDER BY in views).
So I suggested several rules:
Each app will get it's own schema where all the app-related code will be placed.
SELECTS from an app will only be against views.
INSERT/UPDATE/DELETE will only be allowed through stored procedures.
Permissions are based on schema level (grant exec and select),....

The decision was to "give it a try".
During the project several adjustments needed to be made:
Examples:
Tables had to be separated (split vertically) to improve performance and reduce blocking -> the app Dev didn't even notice we had to do it nor when we did it
Requests for new stored procedures/SELECT's: the app Dev would request a new function and we would create the "interface" (name, parameter list, output format) and create the sproc/view with dummy data matching the expected output. Now the app dev and we could work in parallel: we made the code to return data based on "real data" while the app dev could work on their side in parallel. Did we have to bother regarding security? Not really, since all we had to do is add the sproc/view to the related schema and everything simply did work. This allowed us to reduce the time-to-deliver significantly.
When we had performance issues we could very easily identify the source being either the database or the app and we could start to solve such issues immediately.
But the major change was the ability to quickly answer the question: "Who needs access to data stored in coly c1 of table t1?" We've been able to answer it instantly (since we didn't add a view/sproc to a schema without a proper request and documentation and provided only the columns needed). It's been slightly more difficult for the folks dealing with the 10yr old project...

Guess what concept will be used for the next project coming down the road...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1599043
Posted Sunday, August 3, 2014 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 5,819, Visits: 3,737
I really hope that I work with DBAs like Lutz on my next project

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1599051
Posted Sunday, August 3, 2014 9:30 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 10:28 AM
Points: 1,544, Visits: 2,274
call.copse (8/1/2014)
In light of the QOTD is anyone using synonyms for such purposes yet or would they not work for that? Don't know much about them.

Views have been soured for me by a particular project (where they were done very wrong), but I guess they could have a place if done correctly - I might consider them for a project I have coming up.

The using all stored procedures to define the interface thing is fine but it can put an awful lot of business logic into fairly impenetrable code. It's got some advantages, but the lack of ability to do things in a very well structured way puts me off that course of action (as a developer, without any access to DBA services). Of course they always have a place in my book for some jobs - just not simple CRUD stuff.

Nice win for our lads at the Amex on another note Gary!


We use synonyms here and there, and have never had any issues with them "not working".

We use SPs over SQL code in our application whenever possible. In some organizations it might shift a lot of load to the DBAs or make the developers have to wait on DBAs to get them changed if needed, but it also allows changes to be made "on the fly" a lot easier in many cases without the need to do a code release. It just depends on the organization/environment you're working in.


The Redneck DBA
Post #1599111
Posted Monday, August 4, 2014 10:41 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:10 AM
Points: 421, Visits: 442
Gary Varga (8/1/2014)
This editorial highlights a key reason why I believe in using stored procedures. All I want from a stored procedure is to answer some question (no, not query) or to store a set of data (no, not a data set). I don't care how these happen and that gives power to those either side of the stored procedure interface definition. Anything can change on either side as long as the contract remains unbroken.

As a systems developer that often has a RDBMS at the back end, I want to give as much leeway to DBAs to do performance tuning as well as allowing for multiple system access to a single database.


Right on! If all data was CRUDed via stored procedures, the world would be a better place.
Post #1599332
Posted Tuesday, August 5, 2014 9:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 2,500, Visits: 1,584
TomThomson (8/2/2014)
fregatepllada (8/1/2014)
I usually stay shy from using ANY triggers - IMHO unless you implementing certain "JOB CREATION SCHEME". My usage of triggers was limited to populating historical tables when you need a "before and after" snapshots.

I agree with that generally, but there are also times when a trigger is a very useful thing to use to undo some of the damage done by someone's earlier "job creation scheme"...


We user triggers for population of audit tables and little else. I agree that they can be useful, but can also be poorly used and problematic.

Stored procedures have been standard fare for years. They are preferred for both the database issues mentioned as well as the security issues for web applications that face malware and potential SQL injection etc. Some may feel that there is additional work to build a stored procedure and the code to execute it, but the mitigated risk and flexibility is well worth it.


Not all gray hairs are Dinosaurs!
Post #1599814
Posted Tuesday, August 5, 2014 10:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 5,819, Visits: 3,737
Miles Neale (8/5/2014)
...
Some may feel that there is additional work to build a stored procedure and the code to execute it, but the mitigated risk and flexibility is well worth it.


...more effort to debug embedded SQL statements. It is an unnatural for them to exist. No real chance for tool support.


Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1599830
Posted Tuesday, August 5, 2014 11:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 2,500, Visits: 1,584
Gary Varga (8/5/2014)
Miles Neale (8/5/2014)
...
Some may feel that there is additional work to build a stored procedure and the code to execute it, but the mitigated risk and flexibility is well worth it.


...more effort to debug embedded SQL statements. It is an unnatural for them to exist. No real chance for tool support.


Gaz - I agree. But some still feel that if you have to interupt the flow of their code and logic to go to another tool and language that is is too much. It is easier for them for some reason to do it all in one place. I do not hold that view, nor will I defend it. But I have heard them speak of this approach as if they are right.

As to it being unnatural for embedded SQL Statements to exist, it is not but I wish it was.

M.


Not all gray hairs are Dinosaurs!
Post #1599872
Posted Wednesday, August 13, 2014 6:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:58 AM
Points: 31,364, Visits: 15,827
Bill Talada (8/1/2014)
Views might work for a small subset of DDL changes since views are rarely update-able when based on more than one table. How can you handle a case where a denormalized child table column gets moved up into a parent table?


Views are updatable with more than one table in their definition. You just can't update more than one table at a time through a view.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1603069
Posted Wednesday, August 13, 2014 6:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:58 AM
Points: 31,364, Visits: 15,827
Jason Shadonix (8/3/2014)


We use synonyms here and there, and have never had any issues with them "not working".

We use SPs over SQL code in our application whenever possible. In some organizations it might shift a lot of load to the DBAs or make the developers have to wait on DBAs to get them changed if needed, but it also allows changes to be made "on the fly" a lot easier in many cases without the need to do a code release. It just depends on the organization/environment you're working in.


Want to write about how you use synonyms? Always looking for descriptions of real world implementations.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1603070
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse