MG Computer Group / How To Articles

Ready to Run?  Microsoft Partner  A+ Certified  Custom Code  Database Integration More Info

Give Us a Try! View Our Portfolio

A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in SQL statements the same way a table is referenced.

A view is used to do any or all of these functions:

Restrict a user to specific rows in a table. For example, allow an employee to see only the rows recording his or her work in a labor-tracking table.

Restrict a user to specific columns.

For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.

Join columns from multiple tables so that they look like a single table.

Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.

Views are created by defining the SELECT statement that retrieves the data to be presented by the view. The data tables referenced by the SELECT statement are known as the base tables for the view. In this example, vContacts selects data from two base tables to present a virtual table of commonly needed data:

CREATE VIEW vContacts

AS

SELECT c.CompanyName, t.ContactName, t.ContactEmail,CompanyID,ContactID

FROM Company c, Contact t

where t.CompanyID=o.CompanyID

You can then reference vContacts in statements in the same way you would reference a table:

SELECT *

FROM vContacts

Views can be used to partition data across multiple databases.

Views can be updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view or if database supports INSTEAD OF triggers on VIEWS.

INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. Also, partitioned views support INSERT, UPDATE, and DELETE statements that modify multiple member tables referenced by the view.

example:

CREATE TRIGGER iInsteadTrigger on vContacts

INSTEAD OF INSERT

AS

BEGIN

INSERT INTO Company (CompanyID, CompanyName)

SELECT CompanyID, CompanyName

FROM inserted

INSERT INTO Contact(CompanyID,ContactID,ContactName,ContactEmail)

SELECT CompanyID,ContactID,ContactName,ContactEmail

FROM inserted

END

View from this example can be used with GeniusConnect.

PrimaryKey will be CompanyID,ContactID.

SQL Statements for generating new id's depends on datatype.

Examples:

select newid()

select max(CompanyID)+1 from Company

select dbo.myfunctionToGetContactID..