Learn about views in SQL Server self-study process

1, what is view?

2, why to use the view;

3, the order by in the view;

4, refresh the view;

5, update the view;

6, view options;

7, indexed view;

1. What is a view

A view is a virtual table defined by a query that differs from a physical table in that the data in the view has no physical representation 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, assign it a name and query. SQL Server saves only the metadata of the view, the user describes the object, and the columns it contains, security, dependencies, and so on. When you query the view, SQL Server uses the definition of the view to access the underlying table, whether you get the data or update the data;

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

When we create a view, we also adhere to three rules:

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

You cannot have an ORDER BY statement on a view chart without a top or for XML description, because the views are considered to be a table, the table is a logical entity, and its rows are not sequential. All columns in the view must have column names, and the only thing I think we all understand;

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

Copy Code code as follows:

CREATE VIEW dbo. V1
As
SELECT Customerid,companyname from Customers
WHERE EXISTS (SELECT * from Orders WHERE customers.customerid = orders.customerid)

2. Why to use Views (update)

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

In fact, if we grasp the purpose of using the view, we can in the right place, with the right view, then the view can solve what problems?

1) To reduce the complexity of database rendering for end users. As long as the client writes simple code to the view, it will be able to return the data I need, some complex logic operations, and put it into the views to complete;

2) To 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 just makes some sort of filtering on the data you're accessing. 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 table's
Rights, but grant them access to the rights that do not contain sensitive data views, thereby hiding sensitive data in advance.

You know, by default, views don't do anything special. The view runs from the command line like a query (there are no optimizations in any form), which means that there is an extra layer of overhead between the data request and the data that will be delivered. This means that views must never be like
It's just as fast as running the underlying SELECT statement directly. However, there is a reason for the view-this is its security or simplification for the user, balancing your needs and expenses, and finding the best solution for a particular situation.

3. Order BY In view

A 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, let's see what the effect is:

Copy Code code as follows:

ALTER VIEW dbo. V1
As
SELECT Customerid,companyname from Customers
WHERE EXISTS (SELECT * from Orders WHERE customers.customerid = orders.customerid)
ORDER BY CompanyName

Running the statement will fail, and the following prompt is recycled:

MSG 1033, level, State 1, Procedure V1, line 5
Unless you also specify top or for XML, the ORDER BY clause is not valid in views, inline functions, derived tables, subqueries, and common table expressions.

According to the prompts, the order by is also not unavailable, and only the top or FOR XML statements are specified, and then a must is used, such as:

Copy Code code as follows:

ALTER VIEW dbo. V1
As
SELECT Top (a) Customerid,companyname from Customers
WHERE EXISTS (SELECT * from Orders WHERE customers.customerid = orders.customerid)
ORDER BY CompanyName

However, it is not recommended that you use order by in the view, because the view represents a table and there is no sort for the table;

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

4. Refresh the View

As I said above, the view saves metadata, columns, security, and dependencies, and if we change the schema of the underlying table, it is not reflected directly into the view; After changing the schema, it is a good habit to use the Sp_refreshview stored procedure to refresh the metadata of the view;

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

First create the table T1:

Copy Code code as follows:

IF object_id (' T1 ') is not NULL
DROP TABLE T1
CREATE TABLE T1 (col1 int,col2 INT)
INSERT into T1 (col1,col2) VALUES (1,2)
Go

Then create the T1 view V1:

Copy Code code as follows:

CREATE VIEW V1
As
SELECT * from T1

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

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

Copy Code code as follows:

ALTER TABLE T1 ADD col3 INT

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

5. Update view

The view is a virtual table, and when we query the view, it is actually a query to the underlying table. The view can be used not only as a target for a select query, but also as a target for modifying statements. Of course, when you modify the view, the change is to the underlying table changes, it is like a proxy. Of course, if you are not allowed to modify the underlying table directly, you can restrict the data you want to expose by allowing only the view to be modified. In this way, you can have a certain amount of protection for your data, but this limit is very small.

So what are the constraints when you update the view?

