SQL Server/SQL Views
Introduction SQL Query SQL Stored Procedures SQL Triggers SQL Joins
SQL Views   Indexes BCP (Bulk Copy Program) Table Commands SQL Operators

Please note that the information on this page is related to an older version of Microsoft SQL Server.

Click on SQL Server Tutorials for new training content and material. In addition we have FREE SQL Tutorial videos here, so feel free to download the demos. Our partners also manage two blogs on the following SQL Server Versions:

-SQL Server 2008 Blog
-SQL Azure Blog

Please send any comments or suggestion to 

A view is a virtual table that does not exist physically in the database. You can use views as security mechanisms by granting permission on a view but not on underlying tables. Also the views can perform complicated joins on base tables giving the end user an easier way to look at all the data. The syntax for creating a view is:

CREATE VIEW [owner.]view_name[(column_name [, column_name]...)]


view_name:Is the name of the view. View names must follow the rules for identifiers.

column_name:Is the name to be used for a column in a view. Naming a column in CREATE VIEW is always legal but only necessary when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns could otherwise have the same name (usually because of a join), or when you want to give a column in a view a name different from the column from which it is derived. Column names can also be assigned in the SELECT statement.

ENCRYPTION:Encrypts the syscomments entries that contain the text of the CREATE VIEW statement. This command also hides the view syntax from the user

AS select_statement:Is the SELECT statement that defines the view. It can use more than one table and other views. You must have permission to select from the objects referenced in the SELECT clause of a view you are creating.

WITH CHECK OPTION:Forces all data modification statements executed against the view to adhere to the criteria set within the select_statement defining the view. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data will remain visible through the view after the modification has been committed.

  Example 1:

Lets us look at the following table mrm_patient with five fields and one primary key (sysno_patient)

sysno_patient(PK) Last_ Name First_Name Address Zip_Code
146498 Mitchel John 1223 West Palm Beach Rd 85023
146499 Spencer Teri 2349 S. 76th Street #102 53219
146500 James Taylor 23 N. Atlantic Blvd 76215
146501 Pewinsky Lewis 675 E Indian School Rd 85023
146502 James Sue 3567 E Tatum Blvd 85032

The second table we are interested in is mrm_condensed_history with five fields one primary key (sysno_porder) and one foreign key (sysno_patient)

sysno_porder(PK) sysno_patient(FK) exam_status_ind exam_date sysno_trans
107425 146494 7 Dec 7 1998 13:15PM 7
107426 168314 5 Dec 8 1998 11:30PM 3
107427 135264 6 Jan 21 1998 03:45PM 8
107428 146502 8 Feb 14 1998 01:00PM 9
107428 146500 7 Mar 7 1998 12:15PM 2

If we want to look up information on the patients and their respective exams, we can create a view called mrm_exam. This way the users will not be required to perform a join on the table instead they can just call up the view. Here is the code for mrm_exam

FROM mrm_patient p
JOIN mrm_condensed_history c
ON p.sysno_patient=c.sysno_patient

When the user types the following command

SELECT * FROM mrm_exam

we will give the following result

Last_Name First_Name exam_status_ind exam_date sysno_trans
James Sue 8 Feb 14 1998 01:00PM 9
James Taylor 7 Mar 7 1998 12:15PM 2

  Example 2

This view was created in one of our projects. We have patient data on four tables:

mrm_patient ( 47 fields, 1 primary key "sysno_patient", 0 foreign keys)
mrm_condensed_history ( 10 fields, 1 primary key "sysno_porder", 4 foreign keys)
mrm_report ( 2 fields, 1 primary key "sysno_trans", 0 foreign key)
mrm_gui_exam_type ( 2 fields, 0 primary key, 1 foreign key)

We are interested in creating a view that would combine fields from the above four tables. In order to do that, it is helpful if we can draw the relationships of the tables. Take a look at this picture

As you can see, these tables are connected together by a set of primary and foreign keys. The lines connecting the tables show the inner joins we need to create to bring all the data together. For an explanation of the join, please look at SQL Joins section of the website. After a few tries, we were successful in creating the following view:

p.med_rec_id_patient "mp_med_rec_id_patient",
p.last_name_patient "mp_last_name_patient",
p.first_name_patient "mp_first_name_patient",
p.dob_patient "mp_dob_patient",
p.date_update "mp_date_update",
g.gui_etype_desc "mget_gui_etype_desc",
g.sysno_etype "mget_sysno_etype",
c.sysno_porder "mch_sysno_porder",
c.comp_date "mch_comp_date",
r.sysno_trans "mr_sysno_trans"
r.report_text "mr_report_text"
FROM mrm_patient p
JOIN mrm_condensed_history c
ON p.sysno_patient=c.sysno_patient
JOIN mrm_gui_exam_type g
ON c.sysno_etype=g.sysno_etype
JOIN mrm_report r
ON c.sysno_trans=r.sysno_trans