Chapter - Views

DDL Statements and Views – Creating a View – SQL Server 2012

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 […]

Altering and Removing Views – SQL Server 2012

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 […]

View Retrieval, INSERT Statement and a View – SQL Server 2012

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

USE sample;
GO
CREATE VIEW v_d2
	AS SELECT emp_no, emp_lname
		FROM employee
		WHERE dept_no ='d2';
GO
SELECT emp_lname
	FROM v_d2
	WHERE emp_lname LIKE 'J%';

USE sample;

CREATE VIEW v_d2

AS SELECT emp_no, emp_lname

FROM employee

WHERE dept_no ='d2';

SELECT emp_lname

FROM v_d2

WHERE emp_lname LIKE 'J%';

The result is: The SELECT statement in Example 11.9 is transformed into the […]

UPDATE Statement, DELETE Statement and Views – SQL Server 2012

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 […]

Indexed Views – Creating an Indexed View – SQL Server 2012

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 […]

Editing Information Concerning Indexed Views – SQL Server 2012

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 […]