Thread drift yes....still, I cannot let this pass by...'

And your comments, as well as the Wiki are not biased???< bg >.... The Wiki being the sole arbiter of the truth or good advice??? I think not...

I'll address the security issue right here and now... With a remote view, right off the bat, the user or the role the user is a member of has to have select privledges...Most people use RV's to update, add, and delete data as well. As a result, either the user or the role has to have insert, update, and delete permissions as well. These operations are pretty much all or nothing. If the user actually got hold of the password, s/he could latch on with anything and tinker with the data.

With Stored Procedures, you have a built in buffer of sorts. You can can determine which application is trying to access the data with the App_Name() function. You can also narrow things down further with the host_name() function in order to determine which workstation is trying to access data. You can also use the User variable to qualify the login. A stored proc gives you an opportunity to check all of these items prior to returning data. Of course, with RV's, you don't get access to any of these features...With SP's, you could limit execution to specific apps/user, or any combination thereof... SP's also allow you to logically/virtually partition your tables.

Compared to stored procedures, RV's *are* inherently less secure and less flexible that SPT. Once you go to SPT, it only makes sense to go all the way an use SP's. This issue by the way dovetails into why trying to update data through an ADO recordset does not work. Rather, the best course of action is to use the command object that executes a SP. The use of stored procedures has been recognized as a best practice in the industry: for good reason.

And, let's not dismiss the tight coupling between the physical structure on the server and the view definition. One small mis-step, and kaboom, your view is toast!

Once the Enterprise Manager came out, that took away any reason to use RV's. Browsing data on the backend is a snap...RV's are an incomplete wrapper for SPT. That is why I make the distinction between SPT cursors and RV's. SPT is good because it provides access to the full power of the language RV's are built upon. With RV's you sacrafice a lot of power and flexibility for convienence.

On balance, RV's don't scale very well. And when I mean scale, I don't just mean size of data. I am talking in terms of security, maintenance, etc.


A person can waste time going through some wishy washy arguments for RV's. Or, one can get to the point and provide slam dunk arguments for SPT/SP that pretty much puts the issue to rest. If I have to make the argument for SPT/SP vs. RV's, I'll lay the security and data coupling issue on the table as my primary arguments. As secondary arguments, I'll hit the issue with the lack of flexibility and lack of stored procedure support. I still have one of the best arguments in the hole - error and exception handling. With RV's, you are forced to deal with the error on the client. With Stored Procedures, the ability to handle errors is much greater and much more flexible since you can deal with them on the server - true application/data independence.

For some po-dunk little app, can RV's work? Sure. However, whether something works and whether it is the optimal approach in the long-run are distinctly different issue. I wish I had a buck for every person who thought RV's were the way to go, only to find out a year or two later, after the data has grown, that it was not a good idea.

Let's not forget, with SP's, you can use other techniques such as index hints, etc. You cannot do that with RV's.

To be fair, there are implementations with RV's that work, and work well. Why? Most often, it has been my experence that the developers put in a lot of effort to keep things working. This gets back to the maintenance argument. STP/SP's greatly minimize that issue.

Do SP's create other issues? Yes, but then again, there is no such thing as a free lunch. The issues with SP's however, can be overcome with developer tools. I cite Dataclass (www.redmatrix.com) as a great example of how a managed environment should work. RV's was supposed to be a manged environment. But, the implementation simply does not work.


People want folks like us to turn the grey into black and white. Of course, this is not always possible. Fortunately, this is a case where that is possible. And with that said, I submit the issue between RV's and SPT is one of black and white.

Not good advice??? Heck, this is the best advice one could get: decisive, on point, non-ambigous, and most of all, from the benefit of first-hand experience... The wiki pages you cite leave one with more questions than answers....

Of course, if you happen to take issue with something I said, please, put it on the table....Sorry Steve, I take your reply to be an indirect shot in my general direction, especially in light of our recent history on the UT...< s >.