Views are preset data sets that you can use to query data you commonly query in your stored procedures or SQL statements. Views are used as tables when you create your programs, but they are not physical tables. You can think of a view as a virtual table. Views are combined data that you query often, so the database indexes and stores these views for faster processing. Views are a common part of database programming, and they are useful when you find that you are typing the same SQL statement often.
The Concept of a View Versus a Table
Again, views are data sets that return records in the same way a SELECT statement returns records. As a matter of fact, views are stored SELECT statements that return data more quickly than running the same SQL statement dozens of times.
When you use a view in your stored procedure, you query it as if it was a table. The data is a snapshot of the SELECT queries you build. Usually, these SELECT queries are large and complex. Using a view saves much of your time and eliminates any errors since you can build the query, check for bugs, and only write logic once.
This article uses the Customer and Order tables below for examples.
Customer
|
CustomerId |
First_name |
Last_name |
City |
State |
|
321 |
Frank |
Loe |
Dallas |
TX |
|
455 |
Ed |
Thompson |
Atlanta |
GA |
|
456 |
Ed |
Thompson |
Atlanta |
GA |
|
457 |
Joe |
Smith |
Miami |
FL |
|
458 |
Frank |
Doe |
Dallas |
TX |
Order
|
OrderId |
CustomerId |
Total |
OrderDate |
|
1 |
321 |
10 |
1/2/2014 |
|
2 |
455 |
40 |
3/2/2014 |
|
3 |
456 |
20 |
3/10/2014 |
One issue with views that you should know before building one is that views cannot take arguments. This means that the query is static and cannot take parameters like you can with a WHERE clause. You can use a WHERE clause in your views, but you cannot pass dynamic values to them.
Building a View
One of the difficult tasks for developers is knowing when to use a view versus just building a new SQL statement. Since a view is assigned a clustered index, a view is beneficial if you have similar queries across stored procedures that could be transferred into a table.
Let's say that you have several stored procedures that query a list of customers with associated orders. You know how to JOIN tables, and you even know how to create a SELECT statement that gives you the right data set. The following SELECT statement is the code you use to query your results.
SELECT c.CustomerId, First_Name, Last_Name, OrderId
FROM Customer c JOIN Order o ON c.CustomerId=o.CustomerId
The above query returns the following data set.
|
CustomerId |
First_name |
Last_name |
OrderId |
|
321 |
Frank |
Loe |
1 |
|
455 |
Ed |
Thompson |
2 |
|
456 |
Ed |
Thompson |
3 |
The above is just a SELECT query, but you can then turn it into a view. The following SQL statement creates the view.
CREATE VIEW CustomerOrders
AS
SELECT c.CustomerId, First_Name, Last_Name, OrderId
FROM Customer c JOIN Order o ON c.CustomerId=o.CustomerId
The view name is how you query your view. The view returns the same data set as the above list except now you can use it in the same way you query a table such as the following SQL statement.
SELECT * FROM CustomerOrders
The above statement returns the same data set as the raw SELECT statement, but you also have the option to return only a subset of columns. Maybe you want to return only the CustomerId from your view. The following SQL code gets the CustomerId from your view.
SELECT CustomerId FROM CustomerOrders
Just like a standard SELECT statement, the view will search based on the SELECT statement created in the view and then return a list of CustomerId column values.
Filtering View Records
Remember we said that you can add a WHERE clause to any SELECT statement, and that's true of your views and querying views. As you can imagine, if you don't filter your views, a customer and order view can return potentially millions of records. You probably don't need millions of records for your reports, so you can use the WHERE clause.
You can use the WHERE clause to build static views. For instance, suppose you query customers in Texas often. You could create a view that retrieved orders from customers in Texas. The following SQL statement is an example.
CREATE VIEW CustomerOrders
AS
SELECT c.CustomerId, First_Name, Last_Name, OrderId
FROM Customer c JOIN Order o ON c.CustomerId=o.CustomerId
WHERE c.State = ‘tx'
When you use the above statement, you only retrieve customers in Texas. When you create a SELECT statement on this view, you will never receive customers outside of Texas. Once again, remember that you can't pass parameters to a view, so you always have a static WHERE clause when you work with views.
However, there is a way to get around the limitation. You can add a WHERE clause in your SELECT statement that calls the view. The following statement allows you to add parameters to your view.
SELECT CustomerId FROM CustomerOrders
WHERE State = ‘tx'
Now, instead of limiting the view query to just customers in Texas, you use the view to return all values from your view's SELECT statement and then filter the view's data set.
Just like tables, you can also ALTER a view's structure. For instance, suppose you want to add the Product table to the view. The following SQL statement changes the view and adds the Product table.
ALTER VIEW CustomerOrders
SELECT c.CustomerId, First_Name, Last_Name, OrderId
FROM Customer c JOIN Order o ON c.CustomerId=o.CustomerId
JOIN Product p ON o.OrderId = p.OrderId
This is the first time you've seen multiple JOIN phrases in a SELECT statement. The above statement selects all customers with orders and the order's associated product. You can keep adding tables to your SELECT statement with multiple JOIN statements as long as you have an associated table with linked columns.
Finally, you can also delete views in the same way you delete a table. The DROP statement is again used. If you recall, the DROP statement was used to delete a table column or an entire table. The DROP statement is also used to delete a view.
The following DROP statement removes the view we just created.
DROP VIEW CustomerOrders
Once again, the SQL language does not warn you or give you any type of confirmation. If you run the above statement, the view is deleted and you must rebuild it if you make a mistake. You could retrieve the view from your backups, but it takes a lot of time to retrieve data from a backup, especially when you must search for one structure among multiple other structures in your database design.
