Using Views in SQL

While working on a recent project, I had a client request database needs where a view was the most optimal solution. In this article, I will go over 'why' and 'when' to use a view in SQL.

Almost all relational database management systems offer some set of components to either add security or increase efficiency. The most widely used of these components are views, stored procedures, and functions. In SQL, a view is a virtual table which can be defined using a SELECT statement. It can reference multiple database tables and other views to encapsulate complex joins and calculations. Views can be reused as many times as needed and are usually called from anywhere you generally call a table - which is pretty much everywhere. Unless you add an index to the view, no data is stored - only the definition of the view is stored in the database. Although it is mostly used for security, it can also be used to help simplify data management tasks. SQL Server, MySQL 5+, Oracle, PostGreSQL, SQLite, and Access all support views.

In what types of situations would you want to use a view? Security is one of the most obvious reason views are used. Users can be restricted to certain views which hide the underlying tables and table structure. Data can also be restricted depending on the user's permissions. By selectively granting permissions on different views, users can be restricted to different subsets of data.

Another reason a view might be used is for query and structural simplicity. Sometimes database access will be given to a user with limited knowledge of the system. Hiding the underlying complexity of multi-table queries and calculations makes a much more presentable set of virtual tables to these types of users.

One of the more obscure reasons to use a view is for logical data independence. Creating an unchanging database schema while refactoring the original schema can be useful during certain development tasks such as legacy code migration. The allows the original application to be functional while different stages of refactoring are ongoing. 

 

Want to Learn More?

This is just a sample of what we can do. We have 15 years of experience working in nearly every technology and industry. Whatever you are doing, we've done it and are prepared to tackle your project. Reach out and we will discuss it with you.