SQL VIEW usage Syntax

have never known the role of the view, write the program is seldom encountered, review the SQL statement when encountered, recorded it.

What is a view?

In SQL, a view is a table of visualizations based on the result set of an SQL statement.

The view contains rows and columns, just like a real table. A field in a view is a field from a real table in one or more databases. We can add SQL functions, WHERE, and JOIN statements to the view, and we can also submit the data as if they came from a single table.

Note: The design and structure of a database is not affected by functions, where, or join statements in the view.

SQL CREATE VIEW Syntax

1 CREATE VIEW  as 2 SELECT column_name (s) 3  from table_name 4 WHERE Condition

Note: Views always display the most recent data. Each time a user queries the view, the database engine rebuilds the data by using SQL statements.

Inquire:

1 SELECT *  from WHERE Condition

SQL Update View

You can use the following syntax to update the view:

1 CREATE OR REPLACE VIEW  as 2 SELECT column_name (s) 3  from table_name 4 WHERE Condition

Can only be recreated with create or replace at a time (create) that is update

SQL undo View

You can delete a view by using the drop View command.

1 DROP VIEW view_name

What does view specifically do?

1. Simplify data access for query writers. (Simplified data access)
2. Managing security and protecting sensitive data. (Rights management and protection of sensitive data)
3. Grant permissions exclusively to views, rather than to the underlying tables (Do not assign table permissions directly to users, replace with view)
4. Expose only those columns it wish the end user to see (which allows end users to view only the columns they want)
5. Allow direct data updates. (Allow data to be updated)

What kind of View?

1. Regular view (normal view, my understanding is the simplest view definition)
   • Do not nest a view (for example, using a view to call another view)
   • If possible use stored procedure to replace view. (In contrast to view, this view's execution plan will be recompiled once every time you visit the view)
2. Indexed View (indexed view, similar to table, define normal View First, then add clustered index and non-clustered index. NOTE: Clustered index can only have one)
3. Distributed partitioned view (distributed view, this view uses union ALL to combine several different SQL Server table together to generate a separate table)

There are a few rules to look at when creating a view

The first select expression can define a maximum of 1024 columns

Second to, OPTION, COMPUTE, COMPUTE by and reference other table variables are not available

The third ORDER by cannot be used directly unless it is used with top

SQL VIEW usage Syntax