MS SQL Basics Tutorial: Creating and Managing views

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;