Applying Index, View, As Well As Full – Text Search Part – 7

How To Handle View?

Sometimes, an individual requires sustaining the views to confirm their constant best performance. The collective view preservation job is consisting of altering, renaming plus deleting a view. For a database designer, it is essential to frequently observe the performance of the view as well as to improve it.

Method To Alter A View

When an individual describe a view with a SELECT command and then modify the arrangement of the base table or relation or underlying table or relation through inserting columns or attributes, the fresh columns or attributes does not show up in the view. Likewise, when an individual choose every columns or attributes in a CREATE VIEW command, the columns or attributes list is taken into consideration only when the individual has formed the view for the first time. In the direction of adding fresh columns or attributes in the view the individual should modify the view. The individual can alter a view devoid of deleting it. This confirms that the authorizations on the view are not misplaced. The individual can alter a view without disturbing its reliant on items. In the direction of altering a view, an individual is required to make practice of the ALTER VIEW command. The code of the ALTER VIEW command is as follows:

ALTER VIEW My_View_Name [ ( My_Column_Name ) ]

[ WITH ENCRYPTION ]

AS My_Select_Command

[ WITH CHECK OPTION ]

here,

· My_View_Name – It stipulates the name of the view that is to be modified.

· My_Column_Name – It stipulates the name or names of the column or attribute / columns or attributes that are to use in a view.

· WITH ENCRYPTION – It stipulates that the script of the view will be encoded in the SYSCOMMENTS system view.

· AS – It stipulates the activity that will be done through the view.

· My_Select_Command – It stipulates the SELECT command which describes a view.

· WITH CHECK OPTION – It obligates the information alteration commands to match up with the standards mentioned in the SELECT command describing the view. The information is observable by the view later the alterations has been done everlastingly.

For an instance, earlier a view named VW_CustomerDetails was formed to recover the particular information from the Customer . Details and Customer . Loan tables or relations. Now, the individual is required to modify the view description through adding the Phone column or attribute from the Customer . Details table or relation. To alter the description, an individual can use the subsequent command:

ALTER VIEW Customer . VW_CustomerDetails

AS

SELECT D . CustomerID , D . Name , D . Phone , L . LoanID , L . ROI

FROM Customer . Details D JOIN Customer . Loan L

ON D . CustomerID = L . CustomerID

The above syntax modifies the view description through adding the Phone column or attribute from the Customer . Details table or relation.

Method To Rename A View

An individual can give another name to the existing view, by means of the SP_RENAME system stored procedure. The individual can give another name to the view devoid of deleting it. This confirms that the authorizations on the view are not misplaced. The code of the SP_RENAME system stored procedure is:

SP_RENAME My_Old_ViewName , My_New_ViewName

here,

· My_Old_ViewName – It stipulates the existing view name which is desired to be retitled.

· My_New_ViewName – It stipulates the fresh name given for the existing view.

For an instance, the subsequent command will give a new name VW_CustomerDetails_Renamed to the existing view named VW_CustomerDetails on the Customer . Details table or relation.

SP_RENAME VW_CustomerDetails , VW_CustomerDetails_Renamed

The above statement retitles the view named VW_CustomerDetails as the VW_CustomerDetails_Renamed

An individual must think through the subsequent rules at the time of renaming the views:

· The view should be in the present database.

· The fresh name of the view should abide by the guidelines mentioned for the identifiers.

· The view can be given another name through its owner only.

· The database owner can provide another name to the view also.

Method To Delete A View

An individual is required to delete a view once it is not necessary any longer. The individual can delete a view from a database by means of the DROP VIEW command. While a view is deleted, it has no consequence on the base table / tables or relation / relations or underlying table / tables or relation / relations. Deleting a view eliminates it description and the entire authorizations that were allocated to it. Additional, if an individual enquiry some view which mentions a deleted table or relation, the individual will get an error message. Deleting a table or relation which mentions a view does not delete the view inevitably. The individual needs to make use of the DROP VIEW command obviously. The code of the DROP VIEW command is as follows:

DROP VIEW My_View_Name

here,

· My_View_Name – It stipulates the name of the view that is to be deleted.

For an instance, the subsequent command will delete the existing view named VW_CustomerDetails on the Customer . Details table or relation.

DROP VIEW VW_CustomerDetails

The above command will delete the VW_CustomerDetails view from the database.

An individual can delete numerous views with one (1) DROP VIEW command. The names of the views which is required to be deleted are parted by means of comma (,) in the DROP VIEW command.

Applying A Full – Text Searching

When enquiring information, an individual can make use of the LIKE operator to search for a textual data in a column or attribute. But, from time to time, an individual may want to do difficult search on the information. For an instance, the individual want to search for alternative word or opposite words for a specific word. The SQL Server permits the individual to enhance the information searching technique through designing the full – text searching capability. The full – text searching capability aids an individual to search for composite sequences in the database. In SQL Server, the full – text searching capability is deactivated by default. For a database designer, it is significant to be familiar with the configuration of the full – text search as well as in what manner to search information through full – text search.

Designing Full – Text Searching Technique

The full – text query technique in the SQL Server permits the employees or end – users to search for an extensive variety of textual information in the SQL Server tables or relations. Think through an instance, the loan management team of XYZBank performs often explorations of data on the Management . LoanDetails table or relation to improve the advertising approaches. The exploration is grounded on the information deposited in the Type column or attribute of the table or relation.

Consider this instance that the bank has decided to provide education loan to as many customer as possible. Hence, the loan manager needs to get the particulars of all the loans which are associated to education; consequently it will help in forming the publicizing plan for those loans. Precisely, the manager is in need of a list for all the loans which have the keyword ‘Domestic Education’ in the Type column or attribute of the Management . LoanDetails table or relation.

As the information is huge, the search query will consume a longer time to retrieve the information from the table or relation. For this situation, an individual can implement a full – text index on the Type column or attribute of the Management . LoanDetails table or relation in the direction of improving the efficiency of searching. On the way to get the necessary particulars by means of full – text searching technique, an individual is required to organize a full – text search on the database. For that, the individual is required to implement the subsequent tasks:

1. Activate the Full – Text Searching technique in the Database.

2. Form a Full – Text Catalog.

3. Form a Unique Index.

4. Form a Full – Text Index.

5. Generate the Full – Text Index.

The individual is required to be the member of the SYSADMIN role to enable full – text searching technique, form the full – text catalog as well as form a full – text index.

In the upcoming part we will be discussing about Activating The Full – Text Searching Technique In The Database, Forming A Full – Text Catalog, Forming A Unique Index, Forming A Full – Text Index, Generating The Full –Text Index, Full Generation, Alteration Tracking – Centric Generation, Incremental Timestamp – Centric Generation, and How To Search Information Through A Full – Text Search?