Applying Index, View, As Well As Full – Text Search Part – 6
Instruction For Forming View
An individual must think through the subsequent rules at the time of forming view on a table or relation:
· The name of a view should abide by the guidelines mentioned for the identifiers as well as should not have the identical name as that of the table or relation on which it is built.
· The view can be formed if there is a SELECT approval on its underlying table or relation or base table or relation only.
· The view cannot originate its information from any provisional tables or relations.
· The view cannot use the ORDER BY clause in the SELECT command.
For an instance, to offer accessibility only to the Customer ID, Name, Loan ID, ROI for every customer, an individual can form the subsequent view:
CREATE VIEW Customer . VW_CustomerDetails
AS
SELECT D . CustomerID , D . Name , L . LoanID , L . ROI FROM
Customer . Details D JOIN Customer . Loan L
ON D . CustomerID = L . CustomerID
The above syntax forms the view named VW_CustomerDetails which contains some particular columns or attributes from the Customer . Details and Customer . Loan tables or relations.
Limitation At The Time Of Altering Information With View
The view does not keep an individual copy of the information; however it only shows the information existing in the underlying table or relation or base table or relation. For that reason, an individual can alter the underlying tables or relations or base tables or relations by altering the information in the view. Nevertheless, the subsequent limitations are present at the time of adding, altering or deleting the information with the help of a view.
· An individual cannot alter information in a view when the alteration marks more than one base table or relation or underlying table or relation. But, an individual is able to alter the information in a view when the alteration marks only one table or relation at a point of time.
· An individual cannot alter a column or attribute which is the outcome of some sort of calculation, for an instance a calculated column or attribute or an aggregate function, like SUM, AVG.
For an instance, consider the view which has been formed earlier named VW_CustomerDetails for showing the Customer ID, Name, Loan ID, and ROI columns or attributes.
Now, when an individual attempts to run the subsequent update command, it creates an error. This is for the reason that the information is getting altered in two (2) tables or relations over a single update command:
UPDATE VW_CustomerDetails
SET Name = ‘ My New Name ’ , ROI = 10.45 WHERE CustomerID = ‘ C#25001 ’
Hence, as an alternative of a one (1) update command, an individual is required to run two (2) update command for both the table or relation separately.
The subsequent command will update the Name column or attribute in the Customer . Details table or relation.
UPDATE VW_CustomerDetails
SET Name = ‘ My New Name ’ WHERE CustomerID = ‘ C#25001 ’
The subsequent command will update the ROI column or attribute in the Customer . Loan table or relation
UPDATE VW_CustomerDetails
SET ROI = 10.45 WHERE CustomerID = ‘ C#25001 ’
Thus, to alter the information in two (2) or numerous (N) base tables or relations or underlying tables or relations with a view, the individual is required to run distinct update commands for every table or relation. An individual may construct an INSTEAD OF trigger on the view to alter information in a view when the alteration marks more than one base table or relation or underlying table or relation.
How To Index View?
Alike to the tables or relations, an individual can form index on view. In default, the view formed on a table or relation is non – indexed. But, an individual can index the view as soon as the size of information in the base tables or relations or underlying tables or relations is huge plus not regularly modified. When a view is indexed, its query performance is increased. A different advantage of forming an indexed view is that the query optimizer starts to make use of the view index in enquiries which do not straightly name the view in the FROM section. When the query mention some columns or attributes which also exists in the indexed view, plus the query optimizer evaluates that by means of the indexed view it will offers the lowermost cost retrieving tool, then the query optimizer chooses the indexed view only.
Once indexing a view, an individual is required to first form a unique clustered index on the view. Next, the individual can form extra non – clustered indexes. As soon as a view is indexed, the rows or tuples of the view are kept in the database in the identical setup as a table or relation.
Instruction For Forming Indexed View
An individual must think through the subsequent rules at the time of forming an indexed view:
· The unique clustered index should be the formed first on the view.
· The view should not mention any additional views; but it can mention merely the underlying tables or relations or base tables or relations.
· Every underlying tables or relations or base tables or relations sourced through the view should be in the similar database plus should have the identical owner as the view have.
· The view should be formed using the SCHEMABINDING selection. Scheme binding fixes the view to the specific scheme of the base tables or relations or underlying tables or relations.
In the upcoming part we will be discussing about How To Handle View?, Method To Alter A View, Method To Rename A View, Method To Delete A View, Applying A Full – Text Searching and Designing Full – Text Searching Technique in details.