1112 11th Chapter View 12th cursor
11th Chapter ViewThe view can contain up to 1024 columns, and the select query cannot contain the COMPUTE clause, the COMPUTE by clause, or the INTO keyword when creating the view, except that the ORDER by clause is not available unless the select contains a top or for XML clause; The query cannot contain the OPTION clause for the specified query hint, nor can it contain the TABLESAMPLE clause. 1. Indexed view (materialized view) must create a unique clustered index P296 when you create an indexed view, you cannot reference other views, you can reference only the base table, and the base table must be in the same database as the CREATE view, and the owner must specify SCHEMABINDING when creating the view. It is also required that the ANSI_NULLS and Quoted_identifer connection options should be on, the first one created for the view so in must be a unique clustered index, and subsequent indexes can be either a fly-clustered index or unique. CREATE VIEW Vw_indexdemo with SCHEMABINDING as--must be coupled with schema binding select A,b,cfrom Dbo.tab1where a between 0 and 100CREATE UNIQUE CL Ustered index INDEX_VW on Vw_indexdemo (a)--create a unique clustered index
B. To prevent the modification of the base table from affecting the structure of the view, you can use the SCHEMABINDING clause to bind the view to the schema of the underlying table. After you specify schemabinding, to modify the base table columns or other objects that affect the view structure, you must first modify or delete the view definition itself to contact this dependency. When using schemabinding, the query statement must use two-part names, all referenced objects must be within the same database, and * is not allowed in the SELECT list. CREATE VIEW Dbo.vwavoidmodi with shemabinding--plus the schema name, with schema bindings. As SELECT col1, col2, col3 from dbo. Tab----can not use *, to add the schema name after this, to modify the base table of the view, will report an error! Can effectively prevent the base table from being modified.
C. P 299 update the data in the view focus: Through the view to modify the base table, if the view is a Do Zhangki table Association generated, then the update is through the associated on the back of the condition to find the data in the base table can match to update, it is possible to update the column, because the association when not only associated with a piece of data 。 What's more, it's likely that you'll lose the line as it's updated. p301-302 to prevent the loss of rows, to ensure that data is still visible through the view after the data has been modified, you should use the CHECK option option in the view, example: CREATE view Dbo.vcsasselect c.classid, C. ClassName ... FROM dbo. Student as SON c.classid = S.classidwith CHECK OPTION; Rename: sp_rename ",", ";
12th cursor DECLARE cursors do not generate a result set when the open cursor is executed to generate @ @FETCH_STATUS-0 FETCH Success-1 F Etch failed or the row is not in the result set--2 indicates that the fetched row does not exist. Fast Forward-only cursors, scrollable cursors
For Read Only---A read-only cursor for UPDATE--updatable cursor scroll or for update cannot specify Fast_forward P306 FETCH statement positional keyword static cursor, dynamic cursor, keyset-driven cursor Cursors are dynamic by default, and when a scrolling cursor fetches rows, the cursor reflects the most recent changes made in the result set, and to establish a static cursor, you can add the INSENSITIVE keyword after the cursor name. However, this keyword is not used in time, and when a cursor's SELECT statement has distinct, union,group by or having keyword, the way the cursor is handled is the same as the specified insensitive keyword, when the result set is aggregated ... p309-310 updating data with updatable cursors ...11th Chapter ViewThe view can contain up to 1024 columns, and the select query cannot contain the COMPUTE clause, the COMPUTE by clause, or the INTO keyword when creating the view, except that the ORDER by clause is not available unless the select contains a top or for XML clause; The query cannot contain the OPTION clause for the specified query hint, nor can it contain the TABLESAMPLE clause. 1. Indexed view (materialized view) must create a unique clustered index P296 when you create an indexed view, you cannot reference other views, you can reference only the base table, and the base table must be in the same database as the CREATE view, and the owner must specify SCHEMABINDING when creating the view. It is also required that the ANSI_NULLS and Quoted_identifer connection options should be on, the first one created for the view so in must be a unique clustered index, and subsequent indexes can be either a fly-clustered index or unique. CREATE VIEW Vw_indexdemo with SCHEMABINDING as--must be coupled with schema binding select A,b,cfrom Dbo.tab1where a between 0 and 100CREATE UNIQUE CL Ustered index INDEX_VW on Vw_indexdemo (a)--create a unique clustered index
B. To prevent the modification of the base table from affecting the structure of the view, you can use the SCHEMABINDING clause to bind the view to the schema of the underlying table. After you specify schemabinding, to modify the base table columns or other objects that affect the view structure, you must first modify or delete the view definition itself to contact this dependency. When using schemabinding, the query statement must use two-part names, all referenced objects must be within the same database, and * is not allowed in the SELECT list. CREATE VIEW Dbo.vwavoidmodi with shemabinding--plus the schema name, with schema bindings. As SELECT col1, col2, col3 from dbo. Tab----can not use *, to add the schema name after this, to modify the base table of the view, will report an error! Can effectively prevent the base table from being modified.
C. P 299 update the data in the view focus: Through the view to modify the base table, if the view is a Do Zhangki table Association generated, then the update is through the associated on the back of the condition to find the data in the base table can match to update, it is possible to update the column, because the association when not only associated with a piece of data 。 What's more, it's likely that you'll lose the line as it's updated. p301-302 to prevent the loss of rows, to ensure that data is still visible through the view after the data has been modified, you should use the CHECK option option in the view, example: CREATE view Dbo.vcsasselect c.classid, C. ClassName ... FROM dbo. Student as SON c.classid = S.classidwith CHECK OPTION; Rename: sp_rename ",", ";
12th cursor DECLARE cursors do not generate a result set when the open cursor is executed to generate @ @FETCH_STATUS-0 FETCH Success-1 F Etch failed or the row is not in the result set--2 indicates that the fetched row does not exist. Fast Forward-only cursors, scrollable cursors
For Read Only---A read-only cursor for UPDATE--updatable cursor scroll or for update cannot specify Fast_forward P306 FETCH statement positional keyword static cursor, dynamic cursor, keyset-driven cursor Cursors are dynamic by default, and when a scrolling cursor fetches rows, the cursor reflects the most recent changes made in the result set, and to establish a static cursor, you can add the INSENSITIVE keyword after the cursor name. However, this keyword is not used in time, and when a cursor's SELECT statement has distinct, union,group by or having keyword, the way the cursor is handled is the same as the specified insensitive keyword, when the result set is aggregated ... p309-310 updating data with updatable cursors ... Original link This article by Bean John Blog Backup expert remote One click release
1112 11th Chapter View 12th cursor (reprint)
