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...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 […]
Read More...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
1 2 3 4 5 6 7 8 9 10 | 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%'; |
The result is: The SELECT statement in Example 11.9 is transformed into the […]
Read More...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 […]
Read More...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 […]
Read More...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 […]
Read More...