SQL Views

I've briefly spoken about SQL Views in prior posts (see here), so I wanted to take this opportunity to talk a little more about SQL Views.

First, what is a SQL view?

A view is just exactly what it sounds like: it allows you to "view" information.  Views can and are usually created to present data in a certain way.  It is what we may call a virtual table, meaning that the table doesn't actually reside in the database the way it is presented in the view.

A view allows you to take a T-SQL query, spanning across multiple tables if needed, and present it as a view.  Then, someone can write a query using the view and get all the data they need.  This helps to avoid writing the same query multiple times.  The query is already written as a view.

An example might help us better understand the concept.  Let's say that we have three different tables: Customer, Orders and OrderDetails.  We use the following query to get information for an order:

SELECT *
FROM Customer
join Orders on Customer.CustomerId = Orders.CustomerId
join OrderDetails on Orders.OrderId = OrderDetails.OrderId

It can sometimes be difficult to remember the relationship between each table, so instead of creating the same query over and over again, we create a view.  We create a view using the following syntax:

CREATE VIEW [dbo].[vOrderDetail]

AS

SELECT *
FROM Customer
join Orders on Customer.CustomerId = Orders.CustomerId
join OrderDetails on Orders.OrderId = OrderDetails.OrderId

Now, when you want to get the information for a customer order, your query can look like this:

SELECT *
FROM vOrderDetail

To avoid confusion, it is a good practice to name your views differently from your tables.  In my example, I put a lowercase "v" at the beginning of the view name so I know I am using a view.

Once you've created your view, now you can give people access to views.  These are used for just viewing data, users can't update or delete these records.  The deletion or updating of records has to be done on the tables the view refers to.

To read more on views, go here: SQL Views