A database view is a searchable object in a database that is defined by a query.  Though a view doesn’t store data, some refer to a views as “virtual tables,” you can query a view like you can a table.  A view can combine data from two or more table, using joins, and also just contain a subset of information.  This makes them convenient to abstract, or hide, complicated queries.

Below is a visual depiction of a view:

SQL View

How to Create and Use a Database View

A view is created from a query using the CREATE VIEW command.  In the example below we are creating a PopularBooks view based of a query which selects all Books that have the IsPopular field checked.  The Query is colored in Blue.

SELECT ISBN, Title, Author, PublishDate
FROM Books
WHERE IsPopular = 1

(Note:  This example won’t run in the sample database)

Once a view is created you can use them as you would any table in a SELECT statement.  For example, to list all the popular book titles ordered by the author you could write:

SELECT Author, Title
FROM PopularBooks

In general, you can use any of the SELECT clauses, such as GROUP BY, in a select statement containing a view.

Benefits of a Database View

There are many benefits to using views.  Listed below are some of the one that comes to mind:

  • Enforce Business Rules – Use views to define business rules, such as when an item is active, or what is meant by “popular.”  By placing complicated or misunderstood business logic into the view, you can be sure to present a unified portrayal of the data.  This increases use and quality.
  • Consistency – Simplify complicated query logic and calculations by hiding it behind the view’s definition.  Once defined they calculations are reference from the view rather than being restated in separate queries.  This makes for fewer mistakes and easier maintenance of code.
  • Security – Restrict access to a table, yet allow users to access non-confidential data via views.  For example, you can restrict access to the employee table, that contains social security numbers, but allow access to a view containing the name and phone number.
  • Simplicity – Databases with many tables possess complex relationships, which can be difficult to navigate if you aren’t comfortable using Joins.  Use views to provide a “flattened” view of the database for reporting or ad-hoc queries.
  • Space – Views take up very little space, as the data is stored once in the source table.  Some DBMS all you to create an index on a view, so in some cases views do take up more space than the definition.

Disadvantages of Views

  • Performance – What may seem like a simple query against a view could turn out to be a hugely complex job for the database engine.  That is because each time a view is referenced, the query used to define it, is rerun.
  • Modifications – Not all views support INSERT, UPDATE, or DELETE operations.  In general, in order to support these operations, the primary key and required fields must be present in the view.  Complex multi-table views are generally read-only.

I hope this helped you understand what are views and why we use them in databases.  Remember!  I want to remind you all that if you have other questions you want to be answered, then post a comment or tweet me.  I’m here to help you.

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}


    Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

    But it doesn't have to be this way.

    I'm Putting together a free email course to help you get started learning SQL Server.

    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b6728":{"name":"Main Accent","parent":-1},"03296":{"name":"Accent Low Opacity","parent":"b6728"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"b6728":{"val":"var(--tcb-skin-color-0)"},"03296":{"val":"rgba(17, 72, 95, 0.5)","hsl_parent_dependency":{"h":198,"l":0.22,"s":0.7}}},"gradients":[]},"original":{"colors":{"b6728":{"val":"rgb(47, 138, 229)","hsl":{"h":210,"s":0.77,"l":0.54,"a":1}},"03296":{"val":"rgba(47, 138, 229, 0.5)","hsl_parent_dependency":{"h":210,"s":0.77,"l":0.54,"a":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"dffbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"dffbe":{"val":"var(--tcb-color-4)"}},"gradients":[]},"original":{"colors":{"dffbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
    Sign Up