Smart Software Solutions Inc 108 S Pierre St.
Pierre, SD 57501
605-222-3403
sales@smartsoftwareinc.com

Contact Us

Articles

Using Views in SQL

Published 1 year ago

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. 

 

AUTHOR Brent Smith

Brent Smith, a graduate of the University of South Dakota, has been with Smart Software Solutions since December of 2007. He holds a Master's Degree in Computer Science and a B.S. in Mathematics.  He currently resides in Pierre, SD with his wife and two sons.  In his spare time he enjoys reading, watching baseball, and playing in a local cover band.