August 28, 2008 at 10:12 am
Views can be used as a security measure, to prevent direct table access. I've always felt that was their first, best use. You can also abstract complex joins with a view. In my environment the second approach has been used extensively. I disagree with this approach in general but wanted some feedback to see if there is consensus on this or if I am out in left field.
My argument against the usage I see here is that there are views nested within view within views. This is done to such an extent that what may appear as a USP (User defd sp) that only joins 2 tables and a view may in fact be joining 20 tables via that view. I think if you have to join 20 tables you should KNOW you are doing so. I think many of these views are generalized-- possibly joining more tables and/or returning more columns than all consumers use.
In some cases there are views that are used by a single USP which seems to me to unnecessarily complicate the structure-- embed the TSQL from that view in the USP!
The counter argument of course is that if the view is the right way to get some linkage that it's better to black box that code so we don't constantly reinvent the wheel.
I can see an argument for using views to abstract data, but I don't think views built from views (to the Nth) is a great way to approach things.
What do you think?
August 28, 2008 at 10:21 am
I've used views in both ways, though typically we've named them vXX, so that we know they're views. If you use one, you have to understand what's underneath. Typically I haven't seen views nested more than twice, but often there are 10 tables underneath. The view abstracts the need to write all that code for joins.
Removing the view wouldn't get away from that, but it might make things harder to write.
If you write SPs, perhaps it's not a big deal, and in 2005, with CTEs, I would tend to use views less.
The other place is to simplify queries for SSRS. Often the people writing reports don't want to understand SQL, and they might not need to. Giving them a view allows them to more easily write reports.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 1, 2008 at 7:00 pm
With the progression of the database/object abstration tools like MyGeneration/DooDads/EntitySpace/Hibernate, views offer the DBA/SQL developer the ability to pre-design application queries that can then be used easily by the development team.
That is a major benefit.
The more you are prepared, the less you need it.
September 1, 2008 at 7:52 pm
mstjean (8/28/2008)
My argument against the usage I see here is that there are views nested within view within views. This is done to such an extent that what may appear as a USP (User defd sp) that only joins 2 tables and a view may in fact be joining 20 tables via that view. I think if you have to join 20 tables you should KNOW you are doing so. I think many of these views are generalized-- possibly joining more tables and/or returning more columns than all consumers use.
For security, absolutely a great idea. For encapsulation, should be kept to a single level because views of views violate the second directive of database processing... process/return only the columns/data necessary to get the job done.
--Jeff Moden
September 1, 2008 at 9:03 pm
Stored procedures are in my opinion a better choice for security purposes, because they hide the code from the users. So in that respect, implementing a view over a stored procedure really depends on your usage.
In the past I have setup many reporting projects using views, but lately have been moving to stored procedures. I've done this for a) security reason and b) source control. Views can often sprawl as the nature of them is not often expressed in the naming conventions people tend to use.
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size]
September 2, 2008 at 6:47 am
I agree with using views for security purposes, but, as Jeff noted, you shouldn't ever go more than a layer deep on views because you will run into performance issues. The optimizer just starts to choke after a while. Stored procedures offer a lot more in the way of functionality than views can.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 10, 2008 at 7:53 pm
Thanks for your input.
You've gotta write the code somewhere... If you put it in a view you only have to write it once-- and if you discover a bug you only have to fix it once... and hopefully all code that uses it will continue to work. But when the view gets used like spandex (one size fits all) performance will inevitably suffer.
I'd rather have the pseudo-view code in a document somewhere I could paste from (or even stored as a view if we all agree not to *call* the view)-- and then whittle it down to suit my needs without extra table joins or columns selected.
September 11, 2008 at 8:41 am
in our shop, we see views used and misused. the worst examples are the ones you have already outlined here, such as nested views. but you can also have issue with parameterized views across linked servers. we have fought many fires that are the result of someone doing what they think is a simple select across a link but it turns out to be a view and their query drags.
----------------------
https://thomaslarock.com
September 11, 2008 at 9:44 am
Lucky for me we don't do any cross-server queries of any kind... the last time I tried (gaddz I think that was back in v7) it was pretty dreadful no matter what.
September 11, 2008 at 6:52 pm
Like all aspects of a database, or development for that matter, views have +/-'s . learn all aspects of the database so you can use them wisely
The more you are prepared, the less you need it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply