Create a view of database optimization

A view is a select query that is saved in a database, and most commands for a SELECT statement can be executed on the view. There are two reasons to create a view: One is security, the user does not have to see the entire database structure, while hiding part of the data, and the second is to conform to the user's daily business logic to make it easier to understand the data

What is a view?

A view is another way to view data in one or more tables in a database. A view is a virtual table, usually created by a subset of rows and columns of one or more tables, and is not a collection of data values stored by the database, and its rows and columns come from the tables referenced by the query. When executed, the view displays the data in the table directly.

A query that defines a view can be based on one or more tables, or based on other views, the current database, and other databases

The role of the view

* filter rows in a table

* prevent unauthorized users from accessing sensitive data

* abstract multiple physical data tables into a single logical data table

Benefits of using views

1. For end-users

* Results are easier to understand, change column names to meaningful names

* Easier access to data without the need to master T-SQL statements for multi-table queries to view through views

2. For developers

* Limit retrieval easier, when creating views, troubleshoot user-confidential columns

* More convenient to maintain the application

Create a View

In SQL Server, there are two ways to create a view: Using SSMS and using T-SQL statements

1. Create a view using SSMs

(1) Expand the database, right-click the view, select New View

(2) Select the desired table and select the columns you want to view, fill in aliases, and modify the following T-SQL statements to perform

(3) Last select Save, you can use it as you would open a normal table

2. Create a view using T-SQL statements

The syntax for creating a view is as follows:

CREATE view name as T-SQL statement

For example: Create a view for students in a network class, showing only names, genders, classes

Considerations for using Views

1. Multiple tables can be used in each view

2. Similar to a query, a view can nest another view, but preferably not more than three layers

3. The SELECT statement in the view definition cannot include the following

* ORDER by statement unless there is a TOP clause in the SELECT list of the SELECT statement

* INTO keyword

* Referencing temporary tables or table variables

Create a view of database optimization