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 Friday, August 1, 2014 8:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 9:23 AM
Points: 186, Visits: 365
I've seen several major ERP systems designed using views (JD Edwards, PeopleSoft, etc) . It adds a security layer and makes it easier to change the underlying data structure. It is a best practice. And for CRUD - use stored procedures as much as possible - as was noted earlier.
Yea, it is quicker to just use tables, until you need to make a change.


The more you are prepared, the less you need it.
Post #1598712
Posted Friday, August 1, 2014 9:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:52 PM
Points: 267, Visits: 250
We're in the middle of a huge project that's migrating an old legacy database into the current model, and we're using this strategy heavily, along with instead of triggers on the views. It's been very helpful in letting us make the changes iteratively, especially since there can be literally hundreds of objects in the database that reference a given table in some fashion.

There are some serious challenges though, and performance is sometimes one of them. There's no way we could do these changes all in one release cycle though, that way would lie madness and destruction.
Post #1598748
Posted Friday, August 1, 2014 11:37 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 7:17 AM
Points: 31,080, Visits: 15,526
Jason Whitish (8/1/2014)
We're in the middle of a huge project that's migrating an old legacy database into the current model, and we're using this strategy heavily, along with instead of triggers on the views. It's been very helpful in letting us make the changes iteratively, especially since there can be literally hundreds of objects in the database that reference a given table in some fashion.

There are some serious challenges though, and performance is sometimes one of them. There's no way we could do these changes all in one release cycle though, that way would lie madness and destruction.


This would be interesting to plenty of people. If you had a particular table or two and are interested, we'd love an article or two on your experiences.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1598784
Posted Friday, August 1, 2014 11:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 2:52 PM
Points: 267, Visits: 250

This would be interesting to plenty of people. If you had a particular table or two and are interested, we'd love an article or two on your experiences.


I'd be happy to. I'll go ahead and get started on it this evening.
Post #1598787
Posted Friday, August 1, 2014 12:01 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 140, Visits: 900
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?

I queue up developer requests for a day, code and test locally, then deploy to development the next day. It breaks the app but all developers fix their stuff, check in, and get running again. The scheduled break and fix usually lasts less than an hour. I keep a full history of all DDL changes so I can apply them in order into other databases such as Testing, Staging, Production, etc.

Alternatively, if you have a stored procedure only interface to applications, you are completely free of the physical database design. This is the case for 90% of what I have.

I only use synonyms for objects external to the current database. I don't see how they would solve anything for a local schema change.
Post #1598789
Posted Friday, August 1, 2014 1:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 7,742, Visits: 9,491
I agree withGary's first comment above; views are not a patch on stored procedures as a mechanism for preotecting the schema from becoming frozen/unchangeable because the apps depend on knowing its exact structure. Stored procedures do a much better job of this. Anyone who has read my other posts on defending the schema from being frozent by the app is alreadyaware of my views on this.

Someone else pointed out that it's impossible to update views that involve more than one table. That's an implementation restriction, there's nothing in relational theory that prevents update through views involving multiple tables provided it's computationally possible to determine exactly which rows in which tables are involved in the update; but the general problem "is this update on this view possible" is Turing-undecidable (proved by Buff in 1986) so Codd's original Rule 6 had to be modified to avoid saying that all possible updates through views had be valid and say instead that the system had to decide which updates would be valid, and all the implementations are very lazy about this, for example T-SQL says only updates to seingle table views can be valid. That means that any update that involves two tables has to be done by the app as two sepaate updates if views are used unless INSTEAD OF triggers are used to get round this poblem, which suggests that using views to separate the schema from the app forces you either to use triggers or to have multiple queries from the app to do the update, each of which is undesirable. Using stored procedures to separate the schema from the app doesn't cause this problem. The delete case is even worse that the update case - almost no deletions on multi-table views are possible. So using views is usually going to cause performance problems unless you use triggers, and it's at least as easy to write a set of stored procedures as it is to write a set of triggers.

Performance can be an issue too. Stored procedures tend to beat views there. They also win on security - if apps have access only to stored procedures it's very easy to make sure that parameters are not misused so than injection attacks are impossible. Using views doesn't help with that at all.

