Make your job easier while adding an extra layer of security and increasing performance with SQL Server Views.
Try Performance Advisor for free for 15 days and save even more time!
“This utility has transcended our core monitoring and alerting functionality at the shop. The customization and report ability is second to none, and allows us an in-depth look enterprise wide at our systems.”
Chris Yates, Assistant Vice President | Database Administration Manager
Views can be a valuable device for DBAs and IT professionals, allowing you to obscure complex schemas, implement security, and even improve performance. You can think of a view as a virtual table; in most cases, the view does not actually persist any data, so there is practically zero storage cost. The view just serves as a template for retrieving data from the underlying table or tables (and for updating the underlying data in some cases).
Views can allow you to join multiple tables and return the data as if it were coming from a single source, which can simplify query logic for users running ad-hoc reporting. You can also use a view to restrict the data visible to end users, by excluding columns from the view, or applying filters to exclude rows. You can implement indexed views to aid in the performance of aggregation queries by calculating those aggregates in advance, and distributed partitioned views to separate data onto different servers – spreading out both the read and write portions of the workload.
A view is usually described as a virtual table because the database doesn’t store the view data. When the data is retrieved from a view, the database engine recreates the data with a SELECT statement in the view’s definition. Because the database only stores the definition of the view and not the data, there isn’t any significant cost in space for utilizing a view. Views can also reference other views.
You may be asking yourself when you should use a view. Whenever you are going to create a new view you need to have a particular goal in mind. There are many scenarios where a view will be a good solution for what you are trying to accomplish. You may want to hide the complexities of underlying database schema, or even customize the data and schema for a particular set of users. Sometimes, you may wish to control access to rows and columns of data. Other times, you may just want to aggregate data for performance reasons.
Views are extremely useful to have in your database toolbox. SQL Server views are more compact than functions, more malleable than stored procedures; they can give your database an added layer of security, better performance, and they’re easier to use. Give SQL Server views a try in your day to day routine and see what you can simplify and secure in your database.
For your convenience here are links to supported browsers:
Your browser could be incompatible with this website.
If you close this window and continue you may experience less than optimal behavior.