Creating and Managing views

Create a | View 13.10.1 create a view
SQL Server provides two ways to create a view using SQL Server Enterprise Manager and Transac-sql commands. When you create or use a view, you should note the following:
• Views can only be created in the current database, and can only be referenced in a view of 1024 columns;
• If the table referenced by the view is deleted, an error message is returned when the view is used, or you must re-create the view if you create a new structure with the same table to replace the deleted table view;
• If a column in the view is a function, a mathematical expression, a constant, or a column name from more than one table, you must define a name for the column;
• You cannot create an index on a view; You cannot refer to a view in a rule, default, or trigger definition;
• When querying data through a view, SQL SERVER not only checks whether the table referenced by the view exists, is valid, but also verifies that changes to the data violate the integrity constraints of the data. If the failure returns an error message, if it is correct, the query to the view is converted to a query to the referencing table.

1. Use SQL SERVER Enterprise Manager to create views
The steps for creating a view in SQL Server using SQL Server Enterprise Manager are:
(1) Start SQL Server Enterprise Manager and log on to the specified server;
(2) Open the database folder where you want to create the view, select the view icon, display all views of the current database in the right pane, right-click the icon, select the new View option in the pop-up menu, and open the New View dialog box. As shown in Figure 13-1. There are four areas in the new View dialog box: The table area, the column area SQL script area, the data result area (where the four areas in the dialog box are blank);

(3) When creating a view, first click on the button, open the Add Table dialog, as shown in Figure 13-2;

(4) Select the data column in the column area that will be included in the view, at which point the corresponding SQL SERVER script is displayed in the SQL script area;
(5) A single point button that displays data rows contained in the view in the data result area;
(6) Click the button, enter the view name in the pop-up dialog box, and click Save to finish creating the view.

2. Transact-SQL command CREATE view
The syntax format for creating views using Transact-SQL command CREATE VIEW is:

The meanings of each parameter are described as follows:
view_name
Represents the view name.
select_statement
The body that forms the visual text, using the Select command to select columns from the table or view that make up the new view.
Tip:However, in a SELECT statement, you cannot use the order by, COMPUTE, COMPUTE by statements, and you cannot use the INTO keyword, and you cannot use temporary tables.
with CHECK OPTION
Ensure that the data is still visible through the view after the data modification is performed on the visual. For example, when you create a view, you define a conditional statement, and it's obvious that the view result set contains only the rows of data that meet the criteria. If you modify a row of data, which causes the row to not meet this condition, but because you used the WITH CHECH option when you created the view, the result set still includes the record when you query the view, and the modification is invalid.
encryption
Indicates that the text of the view is encrypted so that when the syscomments table is viewed, the value of the TXT field you see is only garbled.
schemabinding
Indicates that if a table, view, or reference user-defined function is included in the Select_statement statement, the table name, view name, or function name must have an owner prefix before it.
View_metadata
Indicates that SQL Server will return the view's metadata information to the Dblib and OLE DB APIS if the view is referenced in a query and requires return browse mode metadata
Example 13-13: Use the WITH encryption with CHECK option and include the function column.

Because the with CHECK OPTION is used, an error message is returned when the view is modified. For example, execute the following statement:

The error message returned is:


13.10.2 Management view
1, view, modify the View
In SQL Server, viewing and modifying views through SQL Server Enterprise Manager performs the following steps primarily:
(1) Start SQL Server Enterprise Manager and log on to the specified server;
(2) Open the database folder where you want to create the view, select the view icon, and display all views of the current database in the right pane;
(3) Right-click the view you want to view, select the Properties option in the pop-up menu, and open the View Properties dialog box, as shown in Figure 13-3.

(4) In the dialog box, you can browse to the body of the view, or modify the view, and then click the Check Syntax button to check the legality of the statement, and to set the access rights for your views, click the Permissions button.
2 checking views using stored Procedures


System procedure sp_help is used to return detailed information about a database object and, if not to a particular object, returns all object information in the database with the following syntax:

Sp_help Database object Name
The system procedure sp_helptext retrieves the text of the view, trigger, and stored procedure. Its syntax is:
Sp_helptext view (triggers, stored procedures)


3 Delete View
Deleting a view uses the drop command as much as deleting a table. Its syntax is:
DROP View View Name