“The more that I use SQL Sentry, the more I fall in love with it. #SQLSentry #LoveStory”
John Morehouse, Consultant at Denny Cherry & Associates
Views can be a valuable device for DBAs and IT professionals, allowing them 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 the 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.
Learn more about SentryOne and visit the product overview page for our industry-leading monitoring, diagnosing, and optimizing tools for the Microsoft Data Platform.