SQL Server 2014 - Views

A brief overview of SQL Server views and how to create them.

In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table. Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table.

Benefits of Views

A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all of the data). Views can do the following:

Restrict access to specific rows in a table
Restrict access to specific columns in a table
Join columns from multiple tables and present them as though they are part of a single table
Present aggregate information (such as the results of the COUNT function)

View Syntax

You create a view by using the CREATE VIEW statement, followed by the SELECT statement.

Create a View

We previously used the Query Designer to create a query that selected data from two tables. Let's now take that query and save it as a view called "ToDoList". Basically, all we need to do is put CREATE VIEW ToDoList AS in front of the query, like this:

Once you've run the script, refresh the Views folder in the left pane, and you'll see your view sitting there in the left pane:

Running a View

So now that you've created the view, you can view the results simply by selecting it like you would select any table. So instead of typing out the big SELECT statement with the INNER JOIN etc etc, you can simply type select * from todolist and it will run the full query:

You can also right-click on the view and select "Select Top 1000 Rows".

Data Updates

The view will return up to date data. If the data in the table changes, the results of the view will change too. So if you were to add a new task with a status of "To Do", next time you run the view, it will include the new record in the result set.

Modifing a View

You can modify an existing view by using using ALTER instead of CREATE.

So if we wanted to change the view to use the StatusName field instead of StatusId, we could do this:

You can also right click on the view and select Design to modify your view using the Query Designer.

As you can see, views allow you to save your queries so that you can run them again simply by doing a SELECT on them. But they do have their limitations. They allow you to select data but they don't allow you to perform any business logic, such as conditional statements etc. To do that, you will need a stored procedure.