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.
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.
"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.
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.
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.
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.
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...
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/**
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/
