View in SQL Server (above) reprint

In the original: View in SQL Server (top) http://www.cnblogs.com/xbf321/archive/2009/06/16/view_in_sqlserver.html

1, what is a view?

2, why use a view;

3, order by in the view;

4, refresh the view;

5, update the view;

6, view options;

7, indexed view;

Go into the View in SQL Server (bottom) you should be asked to add the "Why should I use views" section in "View in SQL Server (above)".

1. What is a view

A view is a virtual table defined by a query that, unlike a physical table, has no physical representation of the data in the view, unless you create an index for it, and if you query a view without an index, SQL Server actually accesses the underlying table.

If you want to create a view, specify a name and query for it. SQL Server saves only the view's metadata, the user describes the object, and the columns it contains, security, dependencies, and so on. When you query the view, whether it is fetching data or updating the data, SQL Server accesses the underlying table with the definition of the view;

Views also play a number of important roles in our daily operations, such as the ability to access filtered and processed data using views, rather than directly accessing the underlying tables and, to a certain extent, protecting the underlying tables.

There are three rules to follow when creating a view:

1. You cannot specify an order by in a view definition unless the definition contains a top or for Xml description;
2. All columns must have a column name;
3. All of these column names must be unique;

For a view chart where there is no top or FOR XML description, there cannot be an order BY statement, because it is considered a table, the table is a logical entity, and its rows are in no order. All columns in the view must have a column name, and the only thing I think we all understand;

The following SQL statement represents the creation of a simple view:

   1:  VIEW dbo. V1

   2:   as

   3:  

   4:  EXISTS (WHERE customers.customerid = orders.customerid) 

2. Why use Views (update)

 Since SQL Server provides us with such an object, it must have its role. And we are using the view, either too much, or not enough, so some people suggest not to use the view, and some people are advised to use less. So who do we listen to?

In fact, if we master the purpose of the view, we can use the correct view in the right place, then what problem can the view solve for us?

1, reducing the complexity of database rendering for end users. The client can return the data I need as long as the simple code is written on the view, and some complex logical operations are done in the views;

2, prevent sensitive columns from being selected, while still providing access to other important data;

3, add some additional indexes to the view to improve the efficiency of the query;

The view doesn't actually change anything, it's just some sort of filtering of the data being accessed. Considering the role of the view, you should be able to see how the concept of views simplifies data for inexperienced users (showing only the data they care about), or does not give users access to the underlying tables
  Rights, but grants them access to the right to not contain sensitive data views, thereby hiding sensitive data in advance.

You know, in the default case, the view does not do anything special. The view is run from the command line as if it were a query (there is no pre-optimization of any kind), which means that there is a layer of overhead between the data request and the data being delivered. This indicates that the view must never be as
   It is just as fast as running the underlying SELECT statement directly. However, there is a reason for the view-that is, its security or simplification for the user, the tradeoff between your needs and costs, and finding the best solution for your particular situation.

3. Order BY in the view

    The view represents a logical entity, which is very similar to a table;

    If we add an order BY statement to the SQL statement created above, we'll see what the effect is:

   1:  VIEW dbo. V1

   2:  

   3:  

   4:  EXISTS (  

   5: by  CompanyName

Running the statement will fail and recycle to the following prompt:

MSG 1033, Level A, State 1, Procedure V1, line 5
The ORDER by clause is not valid in views, inline functions, derived tables, subqueries, and common table expressions unless you specify also TOP or for XML.

As a hint, order by is also not unavailable, only if the top or FOR XML statement is specified, order by can be used, such as:

   1:  ALTER VIEW dbo. V1

   2:  

   3:  

   4:  where EXISTS (SELECT * from Orders WHERE customers.customerid = orders.customerid)  

   5:  ORDER by CompanyName

However, it is not recommended to use order by in a view because the view represents a table and is not sorted for the table, so it is recommended to use order by when querying the view;

SQL Server2005 Books Online has one such description: "Use ORDER by in the definition of a view, inline function, derived table, or subquery, and the clause can only be used by the user to determine the row returned by the top clause." Order by does not guarantee an ordered result when querying these constructs, unless an order by is also specified in the query province. "

4. Refresh the View

As I said above, the view holds information such as metadata, columns, security, and dependencies, and if we change the schema of the underlying table, it does not directly reflect the view; After changing the schema, it is a good practice to refresh the view's metadata with the sp_refreshview stored procedure;

For example, we create a table T1 and a T1 view V1, then change T1, and then look at the results of V1:

First create the table T1:

   1:  IF object_id (' T1 ') is not NULL

   2:      DROP TABLE T1

   3:  

   4:  CREATE TABLE T1 (col1 int,col2 INT)

   5:  INSERT into T1 (col1,col2) VALUES

   6:  GO

Then create the T1 view V1:

   3:  VIEW V1

   4:  

   5: From  T1

In practical practice, to avoid using * in a SELECT statement in a view, this is just a demonstration. If you query the view V1, the following results will appear:

Next, we add a column of col3 to the table T1:

   1:  ADD col3 INT

Then query the view V1 again, do you think the result is three columns, or column? The answer is two columns. T1 schema changes, and does not affect the view of the metadata, this time, if we want to refresh the view V1, we can use: EXEC sp_refreshview V1 Command, re-query, V1 The result is three columns.

View in SQL Server (above) reprint