To access data through the view, the advantage is very obvious.Such as data confidentiality can play to ensure the independence of the data logic to simplify queries and so on.
However, having said that, SQL Server database, the view is not a panacea, he and the basic object table is a major difference.In the use of view, the need to comply with four constraints.
Constraints I: view data changes.
When the user updates the data view, in fact, change is its corresponding data table of data.Whether the data is to change the view or the view to insert or delete data, are similar reason.However, not all views can be changed.If following these views, the SQL Server database will not be able to update the content directly, otherwise, the system will reject this illegal operation.
If in one view, the use of a Group By clause, on the view the content of the summary.The user will not be able to update this view.This is mainly because the use of Group By clause to the query results are summarized in, the view will be lost in this record of the physical storage location.So, the system can not find the need to update the record.If the user wants to change the data in the view, the database administrator will not be able to view the Group BY group statement added.
If not able to use the Distinct keyword.Use this keyword to remove duplicate record is.If there is no time to add this keyword, view check out the record with 250.Add the keyword, the database will eliminate duplicate records show only 50 records of non-repetition.At this point, if the user wants to change one of the data, the database need to be changed in the end do not know which of their records.Because the view is only one record in the look, but in the underlying table, some records may have dozens.Therefore, if used in the view Distinct keyword, then it can not change the contents of the view.
If the view has AVG, MAX and other functions, is not and can not be updated.If a view, which uses a function to summarize SUN staff wages, this time, not able to update this table.This is the database for the added protection of data consistency constraints.
Visible, attempts Although convenient, safe, but its still not able to replace the table position.When you need some updated data in the table, we passed more often to complete the operation on the table.View content directly because of changes to it, need to comply with some restrictions.In practice, the more processing rules for the program directly through the front desk to change the background based on the table.As for the security of data in the table, will have to rely on to protect the foreground application.Sure to change the accuracy, legality.
Constraints II: the definition of the view query is not able to use certain keywords.
We all know that the view is actually composed of a set of queries.Or, view is a package query tool.In the query, we can show through a number of keywords to format the results.As we usually work, often need a tables in a table of data to merge with another.At this point, the database administrator can use Select Into statements to complete.First data from a table in the query out, and then add it to a table.
As often need a similar operation, we can make it into a view.Every time when the need arises, you can only run this view, rather than re-write every time SQL code.But unfortunately, the result is negative.In the SQL Server database, view, can not be with Into keyword.If you want to achieve similar functionality, only by function or process to achieve.
In addition, the difference is with the Oracle database, in Microsoft's SQLServer database, create a view when there is an additional limitation.Is not able to create a query view, use the sort order by statement.This is a very special requirements.A number of Oracle database administrators in the use of SQL Server database to create the view, it often will make that mistake.They do not understand why Oracle database possible, but Microsoft's database will not work? This I am afraid only Microsoft database product designers to be able to answer.In short we need to remember is that in the SQLServer database, create a view, the query can not be included in the Order By statement.
Three constraints: column alias to some, and to ensure that the column name only.
Associated with the query in the table, when the column names in different tables the same, only need to add the prefix to the table.Also on the list do not need to be named.However, the problem occurs when creating the view, the database will be prompted to "duplicate column name" in the error message, warning users have duplicate column names.Sometimes, users to connect multiple Select statements from different columns of the table, if it has the same name, then this statement can still be executed.However, if we copy it to create a view window, create a view, they will not succeed.
Query with the query to create view there are many similar differences.If when we query, may be more frequent use of some arithmetic expressions; or use the query function and so on.In the query, we can not give the column "name."When the database query will be automatically given its name.However, the view is created, the database system will give you a problem.The system will remind you of the column alias.
From the above two examples, we can see that, although the view is encapsulated SQL statement, but there are still differences between the two.Create view query must comply with certain restrictions.To ensure that the view only of each listing; if A view from a column is an arithmetic expression, function or constant, when taken to give their names, and so on.
Four constraints: the dual restrictions on rights.
In order to protect the security of the data underlying table, the view is created, the more stringent access control.
On the one hand, if the user needs to create a view, you must have permission to create a database view.This is the view must be followed when establishing a basic condition.Although, as some database administrators create a table, modify the permissions; However, this does not mean that the database administrator will have permission to create the view.On the contrary, in a large database design, database administrators will often be divided.Establish a basis to establish the basis of the table to just the table; responsible for creating the view to create a view only permissions.
Secondly, in view of rights has created the same time, the user must also have access to the tables of the authority.Such as a database administrator, already has permission to create a view.At this point, if their wages and salaries of the information you need to create a view, not necessarily be successful.This is also the database administrator has the reputation associated with the salary information access based on the table.Such as the establishment of employee salary information related to this view, a total of five tables, then the database administrator will need to query each table owner privileges.If not, then create this view will end in failure.
Third, is the view of succession rights.If the above example, the database administrator is not the owner of the underlying table.But after the owner's authorization, he can access the table on this basis to be established as the basis for the view.However, the database administrator has not put the table on the basis of access to sublicense to other people? If he can authorize access to employee attendance A user information table? The answer is not necessarily.By default, the database administrator can no longer be authorized for other users.However, if the owner of the underlying table to the right then to the database administrator, he can re-authorize the user.A database administrator can enable users to authorize, so that it could be related operations.
Visible, the view though flexible, safe, convenient, but it still has more restrictions.According to the author's experience, usually in the reports, forms, etc. work, using the view will be more reasonable.Because of its SQL statements can be reused.In the base table updates, including a record change, delete, or insert, and is often based directly on the table to be updated.For some table constraint, can be triggers, rules, etc. to achieve; even directly through the front SQL statement to achieve constraint.As a database administrator, to have the ability to determine when to use view based on the table when called directly.