View (up) (update) in SQL Server)

 

1. What is a view?

2. Why view;

3. order by in the view;

4. Refresh the view;

5. Update the view;

6. view options;

7. Index View;

To elaborate on the views (below) in SQL Server, you must have added the "Why to use views" section in "View (top) in SQL Server.

 

1. What is a view?

A view is a virtual table defined by a query. Unlike a physical table, the data in the view has no physical representation unless you create an index for it; if you query a view without indexes, SQL Server actually accesses the base table.

If you want to create a view, specify a name and query for it. SQL Server only saves the view metadata. You can describe this object, as well as the columns, security, and Dependencies it contains. When you query a view, SQL server uses the view definition to access the basic table whether to obtain or update data;

Views also play many important roles in our daily operations. For example, you can use views to access filtered and processed data, rather than directly accessing basic tables, the basic table is also protected to a certain extent.

When creating a view, we must follow three rules:

1. Order by cannot be specified in the view definition, unless the definition contains the Top or For Xml description;
2. All columns must have column names;
3. All these column names must be unique;

The Order by statement is not allowed when top or for xml is not described in the view chart. This is because the view is considered a table and the table is a logical entity, its rows are unordered. All columns in the view must have column names, which are unique;

The following SQL statement creates a simple view:

   1:  CREATE VIEW dbo.V1

   2:  AS

   3:  SELECT CustomerID,CompanyName FROM Customers 

   4:  WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)

2. Why use View (update)

Since SqlServer provides us with such an object, it must have a role. However, when using a view, we either use too much or use less. Therefore, some people suggest not use the view, while some people suggest using less. Who should we listen?

In fact, if we master the purpose of using a view, we can use the correct view in the right place. What problems can a view solve for us?

1. Reduce the complexity of database presentation for end users. As long as the client writes simple code to the view, it will be able to return the data I need, and some complicated logic operations will be completed in the view;

2. prevent sensitive columns from being selected and provide access to other important data;

3. Add some additional indexes to the view to improve query efficiency;

In fact, the view does not change anything, but only filters the accessed data in some form. Consider the role of a view. You can see how the view concept simplifies data for inexperienced users (only displays the data they care about), or does not allow users to access basic tables.
Rights, but grant them the right to access a view that does not contain sensitive data, thus hiding sensitive data in advance.

You know, by default, the view does not do anything special. The view runs from the command line like a query (there is no pre-optimization in any form), which means an additional overhead is added between the data request and the data to be delivered. This indicates that the view cannot be like
It is just as fast as running the underlying SELECT statement. However, there is a reason for the view-that is, its security or simplified for users, balancing your needs and overhead to find the most suitable solution for specific situations.

3. order by in the view

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

If we add an Order BY statement in the SQL statement created above, let's see what the effect is:

   1:  ALTER VIEW dbo.V1

   2:  AS 

   3:  SELECT CustomerID,CompanyName FROM Customers 

   4:  WHERE EXISTS(SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID)  

   5:  ORDER BY CompanyName

 

Running this statement will fail and the following prompt will be returned:

Msg 1033, Level 15, State 1, Procedure V1, Line 5
Unless TOP or for xml is also specified, the order by clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions.

As prompted, ORDER By cannot be used either. order by can be used only after Top or for xml statements are specified, for example:
 

   1:  ALTER VIEW dbo.V1

   2:  AS 

   3:  SELECT TOP(10) CustomerID,CompanyName FROM Customers 

   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 the view because the view represents a table, but there is no sorting for the table. Therefore, when querying the view, use order;

The SQL Server2005 online series provides the following description: "The ORDER BY clause is used in the definitions of views, inline functions, derived tables, or subqueries. The clause can only determine the rows returned BY the TOP clause. Order by does not guarantee that the ordered results are obtained when these structures are queried, unless order ."

4. Refresh the view

As I said above, the view will save metadata, columns, security, dependencies, and other information. If we change the architecture of the basic table, it will not be directly reflected in the view; after the architecture is changed, it is a good habit to use sp_refreshview stored procedure to refresh the view metadata;

For example, we created a table T1 and a T1 view V1, then changed T1, and then looked at the V1 results:

Create Table T1 first:

   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(1,2)

   6:  GO

Then create the T1 view V1:

   3:  CREATE VIEW V1

   4:  AS 

   5:  SELECT * FROM T1

 

In practice, avoid using * in the SELECT statement in the view. This is only a demonstration. If you query view V1, the following results are displayed:

Next, we will add a column col3 to table T1:

   1:  ALTER TABLE T1 ADD col3 INT

Then you can query view V1 again. Do you want to query three columns or columns? The answer is two columns. The change in T1 architecture does not affect the view metadata. At this time, if we need to refresh the view V1, we can use the EXEC sp_refreshview V1 command to query it again, the V1 result is three columns.

If you don't want to write it, you're sleepy. If you want to complete it tomorrow, you have two sections.

Xingbaofang

References: T-SQL Programming

Tag: View, SQL Server, View, refresh View, update View, index View