am still single dating advice - Updating a view in sql server

To ensure that the alteration does not affect the view or other objects that depend on it, you can explicitly recompile a view after altering one of its base tables.

updating a view in sql server-62

EMPNO ENAME DEPTNO DNAME LOC ---------- ---------- --------- -------------- ---- 7782 CLARK 10 ACCOUNTING NEW YORK 7839 KING 10 ACCOUNTING NEW YORK 7934 MILLER 10 ACCOUNTING NEW YORK 7369 SMITH 20 RESEARCH DALLAS 7876 ADAMS 20 RESEARCH DALLAS 7902 FORD 20 RESEARCH DALLAS 7788 SCOTT 20 RESEARCH DALLAS 7566 JONES 20 RESEARCH DALLAS 7499 ALLEN 30 SALES CHICAGO 7698 BLAKE 30 SALES CHICAGO 7654 MARTIN 30 SALES CHICAGO 7900 JAMES 30 SALES CHICAGO 7844 TURNER 30 SALES CHICAGO 7521 WARD 30 SALES CHICAGO OPERATIONS BOSTON 15 rows selected. In the case of views containing an outer join on other nested views, a table is key preserved if the view or views containing the table are merged into their outer views, all the way to the top.

A view which is being outer-joined is currently merged only if it is "simple." For example: OWNER TABLE_NAME COLUMN_NAM UPD ---------- ---------- ---------- --- SCOTT EMP_DEPT_V EMPNO NO SCOTT EMP_DEPT_V ENAME NO SCOTT EMP_DEPT_V DEPTNO NO SCOTT EMP_DEPT_V DNAME NO SCOTT EMP_DEPT_V LOC NO 5 rows selected.

Requesting a "estimated" plan does not involve executing the query, naturally.

If you get an error, either the view is not logically updatable, or the query processor can't tell that it is.

Note: It is not necessary that the key or keys of a table be selected for it to be key preserved.

It is sufficient that if the key or keys were selected, then they would also be keys of the result of the join.

In this case, the view is considered "created with errors." For example, when a view is created that refers to a nonexistent table or an invalid column of an existing table, or when the view owner does not have the required privileges, the view can be created anyway and entered into the data dictionary. To create a view with errors, you must include the .

When you try to create such a view, the database returns a message indicating the view was created with errors.

Typically, it does a good job with this, so the update view plan may look very similar to a plan for a simple update to the single affected base table. Then read this blog I wrote recently to see how it could be affected.

Tags: , ,