It doesn't matter how many tables a relational join traverses, regardless of whether it contains a GROUP BY clause (or anything else for that matter), all query result sets manifest themselves as one or more rows that contain an identical collection of one or more columns. So, in a way, query results are virtual tables based on underlying physical tables of data. Now imagine if you could take a query statement and define it as a formal database object that could be accessed just as a table can. Well, you can—that is what is called a view. The code defines a database view.

CREATE VIEW vwEmployee
   AS
   SELECT
   SSN,
   CompanyID,
   Firstname,
   Lastname,
   DateOfBirth
   FROM
 Employee

The boldfaced code is just a standard SELECT statement; the rest of the listing simply encapsulates that SELECT statement as a formal, reusable database object—a view— with a formal name. Now, whenever you want to see all employees without their salaries, you can simply perform the following:

SELECT * FROM vwEmployee

Similarly, you can perform more elaborate queries on a view, as if it was a table, as follows:

SELECT
   SSN,
   Firstname + ‘ ‘ + Lastname AS Fullname,
   DateOfBirth
   FROM
   vwEmployee
   WHERE
   DateOfBirth > ‘01/01/1960’
   ORDER BY
   DateOfBirth DESC

In fact, views can be filtered, sorted, and joined just as any physical table can. Views typically hide elements of data so that what is returned exposes only the data that is needed. The elements being hid can be specific columns of a table, rows in that table that do not satisfy a WHERE clause, or a combination of both. Basically, a view masks unneeded data from your application. These masks can be either vertical or horizontal. A vertical mask shields specific columns of a table from returning and is simply the defined collection of underlying table columns that your view contains. A horizontal mask shields specific rows of a table from returning and is simply the WHERE clause that supplies the filtering criteria for the view. You can combine both vertical and horizontal masking in the same view. The code, for example, returns only the SalesOrderID, SaleDate, and OrderTotal columns of only those sales orders with a Status of 20.

CREATE VIEW vwFinishedSalesOrder
   AS
   SELECT
   SalesOrderID,
   SaleDate,
   Total
   FROM
   SalesOrder
   WHERE
 Status = 20

Views don’t stop at a single table. In fact, views are often used to simplify complicated relational joins across multiple tables so that the application developer doesn’t need to concern himself with such complexities and can instead concentrate on simply displaying relevant data. The code defines such a relational view.

CREATE VIEW vwEmployeeCompany
   AS
   SELECT
   e.SSN,
   e.Firstname + ‘ ‘ + e.Lastname AS Fullname,
   e.DateOfBirth,
   c.CompanyName
   FROM
   Employee e INNER JOIN Company c
 ON e.CompanyID = c.CompanyID

The ColdFusion developer now can select all employees born earlier than 1960, hide the salary column, and display the name of the company for which each works, all by simply doing the following:

SELECT * FROM vwEmployeeCompany

Similarly, suppose that you tried to perform a SELECT against this view that included the Salary column, as follows:

SELECT
   SSN,
   Fullname,
   DateOfBirth,
   CompanyName
   FROM
   VwEmployeeCompany
   WHERE
   Salary > 100000

It would fail, because although the Salary column is a part of the underlying Employee table, it is not a part of the vwEmployeeCompany view being queried. So why define views at all? Why not just CFINCLUDE a common CFQUERY call wherever that query is needed? Isn’t that just as good? No, it isn’t. Not by a long shot. You see, if you send a query to your database server, the server goes through the complexities and processing expense of parsing the syntax with which the query is defined, optimizing the query for best performance, and compiling an execution plan. This very expensive process is what happens almost every time that you send a plain query to your database—just so that it can run the query! If you define a view, your database performs the same parse/optimize/compile process on the query for which the view is defined. If you call the view, the database server knows that an appropriate execution plan already exists and executes it directly, thereby eliminating virtually all the overhead necessary to execute the query and, in the bargain, increasing performance. You realize the biggest increases in performance by defining views on very complex relational queries, because these queries are the most difficult and time-consuming to parse and optimize. Another benefit that views afford you is the capability to lock down security on your database. Because a view is a formal database object, you can grant user and group privileges to it just as you would a table or a stored procedure. If fact, to denying direct access to physical tables and granting them only on views and stored procedures is a good practice overall —that way, you are absolutely certain that ColdFusion developers touch only the data that you want them to access. Views are great for easily selecting only that data that you want to access, but they can also be used to insert and update data. This can be both a help and a hindrance, depending on the view.

