1.3 SQL Server 2000 View ManagementA view is a table exported from one or more tables or views. Its structure and data are based on table queries. Like a real table, a view also contains several defined data columns and multiple data rows. However, in essence, these data columns and data rows come from the referenced table. Therefore, a view is not a basic table that actually exists, but a virtual table. The data displayed in the view is not stored in the database in the view structure, but in the table referenced by the view. The following describes how to create, modify, and delete a view.
1.3.1 create a viewNote the following when creating a view. ● Views can only be created in the current database. ● If a base table or view referenced by a view is deleted, the view cannot be used until a new base table or view is created. ● If a column in the view is the same as a function, mathematical expression, constant, or column name from multiple tables, the column name must be defined. ● You cannot create an index on a view. You cannot reference a view in the definition of a rule, default, or trigger. ● When querying data through the view, SQL Server should check to ensure that all database objects involved in the statement exist, and the data modification statement cannot violate the data integrity rules. ● The View name must follow the identifier rules and be unique to each user. In addition, the name cannot be the same as the name of any table owned by the user. SQL Server 2000 provides two ways to create a database view: one is to use the view designer to create a view, and the other is to use the CREATE command in the transact-SQL statement to create a view. (1) Use the view designer to create a view to open the Enterprise Manager, expand the database of the specified instance, and select the "View" directory. Right-click the view and select the "Create View" command from the shortcut menu to open the "view designer" dialog box. See Figure 4-17. Figure 4-17 the interface design of the view designer is very similar to that of the query designer. You can add a table to the designer and select the columns to display to form a view. Due to space limitations, I will not go into details here. You can refer to the content in the previous section. (2) Use the create view command in the transact-SQL statement to create a view. The syntax format is as follows: Create view [<
Database_name>.] [<
Owner>.]
View_name[(
Column[,...
N])] [With <view_attribute> [,... n]
Select_statement[With check option] <view_attribute >::={ encryption | schemabinding | view_metadata}. The parameters are described as follows. ●
View_name: Specifies the view name. column is used to specify the field name in the view. ● With encryption: SQL Server encrypts system columns including the create view statement text. ●
Select_statement: Select statement used to create a view. You can use the SELECT command to select columns from a table or view to form columns in the new view. ● With check option: All data modification statements used to force the view to comply
Select_statement.● Schemabinding: indicates
Select_statementIf a statement contains a table, view, or reference a user-defined function, the table name, view name, or function name must have the owner prefix before it. ● VIEW _ metadata: indicates that if the view is referenced in a query and the view mode metadata is required to be returned, SQL Server Returns the view metadata to dblib and OLE DB APIs. For example, create a view with a simple SELECT statement. A simple view is useful when you need to frequently query a combination of columns. In this example, a view named view_students is created to display the name, age, and Department of a student. The syntax is as follows: Use studentgocreate view view_studentsasselect sname, sage, sdptfrom studentsinfogo
1.3.2 modify ViewYou can modify an existing view in the view designer. Right-click the view to be modified and select the "design view" command to open the "view designer ". You can modify a view based on your needs. The process is similar to adding a view. You can also use the alter view statement in the transact-SQL statement to modify the view. Syntax: Alter View
View_name[(
Column[,...
N])] [With encryption]
Select_statement[With check option] The meanings of parameters are basically the same as those used when creating a view.
1.3.3 delete a viewUsers with relevant permissions can delete existing views. After a view is deleted, the data of the table and view is not affected. You can delete a view in the Enterprise Manager. Right-click the view to be deleted and select the DELETE command from the shortcut menu to open the delete object dialog box, as shown in Figure 4-18, click "Remove all" to delete the view. Figure 4-18 confirm to delete the view. You can also use the alter view statement in the transact-SQL statement to modify the view. Syntax: Drop view {
View_name}[,... N] where,
View_nameSpecifies the name of the view to be deleted. You can use this command to delete multiple views at the same time. You only need to separate the view names to be deleted with commas.
