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 info@sqlserver2008tutorial.com

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]...)]
[WITH ENCRYPTION] AS
select_statement
[WITH CHECK OPTION]

where

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)

  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:




CREATE VIEW mrm_data AS
SELECT
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