Today, we will start with a basic article on the database.ArticleBecause I recently met several SQL developer jobs, I took this opportunity to pick up everything I learned from my school. All right, let's get started.
First, what is view used?
Views allow you to create a virtual representation of table data using a SELECT statement as its definition.
In other words, view isThe virtual representation of the table.
What can view do?
- Simplify data access for query writers. (simplified data access)
- Managing Security and protecting sensitive data. (permission management and protection of sensitive data)
- Grant permissions exclusively to views, rather than to the underlying tables)
- Expose only those columns that you wish the end user to see (end users can only see the columns they want to see)
- Allow direct data updates. (allow updating data)
The above points are the specific functions of view and SQL Server.
View type?
- Regular view (in general view, my understanding is the simplest view definition)
- Do not nest view (for example, call another view with one view)
- If you try to use stored procedure instead of view (compare with view, the execution plan of this view will be re-compiled every time you access the view)
- Indexed view (the view of the index, similar to the table, first defines the common view, and then adds the clustered index and non-clustered index. Note: Only one clustered index can be added)
- Distributed partitioned view (Distributed view, which uses Union all to combine several different SQL Server tables to generate a separate table)
View creation syntax
Create View [ Schema_name. ] View_name [ (Column [, n ] )]
[ With [Encryption ] [ Schemabinding ] [ View_metadata ] [ , N ] ]
As Select_statement
[ With check Option ]
For more information about these syntaxes, see this article.Here
Note the following rules when creating a view.
The first select expression can only define a maximum of 1024 columns.
The second into, option, compute, compute by, and Other table variables cannot be used.
The third order by clause cannot be used directly unless it is used together with the top clause.
Sleepy .... Continue writing tomorrow! To be continued