Of course it can be useful to provide the apps with some views - indexed views can sometimes give a big performance boost; but it's not a good idea to let the app have direct access to them.


Tom
Post #1598805
Posted Friday, August 1, 2014 7:40 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:42 PM
Points: 635, Visits: 2,215
The one that I've seen our development team doing and it worked rather nicely: They would never delete a column. They would just add them. Then copy data from the old column(s) and turn on the new app. Then if something went wrong it was just a matter of just rlling back to the old format pointing at the original column.



----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.
Post #1598842
Posted Friday, August 1, 2014 9:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:44 AM
Points: 35, Visits: 171
Agree for 100% in terms of stored procedure - that is ultimately "last line of defense"
You would not scare me with Access linked tables to SQL Server -apparently this is curren "state of the art" of legacy systems created as mushrooms after rain since mid 90s. Been there, done that (no free T-Shirt unfortunately).
I usually use Views to retrieve data in SP (the R part of CRUD), and I would use them for reporting and data export during integration. I normally even enforce TOP 100 Percent to enforce sorting in views.
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.
Create, Update and Delete SPs would work against table and I prefer this way because I could operate
with a set of records (bulk insert, update and delete via TVP) rather than struggle with RBAR anti-pattern
introduced by INSTEAD OF triggers.
Post #1598844
Posted Saturday, August 2, 2014 6:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 7,742, Visits: 9,491
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".
Way beack when I had the problem of converting a heap of rubbish into a viable system. One major component was a vast quantity of C++ which was full of embedded SQL, often that SQL being generated dynamically using C++ string manipulation. One particular problem was a table that grew like topsy, although it should have been very small. This table was declare like this (names changed to protect the guilty):
CREATE TABLE sometable (
ID int identity PRIMARY key
, thingID int REFERENCES thing(ID)
, otherthingID varchar(8) REFERENCES otherthing(ID)
, and several columns more, irrelevant to this comment
) ;

It turned out that the C++ part was doing a lot of inserts. Although thingID and otherthingID weren't declared NOT NULL, they were never observed to be NULL, and the logic of what the application was supposed to present to end users was that (thingID,otherthingID) was a natural key despite there being no unique constraint. The ID column couldn't easilty be eliminated, because the last value inserted was carried about and used in the C++ code (read by "select top 1 ID from something order by ID desc" tacked on to the end of the embedded SQL string whenver the C++ thought it was creating a new row), unsurprisingly given the quality of the rest of the embedded SQL), and rewriting the app was going to take a long time, longer than we could live with this problem.

So the short term problem was how to prevent the table from growing too big and causing performance problems without eliminating the ID column. The natural thing to do was try adding not null restrictions to the two columns of the natural key and a unique constraint and see what happened; what happened was chaos; the unique constraint caused the application to fail in a number of ways (it felt like about a million differnt ways, but of course wasn't); the NOT NULL constraints did no harm, so we tried using them plus an "instead of insert" trigger which deleted all rows which had the same thingID and otherthingID as the row to be inserted before inserting it. That worked - the app didn't complain, and about a dozen known functional errors disappeared from the outstanding bug list (the inserts should have been updates, but we couldn't do that because the highest ID was used in generating some more embedded SQL in that dreadful C++).

Of course we did rewrite that C++ eventually; but first we changed the rest of the app (mostly writen in JS, and with no string manipulation generating SQL) so that it didn't use the C++ component except where essential (the previous developers' job creation project had decided to route all calls to this database through the C++ component, which was incredibly stupid but sure would have guaranteed them lots of work if I hadn't been pulled in to trouble-shoot the db stuff) so there was a lot less functionality needed in C++.


Tom
Post #1598929
Posted Saturday, August 2, 2014 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 7,742, Visits: 9,491
fregatepllada (8/1/2014)
... ... Create, Update and Delete SPs would work against table and I prefer this way because I could operate
with a set of records (bulk insert, update and delete via TVP) rather than struggle with RBAR anti-pattern
introduced by INSTEAD OF triggers.

Why do you think that INSTEAD OF triggers introduce a RBAR pattern? If the triggers are properly written they are set-oriented.


Tom
Post #1598931
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse