Everything about database,how to,example,guide,

The undermentioned viewpoint that in front we had mentioned concerned view:The result part that returns as a result of view and the list that have cavalcade structure are having identical form form,And we can be in SQL statement resemble citing the watch cites in that way view,So we often call view empty is expressed.The result collect of standard view does not store with the form of the watch in the database,Carrying out however cited when the inquiry of view,The logistic structure that SQL Server 2000 just incorporates the data in relevant basic watch into view.
The conclusion above often lets us generate such worry:Because was to be being carried out,cited when the inquiry of view,The logistic structure that SQL Server 2000 just incorporates the data in relevant basic watch into view,The view that should inquire place cites so is included much data goes or involve pair of mass data to undertake aggregate operation or join are operated all right,Beyond question,Found view result collect dynamicly to will bring heavy burden to the system,Often cite especially view of this kind of high capacity.
The method that solves this one headachy problem is founded for view namely get together bunch indexes,The performance that just can improve a system significantly so only.Should found on view get together after bunch indexes,The result collect of view and have getting together is memory like the data set of the watch that bunch indexes in data.
In addition,Index is founded to still can bring such profit on view;Optimize implement view is used to index in the inquiry that can not name view directly in FROM subordinate clause in those,And to the view that already existed character,Because need not write inquiry code afresh,The efficiency that improved inquiry to retrieve data from inside index view thereby.
If index is founded on view,So the data in view can be stored instantly in the database,To indexing view undertakes modification,So these modification meetings are mirrorred in fundamental watch instantly.With manage,The data that to the foundation the watch has is revised also can mirror there index view.The data that the only sex of index improved SQL Server to search those to be revised greatly goes.
The reference that defends index view to be expressed than maintaining a base is more complex.So,The rate that if think to be worth to be cost to come with be revised because of data and increasing systematic burden,raises data retrieval,Should found index on view so.Before founding index for view,View itself must satisfy the following requirement:When executive CREATE VIEW commands,Must be ANSI_NULLS and QUOTED_IDENTIFIER option setting ON condition;When the base that establishs index place to cite in command of use CREATE TABLE is expressed,ANSI_NULLS option should be installed for ON;The view that the object that this view place cites includes a foundation to express only and does not include other;The fundamental watch that view place cites must belong to same database together with view and have identical possessory;SCHEMABINDING option must be used when founding view;If view cited,the user defines function oneself,Also must use SCHEMABINDING option when founding these users to define function oneself so;View must be expressed with what the form of Owner.objectname will make what cite or the user defines function oneself;The function that view place cites must be determinism;Additionally notable is,Should not include the Transact-SQL grammar below in the SELECT statement that founds view;* or Tablename cannot be used in SELECT statement. * will define a kind,Must give break the ranks the name directly,Otherwise not OK;Express travel aggregate function;UNION, DISTINCT, TOP, ORDER BY, COMPUTE, COMPUTE BY, COUNT (* ) wait;The aggregate operation function such as AVG, MAX, MIN, STDEV, STDEVP, VAR and VARP.
Normally,Many index can be founded on view,But should remember,The first index that founds on view must be to get together bunch indexes,The blame that just can found other next gets together bunch indexes.
In the eightth chapter we had introduced CREATE INDEX to command in detail but if prepare to found index for view so carrying out this to command you must ensure the following condition is founded otherwise before will fail.Executant of jussive of CREATE INDEX must be view is possessory;Carrying out during founding index to command,All option of ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIERS should be become by the setting ON condition;NUMERIC_ROUNDABORT option is installed to be OFF condition;The data that view cannot include type of Text, Ntext, Image is listed;If option of ORDER BY is included in the SELECT statement of view,Get together the data that the crucial value that bunch indexes can be definition of the place in subordinate clause of ORDER BY only is listed.

After index was founded on view,If plan to revise view data,The option setting when should assuring to revise and the option setting when founding index are same,Otherwise SQL Server will produce wrong message,Wind the INSERT that rolls place to do, UPDATE and DELETE operation.