Results 1 to 11 of 11

Thread: Why use VIEWS

  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Why use VIEWS

    As I understand, a view is just a read-only customized-version of a table. By customized, I mean you can choose to exclude certain columns depending on the context of the view.

    So why would I want to use a view? Why not just use a stored procedure that returns the result of a SELECT statement?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dbguyfh
    As I understand, a view is just a read-only customized-version of a table.
    You don't understand it

    Views are not necessarily read only.
    They aren't really a "customized-version of a table" they are just a stored query. When I did relational theory views were explained by their similarity to tables, just as you say - but they are defined by DML fer chrissakes!!1

    Some people on here hate them but I like them. Here are the main reasons - encapsulation and code reuse. You tried reusing the set output of a sproc?

    You do need to be careful that their use is fairly tightly controlled though. You don't want deep, nested views for example.

    HTH

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by pootle flump
    You don't understand it
    Some people on here hate them but I like them. Here are the main reasons - encapsulation and code reuse. You tried reusing the set output of a sproc?
    Thanks for the reply

    Hmmm... So you said encapsulation and code resuse? Isn't this just want an sproc does?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Views are great [when not abused]!

    Perfect example fo their greatness: imagine a situation where you have a table of people, which includes people who currently work for you and people who have left your employment - how do you model this?

    Well, they share the same attributes (ok, an employee doesn't have a leaving date, but shh a minute ) so having them in two separate tables would not be an appropriate design, so what do you do? You create a people table and create 2 views, one for leavers and one for employees; identified by the leaving date attribute.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Again the "problem" (this term depends on context) with a sproc is that it is near impossible to use the outputted set as a source for anything else. You can dump the results in a temp table, and you can do some funky fudging with OPENQUERY (if memory serves) but you can't write:
    Code:
    SELECT x, y, z
    FROM mytable
    INNER JOIN 
    mysproc(@input = 1, @input_2 = 12) ON
    .....
    In short - they encapsulate logic but are poor for allowing that logic to be reused by other objects.

  6. #6
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by pootle flump
    In short - they encapsulate logic but are poor for allowing that logic to be reused by other objects.
    You can use a table-valued UDF for this. It can be joined in a query, and it can contain all the logic you want.

    I should add that I'm one of "those people" who doesn't use views.

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by georgev
    Views are great [when not abused]!

    Perfect example fo their greatness: imagine a situation where you have a table of people, which includes people who currently work for you and people who have left your employment - how do you model this?

    Well, they share the same attributes (ok, an employee doesn't have a leaving date, but shh a minute ) so having them in two separate tables would not be an appropriate design, so what do you do? You create a people table and create 2 views, one for leavers and one for employees; identified by the leaving date attribute.
    Ahh, ok.

    So let me see if I understand this right. When using two separate tables, I would need: EmpId, EmpName, EmpJoinDate for the people who DO work for me, and I'll have EmpId, EmpName, EmpLeaveDate for the people who quit (or got fired).

    So...

    Because both tables share the same EmpId and EmpName, I should just create a "master table" containing the four fields EmpId, EmpName, EmpJoinDate, EmpLeaveDate.... Then I'll create 2 views for our two cases: one that hides EmpLeaveDate, and one that hides EmpJoinDate

    Is that right?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by cascred
    You can use a table-valued UDF for this. It can be joined in a query, and it can contain all the logic you want.

    I should add that I'm one of "those people" who doesn't use views.
    But a third party application can't link to, and query, a UDF as it can a table or a view.
    So, another place views have value is in creating a layer between the application and the database tables.
    Not to mention indexed views that can be used to implement constraints too complex for DDL.
    I use views VERY seldomly, as my reusable code usually goes into UDFs, but they do have their place.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by blindman
    I use views VERY seldomly, as my reusable code usually goes into UDFs, but they do have their place.
    I also use views seldomly, and agree with this statement.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cascred
    You can use a table-valued UDF for this. It can be joined in a query, and it can contain all the logic you want.

    I should add that I'm one of "those people" who doesn't use views.
    Agreed. Comparing sprocs & views is not really comparing apples with apples. A more interesting question (that blindman answered) would be why bother with views when table valued UDFs exists? I reckon I write 4 sprocs to every UDF and 20 sprocs to every view (approximately ).

  11. #11
    Join Date
    Sep 2005
    Posts
    161
    I am a view-a-holic, and I haven't written a view in 100 days.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •