question

Mark avatar image
Mark asked

When is creating a View worthwhile?

When should creating a view be allowed? What do you look for when considering whether a view is necessary and worthwhile? Here is a list that I have so far: - Will creating the view be better than using a function?* - Is the proposed view helpful (e.g., consolidates code and provides a shortcut for developers)? - Is it a well designed view? - Is there enough free drive space for the view? - Would adding a column (or columns) to a table be a better solution? What other factors come into play? This is mainly aimed at DBAs since DBAs can veto the use of a view. Thank you for your answers! ---------- *The view I have in mind is to show each person's title, so a function would have to run once per employee ID, which can be hundreds of times just for one procedure.
database-designview
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
A view doesn't require much space unless its an indexed view. A view will outperform à table valued function unless its inline. An unindexed view or a function is only worth it if you want to encapsule repating code. Indexed views can be used to actually increase performance (something that a function never can) There are some general guidelines you should follow, no matter what you are doing in SQL server. KISS (Keep it simple ...), YAGNI (You ain't gonna need it) and Babysteps. If you follow these guidlines you will probably end up with a faster and easier database to maintain.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

True, I was mainly trying to list all obvious objections to get them out of the way. But I could have skipped that one I suppose.
0 Likes 0 ·
Actually, since inserts and updates are slower with views than with functions, the deifference it time is that the server is writing data for the view - I suppose it's updating an index(es). But a function won't do that. Am I right about that?
0 Likes 0 ·
Inserts will only be slower if its an indexed view.
0 Likes 0 ·
"An unindexed view or a function is only worth it if you want to encapsule repating code." Well said, but at least in the projects I have worked on, this one comes up quite frequently.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
A function would probably be better IMO. This is because of the code re-use you gain. You can use the function to return the title for one person, but create the function as a table function. This would allow you to call either as a single operation for one person, or use it via CROSS APPLY / OUTER APPLY to get the title for a set of people - this performs very fast (as fast as a view). The space consideration is moot, as a view doen't save the data at all, it save the SELECT statement for you only (bytes, not megabytes needed here). Adding a computed column may be better, but if it is going through more than one table, then that wouldn't work. So go for a table function and you have the best of both worlds.
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Avoid functions that arent inline
3 Likes 3 ·
+1000 for @Hakan. Avoid functions that are not inline, and avoid scalar UDFs at all costs!
3 Likes 3 ·
T-SQL Scalar UDFs - yes, avoid. CLR Scalar UDFs - not so much... They're a lot more efficient - but still less efficient than in-line SQL.
3 Likes 3 ·
And I know that you can, in effect, use a function as a table name. That's a big plus (to me anyway). Then what would constitute good reasons for creating views?
1 Like 1 ·
@Everyone - so true (+1). I meant an inline table function of course. I still mess up with the english names, because I learned SQL in German. The syntax is still english, but the discussions were in German - it still catches me out sometimes.
1 Like 1 ·
Show more comments
ozamora avatar image
ozamora answered
Is there any way for the application to cache the needed information at once and refer to it? For example, caching all the employees titles (1 index scan) and then letting the application show based on whats on cache. Reducing roundtrips to the DB is key.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
I recently re-worked a client's database code for graphing (as mentioned in my 'how do i tell a client their database sucks') question - and I used indexed views to great effect there. For the same query it was at least 6x and at most 120x faster for runs from hot (i.e. second and subsequent runs after cache clear down) and at least 9x and at most 200x faster for runs from cold (first run after cache clear). So yeah, indexed views for performance. Just remember that NOEXPAND hint if you're not on enterprise edition...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
A few quick ideas on when you might want to allow views... * You need to abstract the physical implementation of your tables away from the behavior of your database. This allows you to change your physical table structure without being required to modify your queries and also results in at least one view per table. All data access is performed through the views. I have never implemented this, and it sounds way over the top for most implementations, but Gert Drapers has [written][1] on it. * You need to implement complex business rules/constraints that can't be simply declared with the table. I have implemented these in the form of indexed views, and they are also handy to provide constraints on the values within [one-true-lookup-tables][2]. Lucas Jellema [writes][3] on this in PL/SQL, and the principle applies equally well to TSQL. * You need to present a de-normalized picture of your data for reporting users or to facilitate data extract processes. I would suggest that using views to consolidate code or as a developer shortcut would not, by itself, be sufficient reason to create a view. It can lead to unnecessary query overhead (through unneeded JOINs and column selections) and associated performance degradation. I've spent some quality time performance tuning and debugging views that were being used, and re-used, in manners not consistent with their original intent. Jen McCown [writes][4] briefly on this. [1]: http://blogs.msdn.com/b/gertd/archive/2007/07/11/table-structure-changes.aspx [2]: http://www.projectdmx.com/dbdesign/lookup.aspx [3]: http://technology.amis.nl/blog/475/introducing-materialized-views-as-mechanism-for-business-rule-implementation-complex-declarative-constraints [4]: http://sqlserverpedia.com/blog/sql-server-bloggers/proving-views-can-hurt-performance/**
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
As Hakan mentions, the first and main use of a view is to provide code reuse. If you or your developpers are very frequently using exactly the same complex query or complex part of a much larger query then it makes sense to create a view. I often create views to hide many-to-many joins for instance. Another, less common, use of a view is as a piece of security plan. For instance, if you want to allow access to most, but not all of a table, you can chose to grant access to a tailored view instead. Finally, using *indexed* views can provide major performance enhancements in some cases as Matt points out, but you must consider the trade offs involved including the use of noexpand where appropriate. I previously wrote about that use of views at: [ http://www.sqlservercentral.com/articles/63963/][1] [1]: http://www.sqlservercentral.com/articles/63963/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.