1, as long as the view has a column can not implicitly get the value, you can not want to insert data in the view, if the column allows null, there is a default value or Idetity property, it can implicitly get the value;

2), if the view contains a join, the UPDATE or INSERT statement can only affect one end of the join. That is, the INSERT or UPDATE statement must define the destination column list, which can only be one end of the data connection. You cannot delete data from the view defined by the join query;

3), you cannot modify the columns that are the result of the calculation. For example: scalar expressions and aggregate functions, SQL Server does not attempt to change the database engine's calculations;

4, if you specify the WITH CHECK option when you create or modify a view, the INSERT or UPDATE statement that conflicts with the query filter for the view will be rejected, and I'll explain in more detail in the "View Options" section.

If the insert on trigger is defined on the view, data modification statements that violate these restrictions can be executed. In the insert of trigger you can replace the original modification with your own code;

When you allow changes to a view that is defined by a linked query, be cautious, such as a one-to-many relationship, and if you modify a record of a column value according to a "more" index, the result can be imagined;

6. View Options

When you create or modify a view, you can specify options that allow users to control the behavior and functionality of the view.

The encryption, schemabinding, and view_metadata options are specified in the view header, and the CHECK OPTION option is specified after the query;

Such as:

Copy Code code as follows:

CREATE VIEW v2
With Encryption,schemabinding,view_metadata
As
SELECT OrderID from dbo. Orders
With CHECK OPTION

1), encryption

This is a good option if you need to encrypt a view when you are building any type of business software.

If you do not specify the encryption option, SQL Server saves the user-defined statement as plain text, and the text of the object is confused if the encryption option is specified.

SQL Server provides a system function sp_helptext view the text of the view, and if the encryption option is applied, it will get "the text for object ' xx ' is encrypted" statement;

Note: Before encrypting, be sure to back up the view you want to encrypt, once encrypted, you cannot turn back.

2), schemabinding

If you create a view using the SCHEMABINDING option, SQL Server will not allow you to delete the underlying table or modify the referenced columns to prevent the view from being "orphaned" when you modify the underlying object, if someone does not notice your view and executes the drop, Deleting a column or some other action referenced by a view is bad. If you use the SCHEMABINDING option, you can avoid this situation.

If you want to create an index on a view, you must use the schmabinding option;

If you apply this option, be aware of two points when defining the view:

1, all objects must have a two-part name, such as: dbo should be used. Orders, not orders.

2, cannot use * in the select list, all column names must specify a name;

3), CHECK OPTION

A view created with check OPTION prevents inserts or UPDATE statements that conflict with the view query filter. Without this option, the view can accept modifications that do not conform to the query filter. Like what:

We created a Customwithorder view in the Northwind database and have not yet added with CHECK option

Copy Code code as follows:

CREATE VIEW Customerwithorder
With View_metadata
As
SELECT Customers.customerid,customers.companyname from Customers
WHERE EXISTS (SELECT 1 from Orders WHERE Orders.CustomerID = Customers.CustomerID)

The purpose of this view is to query the ID and company name of all the customers who have orders, and then we insert a nonexistent user ID into the view and the company name:

Copy Code code as follows:

INSERT into Customerwithorder (customerid,companyname) VALUES (' MYSQL ', ' myreed ')

Execute successfully, and then in the query this Customerwithorder view, it is obvious that the query is not CustomerID for ' MySQL ' users, because the view only contains the users who have had the order, if you directly query the Customers table, you will find this new user information.

Next, add the WITH CHECK option option to the Customerwithorder view

Copy Code code as follows:

ALTER VIEW Customerwithorder
With View_metadata
As
SELECT Customers.customerid,customers.companyname from Customers
WHERE EXISTS (SELECT 1 from Orders WHERE Orders.CustomerID = Customers.CustomerID)
With CHECK OPTION

And then execute the following statement:

Copy Code code as follows:

INSERT into Customerwithorder (customerid,companyname) VALUES (' Ilsql ', ' myreed ')

You will receive the following error:

