2

I've tried searching in different ways, but haven't found a clear answer to my question. This question almost answers my query, but not quite.

Besides the obvious readability differences, are there any benefits to using a view in a stored procedure:

SELECT 
    * 
FROM 
    view1 
WHERE 
    view1.fdate > @start AND 
    view1.fdate <= @end

...over using a linked table list?

SELECT 
    * 
FROM 
    table1 
    INNER JOIN 
    table2 
        ON  table1.pid = table2.fid 
    INNER JOIN 
    table3 
        ON  table1.pid = table3.fid 
WHERE 
    table1.fdate > @start AND 
    table1.fdate <= @end
Paul
  • 4,160
  • 3
  • 30
  • 56
  • 1
    Well, some views may be indexed, so in some cases, indexed views might help performance. Other then that I doubt if there is any real difference. However, I guess DBA experts can probably come up with better answers, so I'll just leave it as a comment. – Zohar Peled Apr 21 '17 at 10:07
  • Thanks for your input, @ZoharPeled. I did notice that, but wouldn't the table level indexes be just as useful (if the table is indexed properly in the first place)? – Paul Apr 21 '17 at 13:45
  • 1
    @Paul indexed views can create indexes that cannot be matched by table indexes, eg. an indexed view can contain aggregates. – Remus Rusanu Apr 21 '17 at 14:44

2 Answers2

2

Is not all about your app and you.

Think enterprise databases, with tens of different apps accessing the same data, and hundreds of individuals querying the data for business purposes. How do you explain to each one of the many individual how to recompose your highly normalized data? Which lookup field maps to which table? How are they joined? And how to you grant read only access to the data, making sure some sensitive fields are not accessible, w/o repeating yourself?

You, the DBA, create VIEWs. They denormalize the data into easy to process relations for the business people, for the many apps, and for the reports. You grant select permission on the views w/o granting access to the underlying table to hide sensitive private fields. And sometime you write views because you're tired of being called at midnight because the database is 'down' cause Johnny from accounting is running a cartesian join.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

There are no difference. Query plans will be identical in both cases. Query optimizer can use indexed view even if you don't use it explicitly (in case 2)

Mikhail Lobanov
  • 2,976
  • 9
  • 24