How do YOU use VIEWs?

Question

How do YOU use VIEWs?

mstjean

Hall of Fame

Points: 3310
More actions
August 28, 2008 at 10:12 am

#197593

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?
Cursors are useful if you don't know SQL

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 1

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.

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jody Claggett-376930 Ten Centuries Points: 1152 More actions · Answer 4

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]

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mstjean Hall of Fame Points: 3310 More actions · Answer 6

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.

Cursors are useful if you don't know SQL

SQLRockstar SSCertifiable Points: 7028 More actions · Answer 7

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

mstjean Hall of Fame Points: 3310 More actions · Answer 8

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.

Cursors are useful if you don't know SQL

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 9

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.