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


Using Views


Using Views

Author
Message
Andrew Peterson
Andrew Peterson
Say Hey Kid
Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)Say Hey Kid (686 reputation)

Group: General Forum Members
Points: 686 Visits: 725
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.
Jason Whitish
Jason Whitish
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 281
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62562 Visits: 19109
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
My Blog: www.voiceofthedba.com
Jason Whitish
Jason Whitish
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 281

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.
Bill Talada
Bill Talada
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1169 Visits: 1997
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.
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14304 Visits: 12197
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

Jim P.
Jim P.
SSC Eights!
SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)SSC Eights! (909 reputation)

Group: General Forum Members
Points: 909 Visits: 2215
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.
fregatepllada
fregatepllada
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 322
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.
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14304 Visits: 12197
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

TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14304 Visits: 12197
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

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