Designing and implementing the view is an important step in the physical design of the database. In general, the design and implementation view should follow the following suggestions and principles.
The following content is excerpted in this document. I have added some important points (in red)
You can only create a view in the current database. However, if you use distributed query to define a view, the tables and views referenced by the new view can exist in other databases or even other servers.
- Distributed view is feasible, but with the improvement of SQL Server's own capabilities, for example, after SQL Server 2005 supports Table Partitioning and other technologies, distributed view should be used as little as possible.
- One of the biggest problems of the so-called distributed view is physically separated tables in multiple databases or even servers, which increases the difficulty of maintenance and query.
View names must follow the rules of identifiers and must be unique to each architecture. In addition, the name cannot be the same as the name of any table contained in the schema.
- A reference is to add a prefix VW before the view name.
You can create views for other views. Microsoft SQL Server allows nested views. However, no more than 32 layers can be nested. Based on the view complexity and available memory, the actual restriction of view nesting may be lower than this value.
- Generally, no more than two layers are recommended.
You cannot associate rules or default definitions with views.
You cannot associate an After trigger with a view. Only an instead of trigger can be associated with it.
- Triggers are generally not recommended unless you have.
A query that defines a view cannot contain the compute clause, compute by clause, or into keyword.
- Many friends do not know that the computer and computer by statements are only used in some special cases to generate a total row. There are roughly the following results:
This feature cannot be used for views, but can be directly used for queries.
A query that defines a view cannot contain an order by clause, unless there is a top clause in the SELECT statement selection list.
- This is very interesting. If you want to access all of them, you must write Top 100 percent.
The query of the definition view cannot contain the option clause of the specified query prompt.
A query that defines a view cannot contain the tablesample clause.
- You may be unfamiliar with the tablesample Statement, which is used to sample data. Unlike the top statement, the top statement has a fixed size, while tablesample may return more data, less data, or even less data.
- I have an articleArticleDescribe this syntaxHttp://www.cnblogs.com/chenxizhang/archive/2009/05/19/1460040.html
Full-text index definitions cannot be defined for views.
You cannot create temporary views or create views for temporary tables.
- In SQL Server 2005, you can use CTE (common table expression) to implement this function.
- In earlier versions, the general approach is to use temporary tables, table variables, functions, and so on.
You cannot delete a view, table, or function that participates in a view created using the schemabinding clause, unless the view has been deleted or changed and no longer has schema binding. In addition, if you execute the alter table statement on the table that participates in the view with schema binding, and these statements affect the definition of the view, these statements will fail.
- If you do not use the schemabinding clause to create a view, you should runSp_refreshview. Otherwise, unexpected results may be generated when you query the view..
- If you modify a table, how do you refresh all views related to the table?
- Http://msdn.microsoft.com/zh-cn/library/ms187821 (SQL .90). aspx
- We strongly recommend that you add the schemabinding clause to some very important views.
Although the view definition can contain full-text queries when you reference a table with full-text indexes configured, you cannot perform full-text queries on The View.
In the following cases, you must specify the name of each column in the View:
- Any column in the view is derived from an arithmetic expression, built-in function, or constant.
- Two or more columns in the view should have the same name (usually because the view definition contains a join, the columns from two or more different tables have the same name ).
- You want to specify a different name for the column in the view from the source column. (You can also rename a column in the view .) Whether renamed or not, the View column inherits the Data Type of its source column.
To create a view, you must obtain the operation execution permission granted by the database owner. If you use the schemabinding clause to create a view, you must have corresponding permissions on any tables or views referenced in the view definition.
By default, because rows are added or updated through the view, they disappear from the view range when they no longer meet the query conditions for the defined view. For example, if you create a query that defines a view, this view Retrieves all rows whose salaries are less than $30,000 from the table. If an employee's salary rises to $32,000 because the employee's salary does not meet the conditions set by the view, the view chart no longer displays the employee. However, the with check option clause forces all data modification statements to be executed according to the view to meet the conditions set in the SELECT statement of the defined view. If this clause is used, modification to the row cannot cause the row to disappear from the view. Any modification that may cause the row to disappear will be canceled and an error will be displayed.
By the author: Chen xizhang in 2009/6/15 17:31:29 published in: http://www.cnblogs.com/chenxizhang/
This article is copyrighted by the author and can be reproduced. However, this statement must be retained without the author's consent, and the original article is clearly connected on the article page. Otherwise, the legal liability will be retained.
For more blog articles, as well as the author's complete comments on Blog references and the policy of cooperation, please refer to the following site: Chen xizhang's blog Center