MSG 1, Line 2
An attempt to insert or update has failed because one of the views spanned by the target view or the target view specified with CHECK option, and the action's single or multiple result rows do not conform to the CHECK option constraint.
The statement was terminated.

4), View_metadata

The effect of this option is to make the view look more like a real table. Without this option, the metadata returned to the client's API will be the data of the underlying table on which the view depends;

You can specify this option when you create or modify a view if the client expects SQL Server to send the metadata information for the view instead of the underlying table's metadata; listen to me slowly;

Assuming that the user has permission to manipulate the view without permission to manipulate the underlying table, the user performs some action on the view, and if the VIEW_METADATA option is specified, the statement will violate security and fail as long as the specified view_ Metadata then returned to the client is the view's metadata, not the underlying table's metadata. On the other hand, if a user tries to modify data through a view, and that action conflicts with the check option defined on the view, this operation can only succeed if it is submitted directly to the underlying table.

There is a tool in SQL Server, in SqlServer2000, where Enterprise Manager is, if we insert a record into the view, such as to the with CHECK Option to insert an arbitrary consumer in the Customerwithorder view regardless of whether it exists or not, and turn on tracking the actions that Enterprise Manager commits to SQL Server, you will find that the operation actually commits the underlying table as a target, and in time he violates check OPTION, Will succeed as well. In the case of SSMs in SQL Server2005, if you insert a record manually in Modify view, you can succeed by inserting it into the underlying table, although the View_metadata and check option options are specified. You can keep track of the actions that you commit to SQL Server (with SQL Profiler). However, if you operate in a panel generated by the Open View, you will fail, prompting:

You can track the actions submitted to SQL Server again, and you can see that he submitted to the target object is a view;

If the client wants SQL Server to send the metadata information for the view instead of the underlying table's metadata, you can specify this option when you create or modify the view

Did you get it this time?

I personally conclude that as long as there is a view_metadata option it is necessary to add check option options, while the schemabinding option is best added to prevent your view from "orphaned", while in indexed view schemabinding options must be added.

7. Indexed view

Without an index, the data in the view does not have any physical representation, and if indexed, the data in the view is physically instantiated, and SQL Server synchronizes the indexed view when the underlying table is modified. But you can't sync view content directly.

We know that creating an index on a table can improve performance, same as in a view, the first index created on a view must be a unique clustered index before other nonclustered indexes can be created.

Indexed views must use the SCHEMABINDING option and cannot refer to other views, only the underlying tables and UDF, and the underlying tables and UDF must be referenced using two-part naming conventions (see the Schemabinding option in 5. View options).

In addition to performance, you may also use indexed views for other reasons, such as having a column in an underlying table where we want to force the uniqueness of known values in the column, but allow multiple null values, what do we do, and the first thing we think of is probably a unique constraint, But the unique will think that two null values are equal, then this has to give up, then what is the solution?

In fact, we can use an indexed view to complete this task, using an indexed view to filter all non-null data, then this index prevents duplicate known values from entering the underlying table, but allows multiple nulls, because NULL is not part of a unique index, and when we insert data into the underlying table, To limit our data by using the unique index view to achieve the purpose of enforcing known values in a column;

We can demonstrate that we first create an underlying table T2 and an indexed view V2:

Copy Code code as follows:

CREATE TABLE T2 (col1 int,col2 NVARCHAR (50))
CREATE VIEW V2
With schemabinding
As
SELECT col1 from dbo. T2 WHERE col1 is not NULL;
CREATE UNIQUE CLUSTERED INDEX idx_col1 on dbo. V2 (col1);

We then insert the following data into the T2 table:

Copy Code code as follows:

INSERT into T2 (col1,col2) VALUES (1, ' 2 ')
INSERT into T2 (col1,col2) VALUES (1, ' 3 ')
INSERT into T2 (col1,col2) VALUES (null, ' 4 ')
INSERT into T2 (col1,col2) VALUES (null, ' 5 ')

So which of the above 4 inserts will fail? The answer is 2. Finally, let's select the base table T2 to see what we started that request for?

Copy Code code as follows:

SELECT * FROM T2

Perform: