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


Using Views


Using Views

Author
Message
fregatepllada
fregatepllada
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 322
@Tom - You answered your question: "if triggers are properly written".
I had to deal with nested loops instead of set-oriented implementation in triggers :-)
LutzM
LutzM
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22321 Visits: 13559
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
Gary Varga
Gary Varga
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26423 Visits: 6541
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!!!
TheRedneckDBA
TheRedneckDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 2613
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
david.gugg
david.gugg
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1840 Visits: 1042
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.


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4180 Visits: 1695
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!
Gary Varga
Gary Varga
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26423 Visits: 6541
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!!!
Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4180 Visits: 1695
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!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: Administrators
Points: 139357 Visits: 19412
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
My Blog: www.voiceofthedba.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