In this lesson you'll learn how to create, use, modify and delete SQL server view objects.

If you’ve been following the lessons up until now, you’ve probably noticed that we tend to write the same code many times. This is especially truth with JOINS. How many times have we joined the Classes table to Students or Teachers? Isn’t it all a little redundant?

You’re right. If you are constantly writing the same code over and over in any programming language, you’re probably doing something wrong or not taking advantage of the language’s full functionality. Although repeating JOINS is an inescapable part of writing SQL code, we can minimize the number of times that we create the exact same joins by implementing a view.

What Is A View?

A view is an amazing little tool that encapsulates a bunch of SQL and allows us to call it as if it were a real table. The most amazing thing about views is that they are still tied to the actual tables. Any changes made to the tables behind the scenes are reflected in results from the view as well. You can even make updates on some views.

Here’s a quick recap of when it’s best to use a view:

  • Data will be used regularly by many processes and users.
  • You wish to hide certain columns from any results for security reasons.
  • You want to be able to update actual tables by updating the subset of data.

Let’s take a look at a scenario where a view would make sense. Take another look at the example schema from the previous lesson:

SQL View

In almost any SQL we write for this application, we’re going to be joining these three tables together. They don’t do much on their own. They are basically just simple lists until we connect them. The following SQL will probably be needed in many places in our application.

SELECT *
FROM Store
INNER JOIN StoreProduct
ON Store.StoreID = StoreProduct.StoreID
INNER JOIN Product
ON Product.ProductID = StoreProduct.ProductID

In many parts of a web application, we won’t need to know what the Primary Key of the StoreProduct table record is. In fact, we don’t really need to see anything from that table. We just need it to connect the string values stored in Product and Store. For that reason, we waste time and space returning everything from this table. Although we’ll probably add WHERE clauses, ORDER BY and other bits to this basic set of JOINS, the joining statements themselves don’t need to change. This is a perfect opportunity to wrap things into a view.

Creating A View

A view represents a new piece of the schema. That means it’s something rather permanent that we’re adding to our database for long-term use. Here’s how we do it.

Syntax:

CREATE VIEW [viewname]
AS
[any select sql statement]

Example:

CREATE VIEW vProductsInStore
AS

SELECT Store.StoreID, StoreName, Product.ProductID, ProductName
FROM Store
INNER JOIN StoreProduct
ON Store.StoreID = StoreProduct.StoreID
INNER JOIN Product
ON Product.ProductID = StoreProduct.ProductID

We’ve now created a new object that we can use just like a table called “vProductsInStore.” Try it out:

SELECT * FROM vProductsInStore
SQL View

Now we can use the view anywhere that we normally would. We can join it to other tables or filter it using a WHERE clause. The options are limitless. Here’s a quick rundown of ways to use a view.

  • Compact a commonly-used join into a single view to be used in many scenarios.
  • Create a subset of data by adding a WHERE clause to a table or series of tables.
  • Create a subset of visible data by narrowing down the SELECT statement’s returned fields list.
  • Create an easier-to-remember name for a long table name.

Changing or Deleting a View

If you decide you’d like to modify a view later, doing so is easy. Just take the exact same code you already used and change the CREATE statement to ALTER. This tells SQL server that you aren’t creating something new, but you’re replacing something that already exists.

ALTER VIEW vProductsInStore
AS

SELECT Store.StoreID, StoreName, Product.ProductID, ProductName
FROM Store
INNER JOIN StoreProduct
ON Store.StoreID = StoreProduct.StoreID
INNER JOIN Product
ON Product.ProductID = StoreProduct.ProductID

Getting rid of a view is just like getting rid of a table. Just use the following statement:

DROP VIEW vProductsInStore

Again, this is a permanent change. Your view SQL will be gone once you drop it from the system.

As you can see, views give us a lot of powerful flexibility. It is a good practice to use views to save you from writing lots of redundant SQL. One of the best benefits of views is that, if your database schema changes, you can adjust the view in only one place and the rest of your system will automatically know about the change.

Categories : SQL
Tags : Basic SQL


author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories