In the previous chapters, base tables were used to describe DDL and DML statements. A base table contains data stored on the disk. By contrast, views, by default, do not exist physically—that is, their content is not stored on the disk. (This is not true for so-called indexed views, which are discussed later in this […]Read More...
The Transact-SQL language supports the nonstandard ALTER VIEW statement, which is used to modify the definition of the view query. The syntax of ALTER VIEW is analogous to that of the CREATE VIEW statement. You can use the ALTER VIEW statement to avoid reassigning existing privileges for the view. Also, altering an existing view using […]Read More...
A view is used exactly like any base table of a database. You can think of selecting from a view as if the statement were transformed into an equivalent operation on the underlying base table(s). Example 11.9 shows this. Example 11.9
CREATE VIEW v_d2
AS SELECT emp_no, emp_lname
WHERE dept_no ='d2';
WHERE emp_lname LIKE 'J%';
The result is: The SELECT statement in Example 11.9 is transformed into the […]Read More...
UPDATE Statement and a View A view can be used with the UPDATE statement as if it were a base table. When a view is used to modify rows, the content of the underlying base table is actually modified. Example 11.14 creates a view that is then used to modify the works_on table. Example 11.14 […]Read More...
As you already know from the previous chapter, there are several special index types. One of them is the indexed view, which will be described next. A view always contains a query that acts as a filter. Without indices created for a particular view, the Database Engine builds dynamically the result set from each query […]Read More...
You can use the sessionproperty property function to test whether one of the options of the SET statement is activated (see the earlier section “Creating an Indexed View” for a list of the options). If the function returns 1, the setting is ON. Example 11.22 shows the use of the function to check how the […]Read More...