Views

Sorry the information on this page is outdated as it relates to older versions of SQL Server.

We have new content and free videos at this location, http://www.learningcomputer.com/id29.htm Please note that rest of the SQL Server videos can be bought from this page Buy Tutorials. Please send any comments or suggestion to kashi@learningcomputer.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)

mrm_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)

mrm_condensed_history

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

CREATE VIEW mrm_exam AS
SELECT
p.Last_Name,p.First_Name,c.exam_status_ind,c.exam_date,c.sysno_trans
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:

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

Back to homepage