INSERT INTO vwInventoryItem (
   ItemNumber,
   Description,
   UnitPrice,
   AvailableToSell
   )
   VALUES (
 ‘CAS30-BLK’,

‘30-Minute Cassette, Black Case’,
   1.05,
   100
   )
   UPDATE
   vwInventoryItem
   SET
   Description = ‘30-Minute Cassette, Black Case’,
   UnitPrice = 1.25,
   AvailableToSell = 90
   WHERE
 ItemNumber = ‘CAS30-BLK’

But if the view does not contain all the critical (that is, NOT NULL) columns from its underlying table, you cannot insert new rows into the underlying table through that view, because you cannot supply all the data necessary to create a valid row in that table. The exception to this rule are NOT NULL columns for which default values are defined; these columns are automatically assigned their default values on insert if they’re not part of the view. You can update existing rows through such a view as long as you are setting the values only of those columns defined in the view—but that’s it. Problems appear if you attempt to insert, update, or delete by using relational views, because the action that you think you are performing isn’t always what’s going to happen—and the results can destroy your production data. Our suggestion is that you never attempt to modify data by using relational views and instead define specific views and/or stored procedures for data modification or have application developers insert, update, and delete from the physical tables themselves. If you are interested in performing inserts, updates, and deletes from relational or complicated views, read your database product’s documentation regarding “Instead-Of Triggers,” which replace the code in the triggering statement with code of your own. Although we do touch on Instead-Of Triggers, a discussion of using them to resolve relational view data modification problems is beyond the scope of this article. After you first learned ColdFusion, you performed every call to your database by using a CFQUERY call. CFQUERY is simple and straightforward, so it is popular with developers who just want to get the job done, but another method is more scalable and flexible for manipulating your data: stored procedures. At its most basic level, a stored procedure takes the SQL logic that you would normally write into a CFQUERY call and stores it directly in the database server. This may not sound like a big deal, and it may not even seem that useful at first, but the added efficiency of stored procedures can spell a huge performance gain for your ColdFusion applications. Whenever ColdFusion Server sends a CFQUERY call to your database server, the SQL logic in that CFQUERY call is pulled apart, or parsed, and translated into internal machine language instructions that your database server can directly understand; then these instructions are put through a process that determines the most efficient methods for executing them against the database. After the most efficient methods are decided on, they are assembled into an execution plan that is compiled and executed against the database. With some exceptions, this process happens every time that you send a CFQUERY call to your database server. That’s a lot of work to do just to get ready to execute your SQL logic. A stored procedure eliminates the need to repeat such drudgery every time that it is executed because it stores the compiled execution plan on the database server, where it can be called directly by an outside program—such as your ColdFusion application. And after this procedure is placed into the server’s memory, it remains there until it is pushed out by some other process that needs to run. So if you have sufficient memory, your stored procedure most likely stays cached in memory; this means that your database server doesn’t even need to retrieve the stored procedure object from disk to execute it, resulting in the fastest possible execution of SQL logic. Nice theory, but you need to take a look at the nuts and bolts of how it’s done.The code shows a typical CFQUERY call.

<cfquery name=”GetCompanies”
   datasource=”CFMXBible”>
   SELECT
   CompanyName,
   ZipCode
   FROM
   Company
   WHERE
   State = ‘#Trim(FORM.State)#’
   ORDER BY
   ZipCode ASC
 </cfquery>

To create a stored procedure in Microsoft SQL Server from the SQL logic inside this CFQUERY call, you add the following code and execute it against the database server (by using Query Analyzer or some other batch processing utility).

CREATE PROCEDURE sp_GetCompanies (
   @State CHAR(2)
   )
   AS
 SELECT

CREATE PROCEDURE sp_GetCompanies (
   @State CHAR(2)
   )
   AS
 SELECT

After you execute the code against the database, you have a precompiled stored procedure object, sp_GetCompanies, that you can call from your ColdFusion application.