Home

Programmatically Creating and Using a View

 
       

Creating a View

To programmatically create a view, you use the following SQL syntax:

CREATE VIEW ViewName
AS
SELECT Statement

If you are using Microsoft SQL Server Management Studio, it can generate skeleton code of a view for you. To use it, first create an empty query window. Display the Template Explorer. In the Template Explorer, expand the View node. From the View node, drag Create View and drop it in the query window.

The creation of a view starts with the CREATE VIEW expression followed by a name. The name of a view follows the rules and suggestions we reviewed for view names. After the name of the view, use the AS keyword to indicate that you are ready to define the view.

Because a view is primarily a SQL statement, it is defined using a SELECT statement, using the same rules we studied for data analysis. Here is an example of a view:

CREATE VIEW dbo.ListOfMen
AS
SELECT dbo.Sexes.Sex,
       dbo.Persons.FirstName, dbo.Persons.LastName
FROM   dbo.G}ers INNER JOIN dbo.Persons
ON     dbo.Sexes.SexID = dbo.Persons.SexID
WHERE  (dbo.Sexes.Sex = 'Male');
GO

After creating the SQL statement that defines the view, you must execute the statement. If using a query window in Microsoft SQL Server Management Studio, you can do this by pressing F5. Once the statement is executed, its name is automatically added to the Views node of its database even if you do not save its code.

Executing a View

After creating a view, it shares many of the characteristics of a table. For example, a view has its own columns although the columns are actually tied to the table(s) that hold(s) the original data. Treated as a table, you can access the columns of a view using a SELECT statement. This means that you can access one, a few, or all of the columns. Here is an example that accesses all columns of a view:

SELECT PayrollPreparation.* FROM PayrollPreparation; 
 

Previous Copyright © 2008-2010 FunctionX, Inc. Next