Views

Views are virtual table for easier access to data stored in multiple tables.

Syntax for creating a View:

CREATE VIEW <ViewName> 
AS
…

… but it might be easier to do it in the graphical view designer that are built into SQL Management Studio.

Syntax for using a View:

select * from <MyView> where …

As shown above, we use a VIEW just like we use an ordinary table.

Example:

We use the SCHOOL and CLASS tables as an example for our View. We want to create a View that lists all the existing schools and the belonging classes.

We create the VIEW using the CREATE VIEW command:

CREATE VIEW SchoolView 
AS

SELECT
SCHOOL.SchoolName, CLASS.ClassName 
FROM
SCHOOL
INNER JOIN CLASS ON SCHOOL.SchoolId = CLASS.SchoolId

Note! In order to get information from more than one table, we need to link the tables together using a JOIN.

Using the Graphical Designer

We create the same View using the graphical designer in SQL Server Management Studio:

Step 1: Right-click on the View node and select “New View…”:

Step 2: Add necessary tables:

Step 3: Add Columns, etc.

Step 4: Save the VIEW:

Step 5: Use the VIEW in a query:

select * from SchoolView